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, 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