RE: New Window Function: ROW_NUMBER_DESC() OVER() ? - Mailing list pgsql-hackers

From Maiquel Grassi
Subject RE: New Window Function: ROW_NUMBER_DESC() OVER() ?
Date
Msg-id CP4P284MB24830F45615BDBB994BDB661EC722@CP4P284MB2483.BRAP284.PROD.OUTLOOK.COM
Whole thread Raw
In response to Re: New Window Function: ROW_NUMBER_DESC() OVER() ?  (Michał Kłeczek <michal@kleczek.org>)
List pgsql-hackers
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.

pgsql-hackers by date:

Previous
From: John Naylor
Date:
Subject: Re: [PoC] Improve dead tuple storage for lazy vacuum
Next
From: Peter Smith
Date:
Subject: Re: Synchronizing slots from primary to standby