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. The clause is hardly useful in combination with the default frame end CURRENT ROW to begin with, since that 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. (And I don't expect that to change for this corner case where a simple fix is available.)

Also, the default window frame is not ROWS UNBOUNDED PRECEDING, but RANGEUNBOUNDED PRECEDING - which matters more than you might think. I published details in a related answer just yesterday:

• Find first 3 orders for each customer

There is quite a bit more that might be improved in your query, but the rest feels like paid work. :)

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. The clause is hardly useful in combination with the default frame end CURRENT ROW to begin with, since that 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. (And I wouldn't hold my breath waiting for a change in this corner case where a simple fix is available.)

Also, the default window frame is not ROWS UNBOUNDED PRECEDING, but RANGEUNBOUNDED PRECEDING - which matters more than you might think. I published details in a related answer just yesterday:

• Find first 3 orders for each customer

There is quite a bit more that might be improved in your query, but the rest feels like paid work. :)

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

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. The clause is hardly useful in combination with the default frame end CURRENT ROW to begin with, since that 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. (And I don't expect that to change for this corner case where a simple fix is available.)

Also, the default window frame is not ROWS UNBOUNDED PRECEDING, but RANGEUNBOUNDED PRECEDING - which matters more than you might think. I published details in a related answer just yesterday:

• Find first 3 orders for each customer

There is quite a bit more that might be improved in your query, but the rest feels like paid work. :)