On Mon, 10 Oct 2022 at 12:45, Erwin Brandstetter <brsaweda@gmail.com> wrote: > However, unless I am missing something, there are window functions where RANGE mode makes no sense on principle, and the result is identical to ROWS mode. Among those, the most popular window function of all: row_number(). These expressions do the same: > > row_number() OVER (ORDER BY a) > > row_number() OVER (ORDER BY a ROWS UNBOUNDED PRECEDING) > > Unfortunately, the first one is substantially more expensive. Hardly anybody seems to be aware of that. I consistently see a performance penalty of around 20 % (or more). Demo for Postgres 15 with a couple of variants:
You might be onto something there. However, a lack of an optimisation is not a bug, so this is not the correct place to discuss.
If you were keen to come up with a patch, you could look at what was done in [1] and perhaps invent a new Node type that can be given to the support function so that the support function can be called to ask if the window function cares about the ROWS / RANGE option. Then in the planner, perhaps just after select_active_windows() is called, call the support function for each set of window functions in each WindowClause to see if the window function cares about this option.
This perhaps should be done more generically than just asking the support function about ROWS vs RANGE. Maybe you can just ask the support function if the frameOptions can be optimised for this window function, then if every WindowFunc in the WindowClause agrees on what those optimised frameOptions are, then you can change the WindowClause.frameOptions to the optimised set. If the given WindowFunc does not have a support function or the support function does not understand the new Node type, then you'll need to leave the WindowClause.frameOptions alone.
If you're keen to do this, then you should start a thread on the -hackers list mentioning what you'd like to do and how you plan to go about doing it. That's a good place to get feedback before you get too deep into writing a patch.