Re: slow count in window query

2009/7/16 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> I'm also not sure how to handle this if the set has to be spooled to
>> disk. Quicksort and Quickselect do a lot of scans throught he data and
>> wouldn't perform well on disk.
>
> I thing, so problem is in aggregate func used as window func - or some
> missing optimalisation.
>
> when I replaced count(*) over () by subselect (SELECT count(*) FROM
> ...) then I got expected speed.
>

WindowAgg always spools its input in the buffer though (in your case)
it throws away row by row, so compared with pure aggregate it has
overhead. I think this is reasonable approach for large data situation
and different type of window. But yes, we must improve the current
model.

1) There should be some kind of lightweight approach for such
small-data/simple-window situations.

2) tuplestore_puttupleslot() seems to me heavy (copy, check, etc) even
if the data fits in the memory by triming rows. We want to have more
flexible temporary storage on the fly.

Regards,

--
Hitoshi Harada