Re: slow count in window query

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> table was filled with random numbers and analyzed - you can simple
> check it - look on begin of the thread. This table wasn't updated.

Confirmed. The ORDER BY consistently speeds up the query. Odd....

Sort speed varied based on random sequence generated, but typical
plan and timings:

test=# explain analyze select count(*) over () from x;
WindowAgg (cost=0.00..229.00 rows=10000 width=0) (actual
time=32.435..97.448 rows=10000 loops=1)
-> Seq Scan on x (cost=0.00..104.00 rows=10000 width=0) (actual
time=0.007..14.818 rows=10000 loops=1)
Total runtime: 112.526 ms

test=# explain analyze select count(*) over (order by a) from x;
WindowAgg (cost=768.39..943.39 rows=10000 width=4) (actual
time=34.982..87.803 rows=10000 loops=1)
-> Sort (cost=768.39..793.39 rows=10000 width=4) (actual
time=34.962..49.533 rows=10000 loops=1)
Sort Key: a
Sort Method: quicksort Memory: 491kB
-> Seq Scan on x (cost=0.00..104.00 rows=10000 width=4)
(actual time=0.006..14.682 rows=10000 loops=1)
Total runtime: 102.023 ms

-Kevin