Re: Allow pageinspect's bt_page_stats function to return a set of rows instead of a single row

On Thu, Jun 30, 2022 at 1:54 PM Hamid Akhtar <hamid(dot)akhtar(at)gmail(dot)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.

Regards,
Bharath Rupireddy.