Re: slow count in window query

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.
Did you run both several times or flush caches carefully between the
runs to eliminate caching effects?
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.)
-Kevin