Search Postgresql Archives

Re: Combining count() and row_number() as window functions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Tom,

* Tom Lane (tgl@xxxxxxxxxxxxx) wrote:
> Thomas Kellerer <spam_eater@xxxxxxx> writes:
> > I assumed that the count() wouldn't increase the runtime of the query as the result of the row_number() can be used to calculate 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

Attachment: signature.asc
Description: Digital signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux