Here's the big stick:
Replace:
ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW
with the equivalent:
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
And you'll see the same performance as with ROWS UNBOUNDED PRECEDING
.
EXCLUDE CURRENT ROW
is a later addition to window frames (with Postgres 11) and I assume (without studying the source code) that its code path is less optimized. This particular clause is hardly useful in combination with the default frame end CURRENT ROW
, which can be covered with basic window frame syntax as demonstrated. But Postgres is not currently smart enough (incl. Postgres 14) to recognize and simplify the needless complication.
There is quite a bit more that might be improved with this query, but the rest feels like paid work. :D