Postgres 9.4 sql query timeout

Question

Below query times out after adding these two lines or any single one of it

AND final IS NOT NULL 
ORDER BY tmodified ASC

Query keeps running more then 10 min.... and time out.

if I remove above two lines, it return the results within 1 milliseconds, which works Ok.

Any idea how can I make above two lines work with below query?

Table table_h has 36 million records and in this table

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

I am using Postgres 9.4

Here is the complete query.

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)

What is 36 million times 36 million? A very large number. You should get rid of that correlated subquery. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 14, 2017 at 2:12
We would have to rewrite your query. My guess is that just doing away with the correlated subquery would allow the query to complete. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 14, 2017 at 2:24
It works fine by just remove two columns( final and order by) from where clause. but that is needed. May be because "is not null" and order by takes huge time.... — Developer
– Developer, Commented Jul 14, 2017 at 2:28
Well ordering that subquery could cost a fortune. Do you see why? The table has 36 million records to sort, for each record of the same table. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 14, 2017 at 2:30

Gordon Linoff · Accepted Answer · 2017-07-14 02:42:16Z

1

Well, I can solve half your problem. The condition:

AND h.final IS NOT NULL

is not needed. The condition:

h.final >= 0

already takes this into account.

If the remaining query returns so quickly, then use a subquery or cte and then order by:

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

answered Jul 14, 2017 at 2:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2017-07-14 03:43:31Z

1

Try replacing the correlated subquery with a join, something like this:

SELECT ...
FROM table_am as am
...
INNER JOIN table_g AS g ON g.id = h.oldid
INNER JOIN
(
    SELECT id, oldid, MIN(tmodified) AS min_mod
    FROM table_h
    WHERE final >= 0 AND final IS NOT NULL
    GROUP BY id, oldid
) t
    ON h.id     = t.id    AND
       h.oldid  = t.oldid AND
       h.tmodified = t.min_mo

Note that we could have also expressed this using row number or another analytical function, but hopefully this gives you a place to start.

edited Jul 14, 2017 at 3:43

answered Jul 14, 2017 at 2:40

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

2 Comments

Developer Over a year ago

tmodfied is time stamp, Do you think max will return last modified record?

Tim Biegeleisen Over a year ago

@Developer Thanks for catching that.

Developer · Accepted Answer · 2017-07-26 23:25:05Z

0

Vacuuming all involve table will fix the issue.

VACUUM ANALYZE table_am;
VACUUM ANALYZE table_t;
VACUUM ANALYZE table_h;
VACUUM ANALYZE table_u;
VACUUM ANALYZE table_im;
VACUUM ANALYZE table_ie;
VACUUM ANALYZE table_g;
VACUUM ANALYZE table_m;

Ref: https://www.postgresql.org/docs/9.4/static/sql-vacuum.html

answered Jul 26, 2017 at 23:25

Developer

26.5k20 gold badges86 silver badges134 bronze badges

Collectives™ on Stack Overflow

Postgres 9.4 sql query timeout

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related