I doubt it is materially different, you need that count regardless so the effort is expended no matter if you put it in an SQL expression or build it into the window function. But as you are the one arguing for the new feature demonstrating that the status quo is deficient is your job.
---//---
Ok, I'll run the tests to validate these performances and draw some conclusions.
However, initially, I have one more obstacle in your feedback. If I use count(*) over() - row_number() over(), it gives me an offset of one unit. To resolve this, I need to add 1. This way, simulating a reverse row_number() becomes even more laborious.
SELECT
row_number() over()
, row_number_desc() over()
, count(*) over() - row_number() over() as FROM pg_catalog.pg_database;
row_number | row_number_desc | count_minus_row_number
------------+-----------------+------------------------
1 | 3 | 2
2 | 2 | 1
3 | 1 | 0
(3 rows)
postgres=# SELECT row_number() over(), row_number_desc() over(), count(*) over() - row_number() over() as count_minus_row_number, count(*) over() - row_number() over() + 1 AS count_minus_row_number_plus_one FROM pg_catalog.pg_database;
row_number | row_number_desc | count_minus_row_number | count_minus_row_number_plus_one
------------+-----------------+------------------------+---------------------------------
1 | 3 | 2 | 3
2 | 2 | 1 | 2
3 | 1 | 0 | 1
(3 rows)
Tks,
Maiquel.
