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
