Re: slow count in window query

2009/7/18 Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>:
> 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:

Kevin's result is quite odd. I confirmed that using IndexScan looked
fater in Pavel's result but yours is with Sort node.

I found that those results are seen in relatively small set. I
increased the source table up to 100000 rows and the OVER (ORDER BY a)
case got slower.

What really suprised me is in any case without ORDER BY clause in the
window, WindowAgg node starts quite later than the lower node
finishes.

> 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

I had thought WindowAgg actual time would be 14.xxx ... 97.448 but
actually 32.435 ....97.448. ORDER BY case returns the first result as
soon as underneath Sort (or IndexScan) returns the first (actually the
second), because window frame has only a row. But even the frame
contains all the row (i.e. OVER() case) can return the first row not
so later than the underneath node returns the last.

If I understand exlain analyze correctly and it tells us the fact,
WindowAgg without ORDER BY clause gets unreasonably slow. Let me see.

Regards,

--
Hitoshi Harada