Why does adding a window function make this query so slow?

Question

Query A executes in microseconds:

SELECT t1.id
 FROM (SELECT t0.id AS id FROM t0) AS t1
 WHERE NOT (EXISTS (SELECT 1
        FROM t2
        WHERE t2.ph_id = t1.id AND t2.me_id = 1 AND t2.rt_id = 4))
 LIMIT 20 OFFSET 0

But query B takes about 25 seconds:

SELECT t1.id, count(*) OVER () AS count
 FROM (SELECT t0.id AS id FROM t0) AS t1
 WHERE NOT (EXISTS (SELECT 1
        FROM t2
        WHERE t2.ph_id = t1.id AND t2.me_id = 1 AND t2.rt_id = 4))
 LIMIT 20 OFFSET 0

(the difference is just one item in the select clause - a window aggregate)

EXPLAIN output is as follows, for A:

 Limit  (cost=0.00..1.20 rows=20 width=4)
   ->  Nested Loop Anti Join  (cost=0.00..3449.22 rows=57287 width=4)
         Join Filter: (t2.ph_id = t0.id)
         ->  Seq Scan on t0  (cost=0.00..1323.88 rows=57288 width=4)
         ->  Materialize  (cost=0.00..1266.02 rows=1 width=4)
               ->  Seq Scan on t2  (cost=0.00..1266.01 rows=1 width=4)
                     Filter: ((me_id = 1) AND (rt_id = 4))

And for B:

 Limit  (cost=0.00..1.45 rows=20 width=4)
   ->  WindowAgg  (cost=0.00..4165.31 rows=57287 width=4)
         ->  Nested Loop Anti Join  (cost=0.00..3449.22 rows=57287 width=4)
               Join Filter: (t2.ph_id = t0.id)
               ->  Seq Scan on t0  (cost=0.00..1323.88 rows=57288 width=4)
               ->  Materialize  (cost=0.00..1266.02 rows=1 width=4)
                     ->  Seq Scan on t2  (cost=0.00..1266.01 rows=1 width=4)
                           Filter: ((me_id = 1) AND (rt_id = 4))

I am adding the window aggregate to get the total number of rows before LIMITing, for the purpose of building a paging UI.

Gordon Linoff · Accepted Answer · 2014-09-06 21:39:54Z

3

Your original query can be written like this:

SELECT t0.id
FROM t0
WHERE NOT EXISTS (SELECT 1
                  FROM t2
                  WHERE t2.ph_id = t1.id AND t2.me_id = 1 AND t2.rt_id = 4
                 )
LIMIT 20 OFFSET 0;

You have no order by, so the query can start returning results as they are found for the result set. When you add the window function:

SELECT t.0.id, count(*) over ()

Now it is counting the number of rows in the result set, so it has to generate the entire result set. Hence, instead of just getting the first twenty rows, the query has to generate all of them. That takes more time.

answered Sep 6, 2014 at 21:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Bulat · Accepted Answer · 2014-09-06 21:41:10Z

3

Can you check how long COUN(*) takes and what execution plan looks like:

SELECT count(*)
FROM (SELECT t0.id AS id FROM t0) AS t1
WHERE NOT (EXISTS (SELECT 1
        FROM t2
        WHERE t2.ph_id = t1.id AND t2.me_id = 1 AND t2.rt_id = 4))

This might give you an idea why it takes longer.

Basically first query only reads 20 first records that match the criteria from t0, while second query has to generate the full set of records matching the criteria to count them.

answered Sep 6, 2014 at 21:41

Bulat

6,9992 gold badges32 silver badges54 bronze badges

Comments

jl6 · Accepted Answer · 2014-09-07 20:09:50Z

1

Thank you for the other answers, which are correct in that the counting has to do more work, but I found the solution from another source. The statistics were not up to date.

After running the command...:

ANALYZE;

... Postgresql was able to choose a more appropriate query plan, and now both queries run very fast.

answered Sep 7, 2014 at 20:09

jl6

6,5227 gold badges42 silver badges68 bronze badges

Collectives™ on Stack Overflow

Why does adding a window function make this query so slow?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related