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

Hi,

On 6/27/22 9:31 AM, Hamid Akhtar wrote:
>
> Hello Hackers,
>
> While working on one of my blogs on the B-Tree indexes
> <https://www.percona.com/blog/postgresql-14-b-tree-index-reduced-bloat-with-bottom-up-deletion/>,
> I needed to look at a range of B-Tree page statistics. So the goto
> solution was to use pageinspect. However, reviewing stats for multiple
> pages meant issuing multiple queries.

FWIW, I think you could also rely on generate_series()

> I felt that there's an opportunity for improvement in the extension by
> extending the API to output the statistics for multiple pages with a
> single query.
>
> That attached patch is based on the master branch. It makes the
> following changes to the pageinspect contrib module:
> - Updates bt_page_stats_internal function to accept 3 arguments
> instead of 2.
> - The function now uses SRF macros to return a set rather than a
> single row. The function call now requires specifying column names.
>
> The extension version is bumped to 1.11 (PAGEINSPECT_V1_11).
> To maintain backward compatibility, for versions below 1.11, the
> multi-call mechanism is ended to keep the old behavior consistent.
>
> Regression test cases for the module are updated as well as part of
> this change. Here is a subset of queries that are added to the
> btree.sql test case file for pageinspect.
>
> ----
> CREATE TABLE test2 AS (SELECT generate_series(1, 5000) AS col1);
> CREATE INDEX test2_col1_idx ON test2(col1);
> SELECT * FROM bt_page_stats('test2_col1_idx', 1, 2);

For example, this could be written as:

select * from
generate_series(1, 2) blkno ,
bt_page_stats('test2_col1_idx',blkno::int);

Or, if one wants to inspect to whole relation, something like:

select * from
generate_series(1, pg_relation_size('test2_col1_idx'::regclass::text) /
8192 - 1) blkno ,
bt_page_stats('test2_col1_idx',blkno::int);

Regards,

Bertrand