Re: [GENERAL] Combining count() and row_number() as window functions

Tom,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Thomas Kellerer <spam_eater@gmx.net> writes:
> > I assumed that the count() wouldn't increase the runtime of the query as the result of the row_number() can be used
tocalculate that.  
>
> No such knowledge exists in Postgres.  Given our general approach in which
> functions (including window functions) are black boxes, it's hard to see
> how it could be done in a way that wasn't a ugly kluge.

No, but what's interesting about this is that the WindowAgg count(*)
query is, apparently, quite a bit slower than the subquery with regular
aggregate of count(*), but the WindowAgg plan is costed out as being
cheaper.

I put the two up if anyone else finds that easier to read:

https://explain.depesz.com/s/bc67
https://explain.depesz.com/s/UWZt

That said, it probably doesn't matter if it was costed cheaper since I
don't think we would actually consider running an aggregate with an
'OVER ()' clause as a regular aggregate instead of as a WindowAgg.  I
don't know how expensive it would be to consider such a path, but it
seems like it might not be too bad since you would only look at those
cases if it's an empty window clause, which should be cheap to check.

The other thing I wonder about is if there's some way we could make the
WindowAgg query faster through code changes in how the windowing
count(*) is called.

I've not really looked at any code, this is all pure speculation, so
feel free to ignore me if I'm completely off-base here. :)

Thanks!

Stephen