Re: slow count in window query

2009/7/17 Hitoshi Harada <umi.tanuki@gmail.com>:
> 2009/7/17 Pavel Stehule <pavel.stehule@gmail.com>:
>> Hello
>>
>> look on:
>> postgres=# explain select count(*) over () from x;
>>                         QUERY PLAN
>> -------------------------------------------------------------
>>  WindowAgg  (cost=0.00..265.00 rows=10000 width=0)
>>   ->  Seq Scan on x  (cost=0.00..140.00 rows=10000 width=0)
>> (2 rows)
>>
>> Time: 1,473 ms
>> postgres=# explain select count(*) over (order by a) from x;
>>                               QUERY PLAN
>> ------------------------------------------------------------------------
>>  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)
>> (2 rows)
>>
>> but
>> query1: 160ms
>> query2: 72ms
>
> Well, how about "select a from x order by a"?
> I wonder if index scan affects more than windowagg.
>

select a from x -> 42ms
select a from x order by a -> 50ms

all data are from cache.



> --
> Hitoshi Harada
>