RE: New Window Function: ROW_NUMBER_DESC() OVER() ?

But as you are the one arguing for the new feature demonstrating that the status quo is deficient is your job.

--//--

I performed these three tests(take a look below) quite simple but functional, so that we can get an idea of the performance. Apparently, we have a higher cost in using "count(*) - row_number() + 1" than in using "row_number_desc() over()".

Perhaps, if we think in terms of SQL standards, my suggested name may not have been the best. The name could be anything else. I don't have another suggestion. Does anyone have a better one? I leave it open for others to also reflect.

postgres=# select * into public.foo_1 from generate_series(1,1000000);
SELECT 1000000
postgres=# explain analyze select count(*) over() - row_number() over() + 1 from public.foo_1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=0.00..38276.25 rows=1128375 width=8) (actual time=244.878..475.595 rows=1000000 loops=1)
-> Seq Scan on foo_1 (cost=0.00..15708.75 rows=1128375 width=0) (actual time=0.033..91.486 rows=1000000 loops=1)
Planning Time: 0.073 ms
Execution Time: 505.375 ms
(4 rows)

postgres=# explain analyze select row_number_desc() over() from public.foo_1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=0.00..26925.00 rows=1000000 width=8) (actual time=141.107..427.100 rows=1000000 loops=1)
-> Seq Scan on foo_1 (cost=0.00..14425.00 rows=1000000 width=0) (actual time=0.031..61.651 rows=1000000 loops=1)
Planning Time: 0.051 ms
Execution Time: 466.535 ms
(4 rows)

postgres=# select * into public.foo_2 from generate_series(1,10000000);
SELECT 10000000
postgres=# explain analyze select count(*) over() - row_number() over() + 1 from public.foo_2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=0.00..344247.31 rows=9999977 width=8) (actual time=2621.014..5145.325 rows=10000000 loops=1)
-> Seq Scan on foo_2 (cost=0.00..144247.77 rows=9999977 width=0) (actual time=0.031..821.533 rows=10000000 loops=1)
Planning Time: 0.085 ms
Execution Time: 5473.422 ms
(4 rows)

postgres=# explain analyze select row_number_desc() over() from public.foo_2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=0.00..269247.48 rows=9999977 width=8) (actual time=1941.915..4527.896 rows=10000000 loops=1)
-> Seq Scan on foo_2 (cost=0.00..144247.77 rows=9999977 width=0) (actual time=0.029..876.802 rows=10000000 loops=1)
Planning Time: 0.030 ms
Execution Time: 4871.278 ms
(4 rows)

postgres=# select * into public.foo_3 from generate_series(1,100000000);
SELECT 100000000
postgres=# explain analyze select count(*) over() - row_number() over() + 1 from public.foo_3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=0.00..3827434.70 rows=112831890 width=8) (actual time=56823.080..84295.660 rows=100000000 loops=1)
-> Seq Scan on foo_3 (cost=0.00..1570796.90 rows=112831890 width=0) (actual time=1.010..37735.121 rows=100000000 loops=1)
Planning Time: 1.018 ms
Execution Time: 87677.572 ms
(4 rows)

postgres=# explain analyze select row_number_desc() over() from public.foo_3;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=0.00..2981195.53 rows=112831890 width=8) (actual time=29523.037..55517.349 rows=100000000 loops=1)
-> Seq Scan on foo_3 (cost=0.00..1570796.90 rows=112831890 width=0) (actual time=12.638..19050.614 rows=100000000 loops=1)
Planning Time: 55.653 ms
Execution Time: 59001.423 ms
(4 rows)

Regards,
Maiquel.