Re: slow count in window query - Mailing list pgsql-hackers

2009/7/17 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
> Pavel Stehule <pavel.stehule@gmail.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 loopsTotal 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=10000Total 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
>