On Thu, Jun 30, 2022 at 1:54 PM Hamid Akhtar <hamid.akhtar@gmail.com> wrote: > >> Do we have any >> difference in the execution times for the above query vs the new >> function introduced in the v1 patch? If there's not much difference, I >> would suggest adding an SQL function around the generate_series >> approach in the pageinspect extension for better and easier usability. > > > Based on some basic SQL execution time comparison of the two approaches, I see that the API change, on average, is around 40% faster than the SQL. > > CREATE TABLE test2 AS (SELECT generate_series(1, 5000000) AS col1); > CREATE INDEX test2_col1_idx ON test2(col1); > > EXPLAIN ANALYZE > SELECT * FROM bt_page_stats('test2_col1_idx', 1, 5000); > > EXPLAIN ANALYZE > SELECT * FROM GENERATE_SERIES(1, 5000) blkno, bt_page_stats('test2_col1_idx',blkno::int); > > For me, the API change returns back the data in around 74ms whereas the SQL returns it in 102ms. So considering this and as you mentioned, the alternative may not be that obvious to everyone, it is a fair improvement.
I'm wondering what happens with a bit of huge data and different test cases each test case executed, say, 2 or 3 times.
If the difference in execution times is always present, then the API approach or changing the core function would make more sense.
Technically, AFAIK, the performance difference will always be there. Firstly, in the API change, there is no additional overhead of the generate_series function. Additionally, with API change, looping over the pages has a smaller overhead when compared with the overhead of the SQL approach.