Default framing option RANGE adds cost for no gain to some window functions

From: Erwin Brandstetter <brsaweda(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Default framing option RANGE adds cost for no gain to some window functions
Date: 2022-10-09 23:45:01
Message-ID: CAGHENJ7LBBszxS+SkWWFVnBmOT2oVsBhDMB1DFrgerCeYa_DyA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The manual states:
https://www.postgresql.org/docs/15/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

> 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:

https://dbfiddle.uk/kHMyTMoY

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David Rowley 2022-10-10 04:21:02 Re: Default framing option RANGE adds cost for no gain to some window functions
Previous Message Tom Lane 2022-10-09 22:53:50 Re: BUG #17630: pg_dump error