Re: slow count in window query

2009/7/17 Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>> postgres=# explain select count(*) over () from x;
>
>>  WindowAgg  (cost=0.00..265.00 rows=10000 width=0)
>>    ->  Seq Scan on x  (cost=0.00..140.00 rows=10000 width=0)
>
>> postgres=# explain select count(*) over (order by a) from x;
>
>>  WindowAgg  (cost=0.00..556.25 rows=10000 width=4)
>>    ->  Index Scan using gg on x  (cost=0.00..406.25 rows=10000
> width=4)
>
>> query1: 160ms
>> query2: 72ms
>
> EXPLAIN ANALYZE is more telling than just EXPLAIN.

Query1

QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=931.50..931.51 rows=1 width=4) (actual
time=274.423..274.425 rows=1 loops=1)
-> Subquery Scan p (cost=0.00..931.25 rows=100 width=4) (actual
time=220.220..274.388 rows=2 loops=1)
Filter: ((p.r = ((p.rc + 1) / 2)) OR (p.r = ((p.rc + 2) / 2)))
-> WindowAgg (cost=0.00..681.25 rows=10000 width=4) (actual
time=120.622..247.618 rows=10000 loops=1)
-> WindowAgg (cost=0.00..556.25 rows=10000 width=4)
(actual time=0.088..89.848 rows=10000 loops=1)
-> Index Scan using gg on x (cost=0.00..406.25
rows=10000 width=4) (actual time=0.066..33.962 rows=10000 loops
Total runtime: 274.934 ms
(7 rows)

query2:

postgres=# explain analyze select avg(a) from (select a, row_number()
over (order by a asc) as hi, row_number() over (order by a desc) as lo
from x) s where hi in (lo-1,lo+1);

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1595.89..1595.90 rows=1 width=4) (actual
time=215.101..215.103 rows=1 loops=1)
-> Subquery Scan s (cost=1220.63..1595.63 rows=100 width=4)
(actual time=175.159..215.073 rows=1 loops=1)
Filter: ((s.hi = (s.lo - 1)) OR (s.hi = (s.lo + 1)))
-> WindowAgg (cost=1220.63..1395.63 rows=10000 width=4)
(actual time=136.985..191.231 rows=10000 loops=1)
-> Sort (cost=1220.63..1245.63 rows=10000 width=4)
(actual time=136.970..151.905 rows=10000 loops=1)
Sort Key: x.a
Sort Method: quicksort Memory: 686kB
-> WindowAgg (cost=0.00..556.25 rows=10000
width=4) (actual time=0.078..106.927 rows=10000 loops=1)
-> Index Scan using gg on x
(cost=0.00..406.25 rows=10000 width=4) (actual time=0.058..33.594
rows=10000
Total runtime: 215.845 ms
(10 rows)

>
> Did you run both several times or flush caches carefully between the
> runs to eliminate caching effects?

yes, - in both variants data was read from cache.

>
> Is it possible that there are a lot of dead rows in the table (from
> UPDATEs or DELETEs), and the table has been vacuumed?  (Output from
> VACUUM VERBOSE on the table would show that.)
>

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

Pavel

> -Kevin
>