The manual states:
> The default framing option is RANGE UNBOUNDED PRECEDING, ...
This seems to be mandated by the SQL standard.
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:
This looks like a performance. Either RANGE mode should only apply to window functions where it actually makes a difference. Or it should be optimized internally to use the faster code path where there is no effective difference.
(In an ideal world, the default mode would be ROWS to begin with, as this is more intuitive. But too late now.)
Regards
Erwin Brandstetter
