AnalyticDB:pg_buffercache

shared_buffer_stat_master_lockfree

Returns cache usage of each table and index on the coordinator node, sorted by buffer occupancy.

SELECT * FROM shared_buffer_stat_master_lockfree;

Sample output:

           relname         | percentage_in_shared_buffer | pages_in_shared_buffer
---------------------------+-----------------------------+------------------------
 others                    |                     0.53125 |                     85
 pg_depend_reference_index |                     0.09375 |                     15
 pg_depend                 |                     0.05625 |                      9
 ...

Use this view to identify which tables and indexes dominate the coordinator's buffer pool and whether to adjust the cache allocation policy or data access patterns.

shared_buffer_stat_segments_lockfree

Returns cache usage of each table and index across compute nodes. The percentage_in_shared_buffer value is the average across all compute nodes.

SELECT * FROM shared_buffer_stat_segments_lockfree;

Sample output:

        relname         | percentage_in_shared_buffer
------------------------+-----------------------------
 gp_distribution_policy |                     0.00625
 pg_namespace_oid_index |                      0.0125
 pg_rewrite             |                     0.00625
...

shared_buffer_summary_master_lockfree

Returns overall buffer utilization on the coordinator node as a single summary row.

SELECT * FROM shared_buffer_summary_master_lockfree;

Sample output:

 pages_in_use | pages_marked_dirty | pages_total
--------------+--------------------+-------------
          594 |                594 |       16000

shared_buffer_summary_segments_lockfree

Returns overall buffer utilization aggregated across all compute nodes.

SELECT * FROM shared_buffer_summary_segments_lockfree;

Sample output:

 pages_in_use | pages_marked_dirty | pages_total
--------------+--------------------+-------------
        11328 |              11328 |       98304
...

The columns are the same as shared_buffer_summary_master_lockfree, but the values represent overall statistics of all compute nodes.

relation_shared_buffer_stat_lockfree

Returns cache hit statistics for a specific table or index.

SELECT * FROM relation_shared_buffer_stat_lockfree('<table or index name>'::regclass);

Example:

SELECT * FROM relation_shared_buffer_stat_lockfree('adbpg_autoanalyze_test_table'::regclass);

Sample output:

           relname            | total_pages | pages_in_shared_buffer
------------------------------+-------------+------------------------
 adbpg_autoanalyze_test_table |       20055 |                   3327
(1 row)

Compare pages_in_shared_buffer against total_pages to estimate the actual cache hit ratio. A low ratio may indicate that the cache allocation policy or data access pattern needs adjustment.

relation_detail_shared_buffer_stat_lockfree

Returns cache statistics for a table and its related indexes and TOAST table in one query.

• If you specify a table name, the result includes the table, its indexes, and the associated TOAST table.

• If you specify an index name, only the index statistics are returned.

SELECT * FROM relation_detail_shared_buffer_stat_lockfree('<table or index name>'::regclass);

Example:

SELECT * FROM relation_detail_shared_buffer_stat_lockfree('adbpg_autoanalyze_test_table'::regclass);

Sample output:

           relname            | total_pages | pages_in_shared_buffer
------------------------------+-------------+------------------------
 adbpg_autoanalyze_test_table |       20055 |                   3327
(1 row)

In this example, 3,327 of 20,055 pages of adbpg_autoanalyze_test_table are cached. If no pages of the related adbpg_autoanalyze_test_table_i_idx index are cached, index-based queries will not benefit from the buffer and may perform poorly.

Use this view when you need a complete picture of how a table's entire object graph (table, indexes, TOAST) is cached — especially before deciding whether to preload data with pg_prewarm.