0006-Extended-vacuum-statistics-per-relation-buffer-acces.patch

From 791dacf1cc81105971add3e7dee45016c4bc7c79 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <alenka.rybakina@gmail.com>
Date: Tue, 16 Jun 2026 10:52:11 +0300
Subject: [PATCH 6/8] Extended vacuum statistics: per-relation buffer access
 for tables and indexes

Expose the per-relation buffer access counters in pg_stat_vacuum_tables and
pg_stat_vacuum_indexes, with documentation and regression coverage:

  rel_blks_read  this relation's blocks read from disk by the vacuum
  rel_blks_hit   this relation's blocks found in shared buffers

Unlike total_blks_*, which count all shared-buffer access during the vacuum,
these are restricted to the target relation's own blocks.
---
 doc/src/sgml/system-views.sgml             | 32 ++++++++++++++++++++++
 src/backend/access/heap/vacuumlazy.c       | 15 ++++++++++
 src/backend/catalog/system_views.sql       |  9 ++++--
 src/backend/utils/activity/pgstat_vacuum.c |  3 ++
 src/backend/utils/adt/pgstatfuncs.c        |  9 ++++--
 src/include/catalog/pg_proc.dat            | 12 ++++----
 src/include/pgstat.h                       |  4 +++
 src/test/regress/expected/rules.out        | 12 +++++---
 src/test/regress/expected/vacuum_stats.out | 20 +++++++++++---
 src/test/regress/sql/vacuum_stats.sql      | 10 ++++++-
 10 files changed, 107 insertions(+), 19 deletions(-)

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 088b532e5c..8e80db3e03 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -5948,6 +5948,22 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
        Number of shared buffer blocks written out by the vacuum.
       </para></entry>
      </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>rel_blks_read</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of this relation's blocks read from disk by the vacuum.
+      </para></entry>
+     </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>rel_blks_hit</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of this relation's blocks found in shared buffers by the vacuum.
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
@@ -6071,6 +6087,22 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
        Number of shared buffer blocks written out while vacuuming the index.
       </para></entry>
      </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>rel_blks_read</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of blocks of this index read from disk by the vacuum.
+      </para></entry>
+     </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>rel_blks_hit</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of block hits within this index during the vacuum.
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 9c524909fe..fdd70653e3 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -569,6 +569,19 @@ extvac_stats_end(Relation rel, LVExtStatCounters * counters,
 	report->total_blks_hit += bufusage.local_blks_hit + bufusage.shared_blks_hit;
 	report->total_blks_dirtied += bufusage.local_blks_dirtied + bufusage.shared_blks_dirtied;
 	report->total_blks_written += bufusage.shared_blks_written;
+
+	if (!rel->pgstat_info || !pgstat_track_counts)
+
+		/*
+		 * if something goes wrong or an user doesn't want to track a database
+		 * activity - just suppress it.
+		 */
+		return;
+
+	report->blks_fetched +=
+		rel->pgstat_info->counts.blocks_fetched - counters->blocks_fetched;
+	report->blks_hit +=
+		rel->pgstat_info->counts.blocks_hit - counters->blocks_hit;
 }
 
 void
@@ -691,6 +704,8 @@ extvac_accumulate_idx_report(PgStat_VacuumRelationCounts * dst,
 	dst->common.total_blks_hit += src->common.total_blks_hit;
 	dst->common.total_blks_dirtied += src->common.total_blks_dirtied;
 	dst->common.total_blks_written += src->common.total_blks_written;
+	dst->common.blks_fetched += src->common.blks_fetched;
+	dst->common.blks_hit += src->common.blks_hit;
 	dst->common.tuples_deleted += src->common.tuples_deleted;
 
 	dst->index.pages_deleted += src->index.pages_deleted;
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index b4fd8f90c6..e7d4165844 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1579,7 +1579,9 @@ CREATE VIEW pg_stat_vacuum_tables AS
         S.total_blks_read AS total_blks_read,
         S.total_blks_hit AS total_blks_hit,
         S.total_blks_dirtied AS total_blks_dirtied,
-        S.total_blks_written AS total_blks_written
+        S.total_blks_written AS total_blks_written,
+        S.rel_blks_read AS rel_blks_read,
+        S.rel_blks_hit AS rel_blks_hit
 
     FROM pg_class C JOIN
             pg_namespace N ON N.oid = C.relnamespace,
@@ -1600,7 +1602,10 @@ CREATE VIEW pg_stat_vacuum_indexes AS
             S.total_blks_read AS total_blks_read,
             S.total_blks_hit AS total_blks_hit,
             S.total_blks_dirtied AS total_blks_dirtied,
-            S.total_blks_written AS total_blks_written
+            S.total_blks_written AS total_blks_written,
+
+            S.rel_blks_read AS rel_blks_read,
+            S.rel_blks_hit AS rel_blks_hit
     FROM
             pg_class C JOIN
             pg_index X ON C.oid = X.indrelid JOIN
diff --git a/src/backend/utils/activity/pgstat_vacuum.c b/src/backend/utils/activity/pgstat_vacuum.c
index 21678bf646..9b1eaf0f4a 100644
--- a/src/backend/utils/activity/pgstat_vacuum.c
+++ b/src/backend/utils/activity/pgstat_vacuum.c
@@ -43,6 +43,9 @@ pgstat_accumulate_common(PgStat_CommonCounts *dst, const PgStat_CommonCounts *sr
 	ACCUMULATE_FIELD(total_blks_dirtied);
 	ACCUMULATE_FIELD(total_blks_written);
 
+	ACCUMULATE_FIELD(blks_fetched);
+	ACCUMULATE_FIELD(blks_hit);
+
 	ACCUMULATE_FIELD(tuples_deleted);
 }
 
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index fc32b20850..4f0be66621 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2374,7 +2374,7 @@ pg_stat_have_stats(PG_FUNCTION_ARGS)
 Datum
 pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_VACUUM_TABLES_STATS_COLS 15
+#define PG_STAT_GET_VACUUM_TABLES_STATS_COLS 17
 
 	Oid			relid = PG_GETARG_OID(0);
 	PgStat_VacuumRelationCounts *extvacuum;
@@ -2414,6 +2414,8 @@ pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
 	values[i++] = Int64GetDatum(extvacuum->common.total_blks_hit);
 	values[i++] = Int64GetDatum(extvacuum->common.total_blks_dirtied);
 	values[i++] = Int64GetDatum(extvacuum->common.total_blks_written);
+	values[i++] = Int64GetDatum(extvacuum->common.blks_fetched - extvacuum->common.blks_hit);
+	values[i++] = Int64GetDatum(extvacuum->common.blks_hit);
 
 	Assert(i == PG_STAT_GET_VACUUM_TABLES_STATS_COLS);
 
@@ -2427,7 +2429,7 @@ pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
 Datum
 pg_stat_get_vacuum_indexes(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_VACUUM_INDEX_STATS_COLS 7
+#define PG_STAT_GET_VACUUM_INDEX_STATS_COLS 9
 
 	Oid			relid = PG_GETARG_OID(0);
 	PgStat_VacuumRelationCounts *extvacuum;
@@ -2460,6 +2462,9 @@ pg_stat_get_vacuum_indexes(PG_FUNCTION_ARGS)
 	values[i++] = Int64GetDatum(extvacuum->common.total_blks_dirtied);
 	values[i++] = Int64GetDatum(extvacuum->common.total_blks_written);
 
+	values[i++] = Int64GetDatum(extvacuum->common.blks_fetched - extvacuum->common.blks_hit);
+	values[i++] = Int64GetDatum(extvacuum->common.blks_hit);
+
 	Assert(i == PG_STAT_GET_VACUUM_INDEX_STATS_COLS);
 
 	/* Returns the record as Datum */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index edf3cc8b62..14f070a941 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12643,9 +12643,9 @@
   proname => 'pg_stat_get_vacuum_tables', prorows => 1000, provolatile => 's', prorettype => 'record', proisstrict => 'f',
   proretset => 't',
   proargtypes => 'oid',
-  proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8}',
-  proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
-  proargnames => '{reloid,relid,pages_scanned,pages_removed,tuples_deleted,tuples_frozen,recently_dead_tuples,missed_dead_pages,missed_dead_tuples,vm_new_frozen_pages,vm_new_visible_pages,vm_new_visible_frozen_pages,total_blks_read,total_blks_hit,total_blks_dirtied,total_blks_written}',
+  proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8}',
+  proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
+  proargnames => '{reloid,relid,pages_scanned,pages_removed,tuples_deleted,tuples_frozen,recently_dead_tuples,missed_dead_pages,missed_dead_tuples,vm_new_frozen_pages,vm_new_visible_pages,vm_new_visible_frozen_pages,total_blks_read,total_blks_hit,total_blks_dirtied,total_blks_written,rel_blks_read,rel_blks_hit}',
   prosrc => 'pg_stat_get_vacuum_tables' }
 
 # oid8 related functions
@@ -12718,9 +12718,9 @@
   proname => 'pg_stat_get_vacuum_indexes', prorows => 1000, provolatile => 's', prorettype => 'record', proisstrict => 'f',
   proretset => 't',
   proargtypes => 'oid',
-  proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8}',
-  proargmodes => '{i,o,o,o,o,o,o,o}',
-  proargnames => '{reloid,relid,pages_deleted,tuples_deleted,total_blks_read,total_blks_hit,total_blks_dirtied,total_blks_written}',
+  proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8}',
+  proargmodes => '{i,o,o,o,o,o,o,o,o,o}',
+  proargnames => '{reloid,relid,pages_deleted,tuples_deleted,total_blks_read,total_blks_hit,total_blks_dirtied,total_blks_written,rel_blks_read,rel_blks_hit}',
   prosrc => 'pg_stat_get_vacuum_indexes' },
 { oid => '8005',
   descr => 'pg_stat_get_vacuum_database returns vacuum stats values for database',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 6a9c9800b7..de38949d12 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -181,6 +181,10 @@ typedef struct PgStat_CommonCounts
 	int64		total_blks_dirtied;
 	int64		total_blks_written;
 
+	/* heap blocks */
+	int64		blks_fetched;
+	int64		blks_hit;
+
 	/* tuples */
 	int64		tuples_deleted;
 }			PgStat_CommonCounts;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 7b67fe72a6..0b28e2ffc3 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2440,12 +2440,14 @@ pg_stat_vacuum_indexes| SELECT c.oid AS relid,
     s.total_blks_read,
     s.total_blks_hit,
     s.total_blks_dirtied,
-    s.total_blks_written
+    s.total_blks_written,
+    s.rel_blks_read,
+    s.rel_blks_hit
    FROM (((pg_class c
      JOIN pg_index x ON ((c.oid = x.indrelid)))
      JOIN pg_class i ON ((i.oid = x.indexrelid)))
      LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))),
-    LATERAL pg_stat_get_vacuum_indexes(i.oid) s(relid, pages_deleted, tuples_deleted, total_blks_read, total_blks_hit, total_blks_dirtied, total_blks_written)
+    LATERAL pg_stat_get_vacuum_indexes(i.oid) s(relid, pages_deleted, tuples_deleted, total_blks_read, total_blks_hit, total_blks_dirtied, total_blks_written, rel_blks_read, rel_blks_hit)
   WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
 pg_stat_vacuum_tables| SELECT n.nspname AS schemaname,
     c.relname,
@@ -2463,10 +2465,12 @@ pg_stat_vacuum_tables| SELECT n.nspname AS schemaname,
     s.total_blks_read,
     s.total_blks_hit,
     s.total_blks_dirtied,
-    s.total_blks_written
+    s.total_blks_written,
+    s.rel_blks_read,
+    s.rel_blks_hit
    FROM (pg_class c
      JOIN pg_namespace n ON ((n.oid = c.relnamespace))),
-    LATERAL pg_stat_get_vacuum_tables(c.oid) s(relid, pages_scanned, pages_removed, tuples_deleted, tuples_frozen, recently_dead_tuples, missed_dead_pages, missed_dead_tuples, vm_new_frozen_pages, vm_new_visible_pages, vm_new_visible_frozen_pages, total_blks_read, total_blks_hit, total_blks_dirtied, total_blks_written)
+    LATERAL pg_stat_get_vacuum_tables(c.oid) s(relid, pages_scanned, pages_removed, tuples_deleted, tuples_frozen, recently_dead_tuples, missed_dead_pages, missed_dead_tuples, vm_new_frozen_pages, vm_new_visible_pages, vm_new_visible_frozen_pages, total_blks_read, total_blks_hit, total_blks_dirtied, total_blks_written, rel_blks_read, rel_blks_hit)
   WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
 pg_stat_wal| SELECT wal_records,
     wal_fpi,
diff --git a/src/test/regress/expected/vacuum_stats.out b/src/test/regress/expected/vacuum_stats.out
index 6c46239eb6..bf2fbc7626 100644
--- a/src/test/regress/expected/vacuum_stats.out
+++ b/src/test/regress/expected/vacuum_stats.out
@@ -114,6 +114,16 @@ SELECT total_blks_read >= 0 AS total_blks_read,
  t               | t              | t                  | t
 (1 row)
 
+-- per-relation buffer access.  The heap is read through the buffer cache
+-- (rel_blks_hit > 0); rel_blks_read depends on the run-time cache state.
+SELECT rel_blks_read >= 0 AS rel_blks_read,
+       rel_blks_hit > 0 AS rel_blks_hit
+  FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_t';
+ rel_blks_read | rel_blks_hit 
+---------------+--------------
+ t             | t
+(1 row)
+
 -- per-index view: the primary key index is processed by the same VACUUM.
 -- No btree leaf empties out (interleaved deletions), so pages_deleted = 0,
 -- while every index entry for a removed heap tuple is deleted.  The index is
@@ -125,11 +135,13 @@ SELECT indexrelname,
        total_blks_read >= 0 AS total_blks_read,
        total_blks_hit > 0 AS total_blks_hit,
        total_blks_dirtied >= 0 AS total_blks_dirtied,
-       total_blks_written >= 0 AS total_blks_written
+       total_blks_written >= 0 AS total_blks_written,
+       rel_blks_read >= 0 AS rel_blks_read,
+       rel_blks_hit > 0 AS rel_blks_hit
   FROM pg_stat_vacuum_indexes WHERE relname = 'vacstat_t' ORDER BY indexrelname;
-  indexrelname  | pages_deleted | tuples_deleted | total_blks_read | total_blks_hit | total_blks_dirtied | total_blks_written 
-----------------+---------------+----------------+-----------------+----------------+--------------------+--------------------
- vacstat_t_pkey | t             | t              | t               | t              | t                  | t
+  indexrelname  | pages_deleted | tuples_deleted | total_blks_read | total_blks_hit | total_blks_dirtied | total_blks_written | rel_blks_read | rel_blks_hit 
+----------------+---------------+----------------+-----------------+----------------+--------------------+--------------------+---------------+--------------
+ vacstat_t_pkey | t             | t              | t               | t              | t                  | t                  | t             | t
 (1 row)
 
 -- index page-deletion path: deleting a contiguous key range empties whole
diff --git a/src/test/regress/sql/vacuum_stats.sql b/src/test/regress/sql/vacuum_stats.sql
index 91079759ea..49ed3b4063 100644
--- a/src/test/regress/sql/vacuum_stats.sql
+++ b/src/test/regress/sql/vacuum_stats.sql
@@ -88,6 +88,12 @@ SELECT total_blks_read >= 0 AS total_blks_read,
        total_blks_written >= 0 AS total_blks_written
   FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_t';
 
+-- per-relation buffer access.  The heap is read through the buffer cache
+-- (rel_blks_hit > 0); rel_blks_read depends on the run-time cache state.
+SELECT rel_blks_read >= 0 AS rel_blks_read,
+       rel_blks_hit > 0 AS rel_blks_hit
+  FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_t';
+
 -- per-index view: the primary key index is processed by the same VACUUM.
 -- No btree leaf empties out (interleaved deletions), so pages_deleted = 0,
 -- while every index entry for a removed heap tuple is deleted.  The index is
@@ -99,7 +105,9 @@ SELECT indexrelname,
        total_blks_read >= 0 AS total_blks_read,
        total_blks_hit > 0 AS total_blks_hit,
        total_blks_dirtied >= 0 AS total_blks_dirtied,
-       total_blks_written >= 0 AS total_blks_written
+       total_blks_written >= 0 AS total_blks_written,
+       rel_blks_read >= 0 AS rel_blks_read,
+       rel_blks_hit > 0 AS rel_blks_hit
   FROM pg_stat_vacuum_indexes WHERE relname = 'vacstat_t' ORDER BY indexrelname;
 
 -- index page-deletion path: deleting a contiguous key range empties whole
-- 
2.39.5 (Apple Git-154)