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.
