sql - Postgres 9.4 sql查询超时

Question

添加这两行或其中任何一行后，下面的查询超时

AND final IS NOT NULL 
ORDER BY tmodified ASC

查询持续运行超过 10 分钟....然后超时。

如果我删除以上两行，它会在 1 毫秒内返回结果，这很好。

知道如何使以上两行与以下查询一起使用吗？

表table_h有3600万条记录，在这个表中

column final is  numeric(10,5)
column tmodified is bigint, Timestamp

我正在使用 Postgres 9.4

这是完整的查询。

SELECT DISTINCT t.cid, h.a, am.b, u2.c, u.d, h.e, ie.f, im.g FROM table_am as am
  INNER JOIN table_t as t on (t.id = am.id AND t.type = am.type)
  INNER JOIN table_h AS h on h.iid = t.id
  INNER JOIN table_u AS u ON u.id = h.uid
  INNER JOIN table_u AS u2 ON u2.id = h.lu
  INNER JOIN table_im AS im ON im.asid = am.sid
  INNER JOIN table_ie AS ie ON ie.uid = u.uid
  INNER JOIN table_g AS g ON g.id = h.oldid
WHERE h.final >= 0 
  AND h.final IS NOT NULL
  AND h.tmodified >= 1499903419
  AND ie.p = im.p
  AND h.sr IN ('x', 'y', 'z')
  AND h.id = (SELECT id FROM table_h WHERE oldid = h.oldid AND final >= 0 
              AND final IS NOT NULL -- Issue is here and
              ORDER BY tmodified ASC -- Issue is here
              LIMIT 1)
  AND h.id NOT IN (SELECT id FROM table_m  WHERE tmodified > 1499903419) 

Answer 1

好吧，我可以解决你一半的问题。条件:

AND h.final IS NOT NULL

不需要。条件:

h.final >= 0

已经考虑到了这一点。

如果剩下的查询返回的这么快，那么使用子查询或者cte，然后order by:

with cte as (
      select . . ., t.modified
     )
select cte.*
from cte
order by modified;