Hi hackers,
We currently have some stats in pg_stat_all_tables[|indexes] but we lack
visibility
into which specific backends are generating this activity.
PFA, a patch series to add $SUBJECT.
It currently displays a few stats through a new pg_stat_backend view (same as
the one already introduced in [1]). This view displays one row per server
process,
showing statistics related to the current activity of that process.
It currently provides something like:
pid | seq_scan | seq_tup_read | idx_tup_fetch | idx_scan | idx_tup_read |
vacuum_count | last_vacuum | analyze_count | last_analyze |
stats_reset
---------+----------+--------------+---------------+----------+--------------+--------------+-------------------------------+---------------+--------------+-------------
3583274 | 0 | 0 | 20 | 11 | 20 |
0 | | 0 | |
3583278 | 3 | 420 | 77 | 60 | 77 |
1 | 2025-08-12 05:47:37.546794+00 | 0 | |
3583371 | 4 | 1 | 11444 | 11428 | 13167 |
0 | | 0 | |
3583372 | 4 | 1 | 11404 | 11388 | 13110 |
0 | | 0 | |
3583373 | 4 | 1 | 11412 | 11396 | 13116 |
0 | | 0 | |
3583272 | 0 | 0 | 0 | 0 | 0 |
0 | | 0 | |
3583270 | 0 | 0 | 0 | 0 | 0 |
0 | | 0 | |
(7 rows)
Let's see some use case examples:
- Debug a specific increase in IO reads: When pg_stat_get_backend_io() shows
an increase in reads/read_bytes for backends, this new view allows deeper
investigation to determine if those reads are caused by table sequential scans
and how many rows are involved.
- One could check if some backends are triggering manual vacuums or analyzes and
check the last time they did so.
- Verify load distribution among sessions: Check if database activities are
evenly distributed across backends (in the pgbench example above, backends
3583371, 3583372, 3583373 show similar activity patterns). Uneven distribution
could indicate outdated application versions on some hosts or suboptimal
connection pool configurations.
- Set up monitoring alerts for backends showing unusual database access
patterns.
Also thanks to the pid, we can join pg_stat_activity and then, for example:
- If backends from one host show significantly more sequential scans
than others, this could indicate an outdated application version on that
host running inefficient queries.
- One could categorize applications based on workload patterns and track how
different applications use the engine.
Patch series structure:
0001 -
Adds a new PgStat_BackendRelPending struct to store those pending statistics and
then relies on the existing per backend statistics machinery that has been added
in 9aea73fc61d. It also adds a new counter (heap_scan) to record the number of
sequential scans initiated on tables.
0002 -
Adds the pg_stat_backend view
This view displays one row per server process, showing statistics related to
the current activity of that process. It currently displays the pid, the
number of sequential scans initiated on a table and the
time at which these statistics were last reset.
It's built on top of a new function (pg_stat_get_backend_statistics()). The idea
is the same as pg_stat_activity and pg_stat_get_activity().
>From 0003 to 0010 -
Adds one counter at a time. These are purely mechanical changes split for easier
review (they don't need to be committed separately).
A few remarks:
- What I don't like that much is that due to the fact that we want to avoid
exposing PendingBackendStats to the outside world (see the related comment in
pgstat_backend.c): then those counters need extra functions calls. Those
functions
are very simple and could just be inlined as macros (avoiding the extra
functions
calls). Maybe we could re-consider hiding PendingBackendStats to the outside
world?
- We could add more stats, for example the n_tup_* ones. I did add those in
this patch series, as I want to get your feedback first about the whole idea.
- With those new counters in place, PgStat_BackendPending grows from 2880
to 2952 (72) bytes and PgStat_Backend from 2920 to 2992 (72) bytes. I think
that the memory increase is relatively small.
[1]:
https://www.postgresql.org/message-id/aJhOfZ2c7XdHXOAU%40ip-10-97-1-34.eu-west-3.compute.internal
Looking forward to your feedback,
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
>From 9e2f8cb9a87f1d9be91f2f39ef25fbb254944968 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Mon, 4 Aug 2025 08:14:02 +0000
Subject: [PATCH v1 01/10] Adding per backend relation statistics tracking
This commit introduces per backend relation stats tracking and adds a
new PgStat_BackendRelPending struct to store the pending statistics. To begin with,
this commit adds a new counter (heap_scan) to record the number of sequential
scans initiated on tables.
This commit relies on the existing per backend statistics machinery that has been
added in 9aea73fc61d.
---
src/backend/access/heap/heapam.c | 3 ++
src/backend/utils/activity/pgstat_backend.c | 59 +++++++++++++++++++++
src/include/pgstat.h | 14 +++++
src/include/utils/pgstat_internal.h | 3 +-
src/tools/pgindent/typedefs.list | 1 +
5 files changed, 79 insertions(+), 1 deletion(-)
73.9% src/backend/utils/activity/
7.4% src/include/utils/
15.4% src/include/
3.2% src/
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index 0dcd6ee817e..d9d6fb6c6ea 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -467,7 +467,10 @@ initscan(HeapScanDesc scan, ScanKey key, bool keep_startblock)
* and for sample scans we update stats for tuple fetches).
*/
if (scan->rs_base.rs_flags & SO_TYPE_SEQSCAN)
+ {
pgstat_count_heap_scan(scan->rs_base.rs_rd);
+ pgstat_count_backend_rel_heap_scan();
+ }
}
/*
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index 8714a85e2d9..8797dac68c7 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -47,6 +47,11 @@ static bool backend_has_iostats = false;
*/
static WalUsage prevBackendWalUsage;
+/*
+ * For backend relations's related statistics.
+ */
+static bool backend_has_relstats = false;
+
/*
* Utility routines to report I/O stats for backends, kept here to avoid
* exposing PendingBackendStats to the outside world.
@@ -259,6 +264,39 @@ pgstat_flush_backend_entry_wal(PgStat_EntryRef *entry_ref)
prevBackendWalUsage = pgWalUsage;
}
+/*
+ * Flush out locally pending backend relations's related statistics. Locking is
+ * managed by the caller.
+ */
+static void
+pgstat_flush_backend_entry_rel(PgStat_EntryRef *entry_ref)
+{
+ PgStatShared_Backend *shbackendent;
+
+ /*
+ * This function can be called even if nothing at all has happened for
+ * relations's related statistics. In this case, avoid unnecessarily
+ * modifying the stats entry.
+ */
+ if (!backend_has_relstats)
+ return;
+
+ shbackendent = (PgStatShared_Backend *) entry_ref->shared_stats;
+
+#define BACKENDREL_ACC(stat) \
+ (shbackendent->stats.stat += PendingBackendStats.pending_backendrel.stat)
+
+ BACKENDREL_ACC(heap_scan);
+#undef BACKENDREL_ACC
+
+ /*
+ * Clear out the statistics buffer, so it can be re-used.
+ */
+ MemSet(&PendingBackendStats.pending_backendrel, 0, sizeof(PgStat_BackendRelPending));
+
+ backend_has_relstats = false;
+}
+
/*
* Flush out locally pending backend statistics
*
@@ -283,6 +321,10 @@ pgstat_flush_backend(bool nowait, bits32 flags)
pgstat_backend_wal_have_pending())
has_pending_data = true;
+ /* Some relations related data pending? */
+ if ((flags & PGSTAT_BACKEND_FLUSH_REL) && backend_has_relstats)
+ has_pending_data = true;
+
if (!has_pending_data)
return false;
@@ -298,6 +340,9 @@ pgstat_flush_backend(bool nowait, bits32 flags)
if (flags & PGSTAT_BACKEND_FLUSH_WAL)
pgstat_flush_backend_entry_wal(entry_ref);
+ if (flags & PGSTAT_BACKEND_FLUSH_REL)
+ pgstat_flush_backend_entry_rel(entry_ref);
+
pgstat_unlock_entry(entry_ref);
return false;
@@ -400,3 +445,17 @@ pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts)
{
((PgStatShared_Backend *) header)->stats.stat_reset_timestamp = ts;
}
+
+#define PGSTAT_COUNT_BACKEND_FUNC(stat) \
+void \
+CppConcat(pgstat_count_backend_rel_,stat)(void) \
+{ \
+ if (!pgstat_tracks_backend_bktype(MyBackendType)) \
+ return; \
+ PendingBackendStats.pending_backendrel.stat++; \
+ backend_has_relstats = true; \
+ pgstat_report_fixed = true; \
+}
+
+/* pgstat_count_backend_rel_heap_scan */
+PGSTAT_COUNT_BACKEND_FUNC(heap_scan)
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 202bd2d5ace..7b4dacd27f8 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -490,8 +490,14 @@ typedef struct PgStat_Backend
TimestampTz stat_reset_timestamp;
PgStat_BktypeIO io_stats;
PgStat_WalCounters wal_counters;
+ PgStat_Counter heap_scan;
} PgStat_Backend;
+typedef struct PgStat_BackendRelPending
+{
+ PgStat_Counter heap_scan;
+} PgStat_BackendRelPending;
+
/* ---------
* PgStat_BackendPending Non-flushed backend stats.
* ---------
@@ -502,6 +508,11 @@ typedef struct PgStat_BackendPending
* Backend statistics store the same amount of IO data as PGSTAT_KIND_IO.
*/
PgStat_PendingIO pending_io;
+
+ /*
+ * Backend statistics related to relations.
+ */
+ PgStat_BackendRelPending pending_backendrel;
} PgStat_BackendPending;
/*
@@ -563,6 +574,9 @@ extern PgStat_Backend *pgstat_fetch_stat_backend_by_pid(int pid,
extern bool pgstat_tracks_backend_bktype(BackendType bktype);
extern void pgstat_create_backend(ProcNumber procnum);
+/* used to track backend relations related stats */
+extern void pgstat_count_backend_rel_heap_scan(void);
+
/*
* Functions in pgstat_bgwriter.c
*/
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 6cf00008f63..286249c0f3a 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -616,7 +616,8 @@ extern void pgstat_archiver_snapshot_cb(void);
/* flags for pgstat_flush_backend() */
#define PGSTAT_BACKEND_FLUSH_IO (1 << 0) /* Flush I/O statistics */
#define PGSTAT_BACKEND_FLUSH_WAL (1 << 1) /* Flush WAL statistics */
-#define PGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO | PGSTAT_BACKEND_FLUSH_WAL)
+#define PGSTAT_BACKEND_FLUSH_REL (1 << 2) /* Flush relations related statistics */
+#define PGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO | PGSTAT_BACKEND_FLUSH_WAL | PGSTAT_BACKEND_FLUSH_REL)
extern bool pgstat_flush_backend(bool nowait, bits32 flags);
extern bool pgstat_backend_flush_cb(bool nowait);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e6f2e93b2d6..61e85da269c 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2226,6 +2226,7 @@ PgStatShared_Wal
PgStat_ArchiverStats
PgStat_Backend
PgStat_BackendPending
+PgStat_BackendRelPending
PgStat_BackendSubEntry
PgStat_BgWriterStats
PgStat_BktypeIO
--
2.34.1
>From 04bd29f1b790b63039cebfef555dce6b081d976f Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Sat, 9 Aug 2025 14:22:36 +0000
Subject: [PATCH v1 02/10] Adding the pg_stat_backend view
This view displays one row per server process, showing statistics related to
the current activity of that process. It currently displays the pid, the
number of sequential scans initiated on tables and the
time at which these statistics were last reset.
It's built on top of a new function (pg_stat_get_backend_statistics()). The idea
is the same as pg_stat_activity and pg_stat_get_activity().
Adding documentation and tests.
XXX: Bump catversion
---
doc/src/sgml/monitoring.sgml | 95 ++++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 7 ++
src/backend/utils/adt/pgstatfuncs.c | 55 ++++++++++++++++
src/include/catalog/pg_proc.dat | 9 +++
src/test/regress/expected/rules.out | 4 ++
src/test/regress/expected/stats.out | 10 +++
src/test/regress/sql/stats.sql | 8 +++
7 files changed, 188 insertions(+)
50.8% doc/src/sgml/
26.9% src/backend/utils/adt/
8.0% src/include/catalog/
7.1% src/test/regress/expected/
4.0% src/test/regress/sql/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 3f4a27a736e..6868af55db4 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -320,6 +320,19 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry>
+ <structname>pg_stat_backend</structname>
+ <indexterm><primary>pg_stat_backend</primary></indexterm>
+ </entry>
+ <entry>
+ One row per server process, showing statistics related to
+ the current activity of that process, such as number of sequential scans.
+ See <link linkend="monitoring-pg-stat-backend-view">
+ <structname>pg_stat_backend</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_replication</structname><indexterm><primary>pg_stat_replication</primary></indexterm></entry>
<entry>One row per WAL sender process, showing statistics about
@@ -1172,6 +1185,72 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</note>
</sect2>
+ <sect2 id="monitoring-pg-stat-backend-view">
+ <title><structname>pg_stat_backend</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_backend</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_backend</structname> view will have one row
+ per server process, showing statistics related to
+ the current activity of that process.
+ </para>
+
+ <table id="pg-stat-backend-view" xreflabel="pg_stat_backend">
+ <title><structname>pg_stat_backend</structname> View</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pid</structfield> <type>integer</type>
+ </para>
+ <para>
+ Process ID of this backend
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>seq_scan</structfield> <type>bigint</type>
+ </para>
+ <para>
+ The number of sequential scans initiated on tables.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Time at which these statistics were last reset
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <note>
+ <para>
+ The view does not return statistics for the checkpointer,
+ the background writer, the startup process and the autovacuum launcher.
+ </para>
+ </note>
+ </sect2>
+
<sect2 id="monitoring-pg-stat-replication-view">
<title><structname>pg_stat_replication</structname></title>
@@ -4921,6 +5000,22 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_stat_get_backend_statistics</primary>
+ </indexterm>
+ <function>pg_stat_get_backend_statistics</function> ( <type>integer</type> )
+ <returnvalue>setof record</returnvalue>
+ </para>
+ <para>
+ Returns a record of statistics bout the backend with the specified
+ process ID, or one record for each active backend in the system
+ if <literal>NULL</literal> is specified. The fields returned are a
+ subset of those in the <structname>pg_stat_backend</structname> view.
+ </para></entry>
+ </row>
+
<row>
<entry id="pg-stat-get-backend-wal" role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 1b3c5a55882..3ab7802cc5c 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -911,6 +911,13 @@ CREATE VIEW pg_stat_activity AS
LEFT JOIN pg_database AS D ON (S.datid = D.oid)
LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
+CREATE VIEW pg_stat_backend AS
+ SELECT
+ S.pid,
+ S.seq_scan,
+ S.stats_reset
+ FROM pg_stat_get_backend_statistics(NULL) AS S;
+
CREATE VIEW pg_stat_replication AS
SELECT
S.pid,
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index c756c2bebaa..2adfbdcb65c 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -685,6 +685,61 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+/*
+ * Returns statistics of PG backends.
+ */
+Datum
+pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
+{
+#define PG_STAT_GET_BACKEND_STATS_COLS 3
+ int num_backends = pgstat_fetch_stat_numbackends();
+ int curr_backend;
+ int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ /* 1-based index */
+ for (curr_backend = 1; curr_backend <= num_backends; curr_backend++)
+ {
+ /* for each row */
+ Datum values[PG_STAT_GET_BACKEND_STATS_COLS] = {0};
+ bool nulls[PG_STAT_GET_BACKEND_STATS_COLS] = {0};
+ LocalPgBackendStatus *local_beentry;
+ PgBackendStatus *beentry;
+ PgStat_Backend *backend_stats;
+
+ /* Get the next one in the list */
+ local_beentry = pgstat_get_local_beentry_by_index(curr_backend);
+ beentry = &local_beentry->backendStatus;
+
+ /* If looking for specific PID, ignore all the others */
+ if (pid != -1 && beentry->st_procpid != pid)
+ continue;
+
+ backend_stats = pgstat_fetch_stat_backend_by_pid(beentry->st_procpid, NULL);
+
+ values[0] = Int32GetDatum(beentry->st_procpid);
+
+ if (!backend_stats)
+ continue;
+
+ values[1] = Int64GetDatum(backend_stats->heap_scan);
+
+ if (backend_stats->stat_reset_timestamp != 0)
+ values[2] = TimestampTzGetDatum(backend_stats->stat_reset_timestamp);
+ else
+ nulls[2] = true;
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+
+ /* If only a single backend was requested, and we found it, break. */
+ if (pid != -1)
+ break;
+ }
+
+ return (Datum) 0;
+}
Datum
pg_backend_pid(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 118d6da1ace..e87200374d8 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5641,6 +5641,15 @@
proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
proargnames => '{pid,datid,pid,usesysid,application_name,state,query,wait_event_type,wait_event,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_type,ssl,sslversion,sslcipher,sslbits,ssl_client_dn,ssl_client_serial,ssl_issuer_dn,gss_auth,gss_princ,gss_enc,gss_delegation,leader_pid,query_id}',
prosrc => 'pg_stat_get_activity' },
+{ oid => '9555',
+ descr => 'statistics: statistics about currently active backends',
+ proname => 'pg_stat_get_backend_statistics', prorows => '100', proisstrict => 'f',
+ proretset => 't', provolatile => 's', proparallel => 'r',
+ prorettype => 'record', proargtypes => 'int4',
+ proallargtypes => '{int4,int4,int8,timestamptz}',
+ proargmodes => '{i,o,o,o}',
+ proargnames => '{pid,pid,seq_scan,stats_reset}',
+ prosrc => 'pg_stat_get_backend_statistics' },
{ oid => '6318', descr => 'describe wait events',
proname => 'pg_get_wait_events', procost => '10', prorows => '250',
proretset => 't', provolatile => 'v', prorettype => 'record',
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 35e8aad7701..63f8f12b8a5 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1847,6 +1847,10 @@ pg_stat_archiver| SELECT archived_count,
last_failed_time,
stats_reset
FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset);
+pg_stat_backend| SELECT pid,
+ seq_scan,
+ stats_reset
+ FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, stats_reset);
pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_alloc() AS buffers_alloc,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 605f5070376..937bf4bfc5b 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -118,6 +118,8 @@ SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
pg_catalog.pg_statio_user_tables AS b
WHERE t.relname='tenk2' AND b.relname='tenk2';
COMMIT;
+SELECT seq_scan AS seq_scan_before
+ FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
-- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
CREATE TABLE trunc_stats_test(id serial);
CREATE TABLE trunc_stats_test1(id serial, stuff text);
@@ -219,6 +221,14 @@ SELECT st.seq_scan >= pr.seq_scan + 1,
t | t | t | t
(1 row)
+SELECT seq_scan AS seq_scan_after
+ FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
+SELECT :seq_scan_after > :seq_scan_before;
+ ?column?
+----------
+ t
+(1 row)
+
SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 54e72866344..b743352eadb 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -38,6 +38,9 @@ SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
WHERE t.relname='tenk2' AND b.relname='tenk2';
COMMIT;
+SELECT seq_scan AS seq_scan_before
+ FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
+
-- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
CREATE TABLE trunc_stats_test(id serial);
CREATE TABLE trunc_stats_test1(id serial, stuff text);
@@ -122,6 +125,11 @@ SELECT st.seq_scan >= pr.seq_scan + 1,
FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
WHERE st.relname='tenk2' AND cl.relname='tenk2';
+SELECT seq_scan AS seq_scan_after
+ FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
+
+SELECT :seq_scan_after > :seq_scan_before;
+
SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
--
2.34.1
>From c7db46f953fdd6d5bf4a6198409910656528edbc Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Thu, 7 Aug 2025 14:41:09 +0000
Subject: [PATCH v1 03/10] Adding seq_tup_read to pg_stat_backend
Adding per backend number of live rows fetched by sequential scans.
XXX: Bump catversion.
---
doc/src/sgml/monitoring.sgml | 9 +++++++++
src/backend/access/heap/heapam.c | 3 +++
src/backend/access/heap/heapam_handler.c | 1 +
src/backend/catalog/system_views.sql | 1 +
src/backend/utils/activity/pgstat_backend.c | 4 ++++
src/backend/utils/adt/pgstatfuncs.c | 14 +++++++++-----
src/include/catalog/pg_proc.dat | 6 +++---
src/include/pgstat.h | 3 +++
src/test/regress/expected/rules.out | 3 ++-
src/test/regress/expected/stats.out | 13 +++++++------
src/test/regress/sql/stats.sql | 7 ++++---
11 files changed, 46 insertions(+), 18 deletions(-)
11.1% doc/src/sgml/
6.9% src/backend/access/heap/
4.7% src/backend/utils/activity/
24.2% src/backend/utils/adt/
11.7% src/include/catalog/
4.8% src/include/
20.7% src/test/regress/expected/
14.3% src/test/regress/sql/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 6868af55db4..3fdc3d639e6 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1231,6 +1231,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>seq_tup_read</structfield> <type>bigint</type>
+ </para>
+ <para>
+ The number of live rows fetched by sequential scans.
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index d9d6fb6c6ea..8561693258f 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -1379,6 +1379,7 @@ heap_getnext(TableScanDesc sscan, ScanDirection direction)
*/
pgstat_count_heap_getnext(scan->rs_base.rs_rd);
+ pgstat_count_backend_rel_seq_tup_read();
return &scan->rs_ctup;
}
@@ -1407,6 +1408,7 @@ heap_getnextslot(TableScanDesc sscan, ScanDirection direction, TupleTableSlot *s
*/
pgstat_count_heap_getnext(scan->rs_base.rs_rd);
+ pgstat_count_backend_rel_seq_tup_read();
ExecStoreBufferHeapTuple(&scan->rs_ctup, slot,
scan->rs_cbuf);
@@ -1555,6 +1557,7 @@ heap_getnextslot_tidrange(TableScanDesc sscan, ScanDirection direction,
* the proper return buffer and return the tuple.
*/
pgstat_count_heap_getnext(scan->rs_base.rs_rd);
+ pgstat_count_backend_rel_seq_tup_read();
ExecStoreBufferHeapTuple(&scan->rs_ctup, slot, scan->rs_cbuf);
return true;
diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index cb4bc35c93e..050ed0a79a3 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -2335,6 +2335,7 @@ heapam_scan_sample_next_tuple(TableScanDesc scan, SampleScanState *scanstate,
/* Count successfully-fetched tuples as heap fetches */
pgstat_count_heap_getnext(scan->rs_rd);
+ pgstat_count_backend_rel_seq_tup_read();
return true;
}
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 3ab7802cc5c..ea2f4785602 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -915,6 +915,7 @@ CREATE VIEW pg_stat_backend AS
SELECT
S.pid,
S.seq_scan,
+ S.seq_tup_read,
S.stats_reset
FROM pg_stat_get_backend_statistics(NULL) AS S;
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index 8797dac68c7..85da2d23fc7 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -287,6 +287,7 @@ pgstat_flush_backend_entry_rel(PgStat_EntryRef *entry_ref)
(shbackendent->stats.stat += PendingBackendStats.pending_backendrel.stat)
BACKENDREL_ACC(heap_scan);
+ BACKENDREL_ACC(seq_tup_read);
#undef BACKENDREL_ACC
/*
@@ -459,3 +460,6 @@ CppConcat(pgstat_count_backend_rel_,stat)(void) \
/* pgstat_count_backend_rel_heap_scan */
PGSTAT_COUNT_BACKEND_FUNC(heap_scan)
+
+/* pgstat_count_backend_rel_seq_tup_read */
+PGSTAT_COUNT_BACKEND_FUNC(seq_tup_read)
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 2adfbdcb65c..c8f2b2a73d4 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -691,7 +691,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
Datum
pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
{
-#define PG_STAT_GET_BACKEND_STATS_COLS 3
+#define PG_STAT_GET_BACKEND_STATS_COLS 4
int num_backends = pgstat_fetch_stat_numbackends();
int curr_backend;
int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -708,6 +708,7 @@ pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
LocalPgBackendStatus *local_beentry;
PgBackendStatus *beentry;
PgStat_Backend *backend_stats;
+ int i = 0;
/* Get the next one in the list */
local_beentry = pgstat_get_local_beentry_by_index(curr_backend);
@@ -719,17 +720,20 @@ pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
backend_stats = pgstat_fetch_stat_backend_by_pid(beentry->st_procpid, NULL);
- values[0] = Int32GetDatum(beentry->st_procpid);
+ values[i++] = Int32GetDatum(beentry->st_procpid);
if (!backend_stats)
continue;
- values[1] = Int64GetDatum(backend_stats->heap_scan);
+ values[i++] = Int64GetDatum(backend_stats->heap_scan);
+ values[i++] = Int64GetDatum(backend_stats->seq_tup_read);
if (backend_stats->stat_reset_timestamp != 0)
- values[2] = TimestampTzGetDatum(backend_stats->stat_reset_timestamp);
+ values[i] = TimestampTzGetDatum(backend_stats->stat_reset_timestamp);
else
- nulls[2] = true;
+ nulls[i] = true;
+
+ Assert(i + 1 == PG_STAT_GET_BACKEND_STATS_COLS);
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index e87200374d8..797a3f22db5 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5646,9 +5646,9 @@
proname => 'pg_stat_get_backend_statistics', prorows => '100', proisstrict => 'f',
proretset => 't', provolatile => 's', proparallel => 'r',
prorettype => 'record', proargtypes => 'int4',
- proallargtypes => '{int4,int4,int8,timestamptz}',
- proargmodes => '{i,o,o,o}',
- proargnames => '{pid,pid,seq_scan,stats_reset}',
+ proallargtypes => '{int4,int4,int8,int8,timestamptz}',
+ proargmodes => '{i,o,o,o,o}',
+ proargnames => '{pid,pid,seq_scan,seq_tup_read,stats_reset}',
prosrc => 'pg_stat_get_backend_statistics' },
{ oid => '6318', descr => 'describe wait events',
proname => 'pg_get_wait_events', procost => '10', prorows => '250',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 7b4dacd27f8..d634fe8e746 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -491,11 +491,13 @@ typedef struct PgStat_Backend
PgStat_BktypeIO io_stats;
PgStat_WalCounters wal_counters;
PgStat_Counter heap_scan;
+ PgStat_Counter seq_tup_read;
} PgStat_Backend;
typedef struct PgStat_BackendRelPending
{
PgStat_Counter heap_scan;
+ PgStat_Counter seq_tup_read;
} PgStat_BackendRelPending;
/* ---------
@@ -576,6 +578,7 @@ extern void pgstat_create_backend(ProcNumber procnum);
/* used to track backend relations related stats */
extern void pgstat_count_backend_rel_heap_scan(void);
+extern void pgstat_count_backend_rel_seq_tup_read(void);
/*
* Functions in pgstat_bgwriter.c
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 63f8f12b8a5..faf73d956e8 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1849,8 +1849,9 @@ pg_stat_archiver| SELECT archived_count,
FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset);
pg_stat_backend| SELECT pid,
seq_scan,
+ seq_tup_read,
stats_reset
- FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, stats_reset);
+ FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, stats_reset);
pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_alloc() AS buffers_alloc,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 937bf4bfc5b..c96d8d2ecae 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -118,7 +118,7 @@ SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
pg_catalog.pg_statio_user_tables AS b
WHERE t.relname='tenk2' AND b.relname='tenk2';
COMMIT;
-SELECT seq_scan AS seq_scan_before
+SELECT seq_scan AS seq_scan_before, seq_tup_read AS seq_tup_read_before
FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
-- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
CREATE TABLE trunc_stats_test(id serial);
@@ -221,12 +221,13 @@ SELECT st.seq_scan >= pr.seq_scan + 1,
t | t | t | t
(1 row)
-SELECT seq_scan AS seq_scan_after
+SELECT seq_scan AS seq_scan_after, seq_tup_read AS seq_tup_read_after
FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
-SELECT :seq_scan_after > :seq_scan_before;
- ?column?
-----------
- t
+SELECT :seq_scan_after > :seq_scan_before,
+ :seq_tup_read_after > :seq_tup_read_before;
+ ?column? | ?column?
+----------+----------
+ t | t
(1 row)
SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index b743352eadb..781b75d426a 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -38,7 +38,7 @@ SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
WHERE t.relname='tenk2' AND b.relname='tenk2';
COMMIT;
-SELECT seq_scan AS seq_scan_before
+SELECT seq_scan AS seq_scan_before, seq_tup_read AS seq_tup_read_before
FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
-- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
@@ -125,10 +125,11 @@ SELECT st.seq_scan >= pr.seq_scan + 1,
FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
WHERE st.relname='tenk2' AND cl.relname='tenk2';
-SELECT seq_scan AS seq_scan_after
+SELECT seq_scan AS seq_scan_after, seq_tup_read AS seq_tup_read_after
FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
-SELECT :seq_scan_after > :seq_scan_before;
+SELECT :seq_scan_after > :seq_scan_before,
+ :seq_tup_read_after > :seq_tup_read_before;
SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
--
2.34.1
>From 7c5b9441b53af95df63f8e82e3f52ad1784a2b09 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Fri, 8 Aug 2025 02:54:32 +0000
Subject: [PATCH v1 04/10] Adding idx_tup_fetch to pg_stat_backend
Adding per backend number of live rows fetched by index scans.
XXX: Bump catversion.
---
doc/src/sgml/monitoring.sgml | 9 +++++++++
src/backend/access/heap/heapam_handler.c | 1 +
src/backend/access/index/indexam.c | 3 +++
src/backend/catalog/system_views.sql | 1 +
src/backend/utils/activity/pgstat_backend.c | 4 ++++
src/backend/utils/adt/pgstatfuncs.c | 3 ++-
src/include/catalog/pg_proc.dat | 6 +++---
src/include/pgstat.h | 3 +++
src/test/regress/expected/rules.out | 3 ++-
src/test/regress/expected/stats.out | 15 +++++++++------
src/test/regress/sql/stats.sql | 9 ++++++---
11 files changed, 43 insertions(+), 14 deletions(-)
14.1% doc/src/sgml/
4.8% src/backend/access/
6.2% src/backend/utils/activity/
7.5% src/backend/utils/adt/
17.2% src/include/catalog/
6.3% src/include/
27.7% src/test/regress/expected/
14.4% src/test/regress/sql/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 3fdc3d639e6..2bfd016a2ff 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1240,6 +1240,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>idx_tup_fetch</structfield> <type>bigint</type>
+ </para>
+ <para>
+ The number of live rows fetched by index scans.
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 050ed0a79a3..ff86b3e67d4 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -2156,6 +2156,7 @@ heapam_scan_bitmap_next_tuple(TableScanDesc scan,
ItemPointerSet(&hscan->rs_ctup.t_self, hscan->rs_cblock, targoffset);
pgstat_count_heap_fetch(scan->rs_rd);
+ pgstat_count_backend_rel_idx_tup_fetch();
/*
* Set up the result slot to point to this tuple. Note that the slot
diff --git a/src/backend/access/index/indexam.c b/src/backend/access/index/indexam.c
index 219df1971da..d7d06266e70 100644
--- a/src/backend/access/index/indexam.c
+++ b/src/backend/access/index/indexam.c
@@ -686,7 +686,10 @@ index_fetch_heap(IndexScanDesc scan, TupleTableSlot *slot)
&scan->xs_heap_continue, &all_dead);
if (found)
+ {
pgstat_count_heap_fetch(scan->indexRelation);
+ pgstat_count_backend_rel_idx_tup_fetch();
+ }
/*
* If we scanned a whole HOT chain and found only dead tuples, tell index
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index ea2f4785602..dd85b78a6dc 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -916,6 +916,7 @@ CREATE VIEW pg_stat_backend AS
S.pid,
S.seq_scan,
S.seq_tup_read,
+ S.idx_tup_fetch,
S.stats_reset
FROM pg_stat_get_backend_statistics(NULL) AS S;
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index 85da2d23fc7..282f62dd0d3 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -288,6 +288,7 @@ pgstat_flush_backend_entry_rel(PgStat_EntryRef *entry_ref)
BACKENDREL_ACC(heap_scan);
BACKENDREL_ACC(seq_tup_read);
+ BACKENDREL_ACC(idx_tup_fetch);
#undef BACKENDREL_ACC
/*
@@ -463,3 +464,6 @@ PGSTAT_COUNT_BACKEND_FUNC(heap_scan)
/* pgstat_count_backend_rel_seq_tup_read */
PGSTAT_COUNT_BACKEND_FUNC(seq_tup_read)
+
+/* pgstat_count_backend_rel_idx_tup_fetch */
+PGSTAT_COUNT_BACKEND_FUNC(idx_tup_fetch)
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index c8f2b2a73d4..d050bcf8ed8 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -691,7 +691,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
Datum
pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
{
-#define PG_STAT_GET_BACKEND_STATS_COLS 4
+#define PG_STAT_GET_BACKEND_STATS_COLS 5
int num_backends = pgstat_fetch_stat_numbackends();
int curr_backend;
int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -727,6 +727,7 @@ pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
values[i++] = Int64GetDatum(backend_stats->heap_scan);
values[i++] = Int64GetDatum(backend_stats->seq_tup_read);
+ values[i++] = Int64GetDatum(backend_stats->idx_tup_fetch);
if (backend_stats->stat_reset_timestamp != 0)
values[i] = TimestampTzGetDatum(backend_stats->stat_reset_timestamp);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 797a3f22db5..a9709214d88 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5646,9 +5646,9 @@
proname => 'pg_stat_get_backend_statistics', prorows => '100', proisstrict => 'f',
proretset => 't', provolatile => 's', proparallel => 'r',
prorettype => 'record', proargtypes => 'int4',
- proallargtypes => '{int4,int4,int8,int8,timestamptz}',
- proargmodes => '{i,o,o,o,o}',
- proargnames => '{pid,pid,seq_scan,seq_tup_read,stats_reset}',
+ proallargtypes => '{int4,int4,int8,int8,int8,timestamptz}',
+ proargmodes => '{i,o,o,o,o,o}',
+ proargnames => '{pid,pid,seq_scan,seq_tup_read,idx_tup_fetch,stats_reset}',
prosrc => 'pg_stat_get_backend_statistics' },
{ oid => '6318', descr => 'describe wait events',
proname => 'pg_get_wait_events', procost => '10', prorows => '250',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index d634fe8e746..64739d04e5a 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -492,12 +492,14 @@ typedef struct PgStat_Backend
PgStat_WalCounters wal_counters;
PgStat_Counter heap_scan;
PgStat_Counter seq_tup_read;
+ PgStat_Counter idx_tup_fetch;
} PgStat_Backend;
typedef struct PgStat_BackendRelPending
{
PgStat_Counter heap_scan;
PgStat_Counter seq_tup_read;
+ PgStat_Counter idx_tup_fetch;
} PgStat_BackendRelPending;
/* ---------
@@ -579,6 +581,7 @@ extern void pgstat_create_backend(ProcNumber procnum);
/* used to track backend relations related stats */
extern void pgstat_count_backend_rel_heap_scan(void);
extern void pgstat_count_backend_rel_seq_tup_read(void);
+extern void pgstat_count_backend_rel_idx_tup_fetch(void);
/*
* Functions in pgstat_bgwriter.c
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index faf73d956e8..35a0786d7b1 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1850,8 +1850,9 @@ pg_stat_archiver| SELECT archived_count,
pg_stat_backend| SELECT pid,
seq_scan,
seq_tup_read,
+ idx_tup_fetch,
stats_reset
- FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, stats_reset);
+ FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, idx_tup_fetch, stats_reset);
pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_alloc() AS buffers_alloc,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index c96d8d2ecae..9804cdc9c84 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -118,7 +118,8 @@ SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
pg_catalog.pg_statio_user_tables AS b
WHERE t.relname='tenk2' AND b.relname='tenk2';
COMMIT;
-SELECT seq_scan AS seq_scan_before, seq_tup_read AS seq_tup_read_before
+SELECT seq_scan AS seq_scan_before, seq_tup_read AS seq_tup_read_before,
+ idx_tup_fetch AS idx_tup_fetch_before
FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
-- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
CREATE TABLE trunc_stats_test(id serial);
@@ -221,13 +222,15 @@ SELECT st.seq_scan >= pr.seq_scan + 1,
t | t | t | t
(1 row)
-SELECT seq_scan AS seq_scan_after, seq_tup_read AS seq_tup_read_after
+SELECT seq_scan AS seq_scan_after, seq_tup_read AS seq_tup_read_after,
+ idx_tup_fetch AS idx_tup_fetch_after
FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
SELECT :seq_scan_after > :seq_scan_before,
- :seq_tup_read_after > :seq_tup_read_before;
- ?column? | ?column?
-----------+----------
- t | t
+ :seq_tup_read_after > :seq_tup_read_before,
+ :idx_tup_fetch_after > :idx_tup_fetch_before;
+ ?column? | ?column? | ?column?
+----------+----------+----------
+ t | t | t
(1 row)
SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 781b75d426a..279cb6f9dfb 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -38,7 +38,8 @@ SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
WHERE t.relname='tenk2' AND b.relname='tenk2';
COMMIT;
-SELECT seq_scan AS seq_scan_before, seq_tup_read AS seq_tup_read_before
+SELECT seq_scan AS seq_scan_before, seq_tup_read AS seq_tup_read_before,
+ idx_tup_fetch AS idx_tup_fetch_before
FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
-- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
@@ -125,11 +126,13 @@ SELECT st.seq_scan >= pr.seq_scan + 1,
FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
WHERE st.relname='tenk2' AND cl.relname='tenk2';
-SELECT seq_scan AS seq_scan_after, seq_tup_read AS seq_tup_read_after
+SELECT seq_scan AS seq_scan_after, seq_tup_read AS seq_tup_read_after,
+ idx_tup_fetch AS idx_tup_fetch_after
FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
SELECT :seq_scan_after > :seq_scan_before,
- :seq_tup_read_after > :seq_tup_read_before;
+ :seq_tup_read_after > :seq_tup_read_before,
+ :idx_tup_fetch_after > :idx_tup_fetch_before;
SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
--
2.34.1
>From 10181916f61cac7be926e9ca37623513e35b3d84 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Fri, 8 Aug 2025 03:17:59 +0000
Subject: [PATCH v1 05/10] Adding idx_scan to pg_stat_backend
Adding per backend number of index scans initiated.
XXX: Bump catversion.
---
doc/src/sgml/monitoring.sgml | 9 +++++++++
src/backend/access/brin/brin.c | 1 +
src/backend/access/gin/ginscan.c | 1 +
src/backend/access/gist/gistget.c | 2 ++
src/backend/access/hash/hashsearch.c | 1 +
src/backend/access/nbtree/nbtsearch.c | 1 +
src/backend/access/spgist/spgscan.c | 1 +
src/backend/catalog/system_views.sql | 1 +
src/backend/utils/activity/pgstat_backend.c | 4 ++++
src/backend/utils/adt/pgstatfuncs.c | 3 ++-
src/include/catalog/pg_proc.dat | 6 +++---
src/include/pgstat.h | 3 +++
src/test/regress/expected/rules.out | 3 ++-
src/test/regress/expected/stats.out | 15 +++++++++------
src/test/regress/sql/stats.sql | 9 ++++++---
15 files changed, 46 insertions(+), 14 deletions(-)
10.5% doc/src/sgml/
3.2% src/backend/access/gist/
7.9% src/backend/access/
4.3% src/backend/utils/activity/
5.7% src/backend/utils/adt/
15.2% src/include/catalog/
4.3% src/include/
30.8% src/test/regress/expected/
16.6% src/test/regress/sql/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 2bfd016a2ff..6aa467b1e7d 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1249,6 +1249,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>idx_scan</structfield> <type>bigint</type>
+ </para>
+ <para>
+ The number of index scans initiated.
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c
index 7ff7467e462..c9d0f2eff9a 100644
--- a/src/backend/access/brin/brin.c
+++ b/src/backend/access/brin/brin.c
@@ -592,6 +592,7 @@ bringetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
opaque = (BrinOpaque *) scan->opaque;
bdesc = opaque->bo_bdesc;
pgstat_count_index_scan(idxRel);
+ pgstat_count_backend_rel_idx_scan();
if (scan->instrument)
scan->instrument->nsearches++;
diff --git a/src/backend/access/gin/ginscan.c b/src/backend/access/gin/ginscan.c
index c2d1771bd77..26efd4ceced 100644
--- a/src/backend/access/gin/ginscan.c
+++ b/src/backend/access/gin/ginscan.c
@@ -442,6 +442,7 @@ ginNewScanKey(IndexScanDesc scan)
MemoryContextSwitchTo(oldCtx);
pgstat_count_index_scan(scan->indexRelation);
+ pgstat_count_backend_rel_idx_scan();
if (scan->instrument)
scan->instrument->nsearches++;
}
diff --git a/src/backend/access/gist/gistget.c b/src/backend/access/gist/gistget.c
index 387d9972345..19517a4cdb2 100644
--- a/src/backend/access/gist/gistget.c
+++ b/src/backend/access/gist/gistget.c
@@ -625,6 +625,7 @@ gistgettuple(IndexScanDesc scan, ScanDirection dir)
GISTSearchItem fakeItem;
pgstat_count_index_scan(scan->indexRelation);
+ pgstat_count_backend_rel_idx_scan();
if (scan->instrument)
scan->instrument->nsearches++;
@@ -752,6 +753,7 @@ gistgetbitmap(IndexScanDesc scan, TIDBitmap *tbm)
return 0;
pgstat_count_index_scan(scan->indexRelation);
+ pgstat_count_backend_rel_idx_scan();
if (scan->instrument)
scan->instrument->nsearches++;
diff --git a/src/backend/access/hash/hashsearch.c b/src/backend/access/hash/hashsearch.c
index 92c15a65be2..2350cf5190a 100644
--- a/src/backend/access/hash/hashsearch.c
+++ b/src/backend/access/hash/hashsearch.c
@@ -298,6 +298,7 @@ _hash_first(IndexScanDesc scan, ScanDirection dir)
HashScanPosItem *currItem;
pgstat_count_index_scan(rel);
+ pgstat_count_backend_rel_idx_scan();
if (scan->instrument)
scan->instrument->nsearches++;
diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c
index d69798795b4..415328a2633 100644
--- a/src/backend/access/nbtree/nbtsearch.c
+++ b/src/backend/access/nbtree/nbtsearch.c
@@ -955,6 +955,7 @@ _bt_first(IndexScanDesc scan, ScanDirection dir)
* _bt_search/_bt_endpoint below
*/
pgstat_count_index_scan(rel);
+ pgstat_count_backend_rel_idx_scan();
if (scan->instrument)
scan->instrument->nsearches++;
diff --git a/src/backend/access/spgist/spgscan.c b/src/backend/access/spgist/spgscan.c
index 25893050c58..79f158e49d2 100644
--- a/src/backend/access/spgist/spgscan.c
+++ b/src/backend/access/spgist/spgscan.c
@@ -421,6 +421,7 @@ spgrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys,
/* count an indexscan for stats */
pgstat_count_index_scan(scan->indexRelation);
+ pgstat_count_backend_rel_idx_scan();
if (scan->instrument)
scan->instrument->nsearches++;
}
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index dd85b78a6dc..7193ab693fb 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -917,6 +917,7 @@ CREATE VIEW pg_stat_backend AS
S.seq_scan,
S.seq_tup_read,
S.idx_tup_fetch,
+ S.idx_scan,
S.stats_reset
FROM pg_stat_get_backend_statistics(NULL) AS S;
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index 282f62dd0d3..dc501306901 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -289,6 +289,7 @@ pgstat_flush_backend_entry_rel(PgStat_EntryRef *entry_ref)
BACKENDREL_ACC(heap_scan);
BACKENDREL_ACC(seq_tup_read);
BACKENDREL_ACC(idx_tup_fetch);
+ BACKENDREL_ACC(idx_scan);
#undef BACKENDREL_ACC
/*
@@ -467,3 +468,6 @@ PGSTAT_COUNT_BACKEND_FUNC(seq_tup_read)
/* pgstat_count_backend_rel_idx_tup_fetch */
PGSTAT_COUNT_BACKEND_FUNC(idx_tup_fetch)
+
+/* pgstat_count_backend_rel_idx_scan */
+PGSTAT_COUNT_BACKEND_FUNC(idx_scan)
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index d050bcf8ed8..60298c5f8d9 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -691,7 +691,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
Datum
pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
{
-#define PG_STAT_GET_BACKEND_STATS_COLS 5
+#define PG_STAT_GET_BACKEND_STATS_COLS 6
int num_backends = pgstat_fetch_stat_numbackends();
int curr_backend;
int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -728,6 +728,7 @@ pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
values[i++] = Int64GetDatum(backend_stats->heap_scan);
values[i++] = Int64GetDatum(backend_stats->seq_tup_read);
values[i++] = Int64GetDatum(backend_stats->idx_tup_fetch);
+ values[i++] = Int64GetDatum(backend_stats->idx_scan);
if (backend_stats->stat_reset_timestamp != 0)
values[i] = TimestampTzGetDatum(backend_stats->stat_reset_timestamp);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a9709214d88..9212faa0506 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5646,9 +5646,9 @@
proname => 'pg_stat_get_backend_statistics', prorows => '100', proisstrict => 'f',
proretset => 't', provolatile => 's', proparallel => 'r',
prorettype => 'record', proargtypes => 'int4',
- proallargtypes => '{int4,int4,int8,int8,int8,timestamptz}',
- proargmodes => '{i,o,o,o,o,o}',
- proargnames => '{pid,pid,seq_scan,seq_tup_read,idx_tup_fetch,stats_reset}',
+ proallargtypes => '{int4,int4,int8,int8,int8,int8,timestamptz}',
+ proargmodes => '{i,o,o,o,o,o,o}',
+ proargnames => '{pid,pid,seq_scan,seq_tup_read,idx_tup_fetch,idx_scan,stats_reset}',
prosrc => 'pg_stat_get_backend_statistics' },
{ oid => '6318', descr => 'describe wait events',
proname => 'pg_get_wait_events', procost => '10', prorows => '250',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 64739d04e5a..a55811ef8c3 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -493,6 +493,7 @@ typedef struct PgStat_Backend
PgStat_Counter heap_scan;
PgStat_Counter seq_tup_read;
PgStat_Counter idx_tup_fetch;
+ PgStat_Counter idx_scan;
} PgStat_Backend;
typedef struct PgStat_BackendRelPending
@@ -500,6 +501,7 @@ typedef struct PgStat_BackendRelPending
PgStat_Counter heap_scan;
PgStat_Counter seq_tup_read;
PgStat_Counter idx_tup_fetch;
+ PgStat_Counter idx_scan;
} PgStat_BackendRelPending;
/* ---------
@@ -582,6 +584,7 @@ extern void pgstat_create_backend(ProcNumber procnum);
extern void pgstat_count_backend_rel_heap_scan(void);
extern void pgstat_count_backend_rel_seq_tup_read(void);
extern void pgstat_count_backend_rel_idx_tup_fetch(void);
+extern void pgstat_count_backend_rel_idx_scan(void);
/*
* Functions in pgstat_bgwriter.c
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 35a0786d7b1..007631d2421 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1851,8 +1851,9 @@ pg_stat_backend| SELECT pid,
seq_scan,
seq_tup_read,
idx_tup_fetch,
+ idx_scan,
stats_reset
- FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, idx_tup_fetch, stats_reset);
+ FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, idx_tup_fetch, idx_scan, stats_reset);
pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_alloc() AS buffers_alloc,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 9804cdc9c84..6f15d1da140 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -119,7 +119,8 @@ SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
WHERE t.relname='tenk2' AND b.relname='tenk2';
COMMIT;
SELECT seq_scan AS seq_scan_before, seq_tup_read AS seq_tup_read_before,
- idx_tup_fetch AS idx_tup_fetch_before
+ idx_tup_fetch AS idx_tup_fetch_before,
+ idx_scan AS idx_scan_before
FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
-- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
CREATE TABLE trunc_stats_test(id serial);
@@ -223,14 +224,16 @@ SELECT st.seq_scan >= pr.seq_scan + 1,
(1 row)
SELECT seq_scan AS seq_scan_after, seq_tup_read AS seq_tup_read_after,
- idx_tup_fetch AS idx_tup_fetch_after
+ idx_tup_fetch AS idx_tup_fetch_after,
+ idx_scan AS idx_scan_after
FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
SELECT :seq_scan_after > :seq_scan_before,
:seq_tup_read_after > :seq_tup_read_before,
- :idx_tup_fetch_after > :idx_tup_fetch_before;
- ?column? | ?column? | ?column?
-----------+----------+----------
- t | t | t
+ :idx_tup_fetch_after > :idx_tup_fetch_before,
+ :idx_scan_after > :idx_scan_before;
+ ?column? | ?column? | ?column? | ?column?
+----------+----------+----------+----------
+ t | t | t | t
(1 row)
SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 279cb6f9dfb..01810b256e0 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -39,7 +39,8 @@ SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
COMMIT;
SELECT seq_scan AS seq_scan_before, seq_tup_read AS seq_tup_read_before,
- idx_tup_fetch AS idx_tup_fetch_before
+ idx_tup_fetch AS idx_tup_fetch_before,
+ idx_scan AS idx_scan_before
FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
-- test effects of TRUNCATE on n_live_tup/n_dead_tup counters
@@ -127,12 +128,14 @@ SELECT st.seq_scan >= pr.seq_scan + 1,
WHERE st.relname='tenk2' AND cl.relname='tenk2';
SELECT seq_scan AS seq_scan_after, seq_tup_read AS seq_tup_read_after,
- idx_tup_fetch AS idx_tup_fetch_after
+ idx_tup_fetch AS idx_tup_fetch_after,
+ idx_scan AS idx_scan_after
FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
SELECT :seq_scan_after > :seq_scan_before,
:seq_tup_read_after > :seq_tup_read_before,
- :idx_tup_fetch_after > :idx_tup_fetch_before;
+ :idx_tup_fetch_after > :idx_tup_fetch_before,
+ :idx_scan_after > :idx_scan_before;
SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
--
2.34.1
>From e2fab62a747ecc37a93f644be2c00e40891fc6bb Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Fri, 8 Aug 2025 04:40:18 +0000
Subject: [PATCH v1 06/10] Adding idx_tup_read to pg_stat_backend
Adding per backend number of index entries returned by index scans.
XXX: Bump catversion.
---
doc/src/sgml/monitoring.sgml | 9 +++++++++
src/backend/access/index/indexam.c | 2 ++
src/backend/catalog/system_views.sql | 1 +
src/backend/utils/activity/pgstat_backend.c | 12 ++++++++++++
src/backend/utils/adt/pgstatfuncs.c | 3 ++-
src/include/catalog/pg_proc.dat | 6 +++---
src/include/pgstat.h | 3 +++
src/test/regress/expected/rules.out | 3 ++-
8 files changed, 34 insertions(+), 5 deletions(-)
19.9% doc/src/sgml/
6.6% src/backend/access/index/
20.3% src/backend/utils/activity/
10.4% src/backend/utils/adt/
19.9% src/include/catalog/
9.4% src/include/
11.1% src/test/regress/expected/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 6aa467b1e7d..a6313a32299 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1258,6 +1258,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>idx_tup_read</structfield> <type>bigint</type>
+ </para>
+ <para>
+ The number of index entries returned by index scans.
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
diff --git a/src/backend/access/index/indexam.c b/src/backend/access/index/indexam.c
index d7d06266e70..7252841cedb 100644
--- a/src/backend/access/index/indexam.c
+++ b/src/backend/access/index/indexam.c
@@ -652,6 +652,7 @@ index_getnext_tid(IndexScanDesc scan, ScanDirection direction)
Assert(ItemPointerIsValid(&scan->xs_heaptid));
pgstat_count_index_tuples(scan->indexRelation, 1);
+ pgstat_count_backend_rel_idx_tup_read(1);
/* Return the TID of the tuple we found. */
return &scan->xs_heaptid;
@@ -781,6 +782,7 @@ index_getbitmap(IndexScanDesc scan, TIDBitmap *bitmap)
ntids = scan->indexRelation->rd_indam->amgetbitmap(scan, bitmap);
pgstat_count_index_tuples(scan->indexRelation, ntids);
+ pgstat_count_backend_rel_idx_tup_read(ntids);
return ntids;
}
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 7193ab693fb..d87fd38b8b2 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -918,6 +918,7 @@ CREATE VIEW pg_stat_backend AS
S.seq_tup_read,
S.idx_tup_fetch,
S.idx_scan,
+ S.idx_tup_read,
S.stats_reset
FROM pg_stat_get_backend_statistics(NULL) AS S;
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index dc501306901..df072cb082f 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -290,6 +290,7 @@ pgstat_flush_backend_entry_rel(PgStat_EntryRef *entry_ref)
BACKENDREL_ACC(seq_tup_read);
BACKENDREL_ACC(idx_tup_fetch);
BACKENDREL_ACC(idx_scan);
+ BACKENDREL_ACC(idx_tup_read);
#undef BACKENDREL_ACC
/*
@@ -471,3 +472,14 @@ PGSTAT_COUNT_BACKEND_FUNC(idx_tup_fetch)
/* pgstat_count_backend_rel_idx_scan */
PGSTAT_COUNT_BACKEND_FUNC(idx_scan)
+
+void
+pgstat_count_backend_rel_idx_tup_read(PgStat_Counter n)
+{
+ if (!pgstat_tracks_backend_bktype(MyBackendType))
+ return;
+
+ PendingBackendStats.pending_backendrel.idx_tup_read += n;
+ backend_has_relstats = true;
+ pgstat_report_fixed = true;
+}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 60298c5f8d9..99486851281 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -691,7 +691,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
Datum
pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
{
-#define PG_STAT_GET_BACKEND_STATS_COLS 6
+#define PG_STAT_GET_BACKEND_STATS_COLS 7
int num_backends = pgstat_fetch_stat_numbackends();
int curr_backend;
int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -729,6 +729,7 @@ pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
values[i++] = Int64GetDatum(backend_stats->seq_tup_read);
values[i++] = Int64GetDatum(backend_stats->idx_tup_fetch);
values[i++] = Int64GetDatum(backend_stats->idx_scan);
+ values[i++] = Int64GetDatum(backend_stats->idx_tup_read);
if (backend_stats->stat_reset_timestamp != 0)
values[i] = TimestampTzGetDatum(backend_stats->stat_reset_timestamp);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9212faa0506..bb5608f492e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5646,9 +5646,9 @@
proname => 'pg_stat_get_backend_statistics', prorows => '100', proisstrict => 'f',
proretset => 't', provolatile => 's', proparallel => 'r',
prorettype => 'record', proargtypes => 'int4',
- proallargtypes => '{int4,int4,int8,int8,int8,int8,timestamptz}',
- proargmodes => '{i,o,o,o,o,o,o}',
- proargnames => '{pid,pid,seq_scan,seq_tup_read,idx_tup_fetch,idx_scan,stats_reset}',
+ proallargtypes => '{int4,int4,int8,int8,int8,int8,int8,timestamptz}',
+ proargmodes => '{i,o,o,o,o,o,o,o}',
+ proargnames => '{pid,pid,seq_scan,seq_tup_read,idx_tup_fetch,idx_scan,idx_tup_read,stats_reset}',
prosrc => 'pg_stat_get_backend_statistics' },
{ oid => '6318', descr => 'describe wait events',
proname => 'pg_get_wait_events', procost => '10', prorows => '250',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index a55811ef8c3..f26b9e12567 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -494,6 +494,7 @@ typedef struct PgStat_Backend
PgStat_Counter seq_tup_read;
PgStat_Counter idx_tup_fetch;
PgStat_Counter idx_scan;
+ PgStat_Counter idx_tup_read;
} PgStat_Backend;
typedef struct PgStat_BackendRelPending
@@ -502,6 +503,7 @@ typedef struct PgStat_BackendRelPending
PgStat_Counter seq_tup_read;
PgStat_Counter idx_tup_fetch;
PgStat_Counter idx_scan;
+ PgStat_Counter idx_tup_read;
} PgStat_BackendRelPending;
/* ---------
@@ -585,6 +587,7 @@ extern void pgstat_count_backend_rel_heap_scan(void);
extern void pgstat_count_backend_rel_seq_tup_read(void);
extern void pgstat_count_backend_rel_idx_tup_fetch(void);
extern void pgstat_count_backend_rel_idx_scan(void);
+extern void pgstat_count_backend_rel_idx_tup_read(PgStat_Counter n);
/*
* Functions in pgstat_bgwriter.c
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 007631d2421..2da8a8122f1 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1852,8 +1852,9 @@ pg_stat_backend| SELECT pid,
seq_tup_read,
idx_tup_fetch,
idx_scan,
+ idx_tup_read,
stats_reset
- FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, idx_tup_fetch, idx_scan, stats_reset);
+ FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, idx_tup_fetch, idx_scan, idx_tup_read, stats_reset);
pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_alloc() AS buffers_alloc,
--
2.34.1
>From 12355fe2b0d1ac5f1d16ac3e1e5860bb4f641a2d Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Fri, 8 Aug 2025 07:12:51 +0000
Subject: [PATCH v1 07/10] Adding vacuum_count to pg_stat_backend
Adding per backend number of manual vacuums triggered.
XXX: Bump catversion.
---
doc/src/sgml/monitoring.sgml | 9 +++++++++
src/backend/catalog/system_views.sql | 1 +
src/backend/utils/activity/pgstat_backend.c | 4 ++++
src/backend/utils/activity/pgstat_relation.c | 1 +
src/backend/utils/adt/pgstatfuncs.c | 3 ++-
src/include/catalog/pg_proc.dat | 6 +++---
src/include/pgstat.h | 3 +++
src/test/regress/expected/rules.out | 3 ++-
src/test/regress/expected/vacuum.out | 15 +++++++++++++++
src/test/regress/sql/vacuum.sql | 11 +++++++++++
10 files changed, 51 insertions(+), 5 deletions(-)
14.5% doc/src/sgml/
8.0% src/backend/utils/activity/
7.2% src/backend/utils/adt/
15.8% src/include/catalog/
5.9% src/include/
30.4% src/test/regress/expected/
16.5% src/test/regress/sql/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index a6313a32299..1b3154d5326 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1267,6 +1267,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vacuum_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ The number of manual vacuums triggered (not counting VACUUM FULL).
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index d87fd38b8b2..6257bce86a1 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -919,6 +919,7 @@ CREATE VIEW pg_stat_backend AS
S.idx_tup_fetch,
S.idx_scan,
S.idx_tup_read,
+ S.vacuum_count,
S.stats_reset
FROM pg_stat_get_backend_statistics(NULL) AS S;
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index df072cb082f..58386662359 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -291,6 +291,7 @@ pgstat_flush_backend_entry_rel(PgStat_EntryRef *entry_ref)
BACKENDREL_ACC(idx_tup_fetch);
BACKENDREL_ACC(idx_scan);
BACKENDREL_ACC(idx_tup_read);
+ BACKENDREL_ACC(vacuum_count);
#undef BACKENDREL_ACC
/*
@@ -473,6 +474,9 @@ PGSTAT_COUNT_BACKEND_FUNC(idx_tup_fetch)
/* pgstat_count_backend_rel_idx_scan */
PGSTAT_COUNT_BACKEND_FUNC(idx_scan)
+/* pgstat_count_backend_rel_vacuum_count */
+PGSTAT_COUNT_BACKEND_FUNC(vacuum_count)
+
void
pgstat_count_backend_rel_idx_tup_read(PgStat_Counter n)
{
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 69df741cbf6..555f0815454 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -258,6 +258,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
tabentry->last_vacuum_time = ts;
tabentry->vacuum_count++;
tabentry->total_vacuum_time += elapsedtime;
+ pgstat_count_backend_rel_vacuum_count();
}
pgstat_unlock_entry(entry_ref);
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 99486851281..bff5bfc0538 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -691,7 +691,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
Datum
pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
{
-#define PG_STAT_GET_BACKEND_STATS_COLS 7
+#define PG_STAT_GET_BACKEND_STATS_COLS 8
int num_backends = pgstat_fetch_stat_numbackends();
int curr_backend;
int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -730,6 +730,7 @@ pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
values[i++] = Int64GetDatum(backend_stats->idx_tup_fetch);
values[i++] = Int64GetDatum(backend_stats->idx_scan);
values[i++] = Int64GetDatum(backend_stats->idx_tup_read);
+ values[i++] = Int64GetDatum(backend_stats->vacuum_count);
if (backend_stats->stat_reset_timestamp != 0)
values[i] = TimestampTzGetDatum(backend_stats->stat_reset_timestamp);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index bb5608f492e..aae43548923 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5646,9 +5646,9 @@
proname => 'pg_stat_get_backend_statistics', prorows => '100', proisstrict => 'f',
proretset => 't', provolatile => 's', proparallel => 'r',
prorettype => 'record', proargtypes => 'int4',
- proallargtypes => '{int4,int4,int8,int8,int8,int8,int8,timestamptz}',
- proargmodes => '{i,o,o,o,o,o,o,o}',
- proargnames => '{pid,pid,seq_scan,seq_tup_read,idx_tup_fetch,idx_scan,idx_tup_read,stats_reset}',
+ proallargtypes => '{int4,int4,int8,int8,int8,int8,int8,int8,timestamptz}',
+ proargmodes => '{i,o,o,o,o,o,o,o,o}',
+ proargnames => '{pid,pid,seq_scan,seq_tup_read,idx_tup_fetch,idx_scan,idx_tup_read,vacuum_count,stats_reset}',
prosrc => 'pg_stat_get_backend_statistics' },
{ oid => '6318', descr => 'describe wait events',
proname => 'pg_get_wait_events', procost => '10', prorows => '250',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index f26b9e12567..558bfd3c123 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -495,6 +495,7 @@ typedef struct PgStat_Backend
PgStat_Counter idx_tup_fetch;
PgStat_Counter idx_scan;
PgStat_Counter idx_tup_read;
+ PgStat_Counter vacuum_count;
} PgStat_Backend;
typedef struct PgStat_BackendRelPending
@@ -504,6 +505,7 @@ typedef struct PgStat_BackendRelPending
PgStat_Counter idx_tup_fetch;
PgStat_Counter idx_scan;
PgStat_Counter idx_tup_read;
+ PgStat_Counter vacuum_count;
} PgStat_BackendRelPending;
/* ---------
@@ -588,6 +590,7 @@ extern void pgstat_count_backend_rel_seq_tup_read(void);
extern void pgstat_count_backend_rel_idx_tup_fetch(void);
extern void pgstat_count_backend_rel_idx_scan(void);
extern void pgstat_count_backend_rel_idx_tup_read(PgStat_Counter n);
+extern void pgstat_count_backend_rel_vacuum_count(void);
/*
* Functions in pgstat_bgwriter.c
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2da8a8122f1..7df7fe37ed4 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1853,8 +1853,9 @@ pg_stat_backend| SELECT pid,
idx_tup_fetch,
idx_scan,
idx_tup_read,
+ vacuum_count,
stats_reset
- FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, idx_tup_fetch, idx_scan, idx_tup_read, stats_reset);
+ FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, idx_tup_fetch, idx_scan, idx_tup_read, vacuum_count, stats_reset);
pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_alloc() AS buffers_alloc,
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 0abcc99989e..b7ab929084f 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -469,6 +469,7 @@ CREATE VIEW vac_option_tab_counts AS
LEFT JOIN pg_class c ON s.relid = c.reltoastrelid
WHERE c.relname = 'vac_option_tab' OR s.relname = 'vac_option_tab'
ORDER BY rel;
+SELECT vacuum_count AS vacuum_count_before FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
VACUUM (PROCESS_TOAST TRUE) vac_option_tab;
SELECT * FROM vac_option_tab_counts;
rel | vacuum_count
@@ -497,6 +498,20 @@ SELECT * FROM vac_option_tab_counts;
toast | 2
(2 rows)
+-- ensure pending stats are flushed
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT vacuum_count AS vacuum_count_after FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
+SELECT :vacuum_count_after > :vacuum_count_before;
+ ?column?
+----------
+ t
+(1 row)
+
-- Nothing is processed.
VACUUM (PROCESS_MAIN FALSE, PROCESS_TOAST FALSE) vac_option_tab;
SELECT * FROM vac_option_tab_counts;
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index a72bdb5b619..b1bcd442c13 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -364,6 +364,9 @@ CREATE VIEW vac_option_tab_counts AS
LEFT JOIN pg_class c ON s.relid = c.reltoastrelid
WHERE c.relname = 'vac_option_tab' OR s.relname = 'vac_option_tab'
ORDER BY rel;
+
+SELECT vacuum_count AS vacuum_count_before FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
+
VACUUM (PROCESS_TOAST TRUE) vac_option_tab;
SELECT * FROM vac_option_tab_counts;
VACUUM (PROCESS_TOAST FALSE) vac_option_tab;
@@ -374,6 +377,14 @@ VACUUM (PROCESS_TOAST FALSE, FULL) vac_option_tab; -- error
-- Only the toast table is processed.
VACUUM (PROCESS_MAIN FALSE) vac_option_tab;
SELECT * FROM vac_option_tab_counts;
+
+-- ensure pending stats are flushed
+SELECT pg_stat_force_next_flush();
+
+SELECT vacuum_count AS vacuum_count_after FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
+
+SELECT :vacuum_count_after > :vacuum_count_before;
+
-- Nothing is processed.
VACUUM (PROCESS_MAIN FALSE, PROCESS_TOAST FALSE) vac_option_tab;
SELECT * FROM vac_option_tab_counts;
--
2.34.1
>From cdfd76728e8297f0b460cc37a2c8bb6c2adf5b72 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Fri, 8 Aug 2025 07:42:53 +0000
Subject: [PATCH v1 08/10] Adding last_vacuum to pg_stat_backend
Adding the last time that a backend triggered a manual vacuum.
XXX: Bump catversion.
---
doc/src/sgml/monitoring.sgml | 9 ++++++++
src/backend/catalog/system_views.sql | 1 +
src/backend/utils/activity/pgstat_backend.c | 23 +++++++++++++++++++-
src/backend/utils/activity/pgstat_relation.c | 1 +
src/backend/utils/adt/pgstatfuncs.c | 7 +++++-
src/include/catalog/pg_proc.dat | 6 ++---
src/include/pgstat.h | 3 +++
src/test/regress/expected/rules.out | 3 ++-
8 files changed, 47 insertions(+), 6 deletions(-)
18.6% doc/src/sgml/
42.6% src/backend/utils/activity/
12.5% src/backend/utils/adt/
13.3% src/include/catalog/
6.7% src/include/
4.5% src/test/regress/expected/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 1b3154d5326..d27369a4d21 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1276,6 +1276,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_vacuum</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Last time at which this backend triggered a manual vacuum (not counting VACUUM FULL).
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 6257bce86a1..5450d60a084 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -920,6 +920,7 @@ CREATE VIEW pg_stat_backend AS
S.idx_scan,
S.idx_tup_read,
S.vacuum_count,
+ S.last_vacuum,
S.stats_reset
FROM pg_stat_get_backend_statistics(NULL) AS S;
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index 58386662359..c248da52bc2 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -292,7 +292,14 @@ pgstat_flush_backend_entry_rel(PgStat_EntryRef *entry_ref)
BACKENDREL_ACC(idx_scan);
BACKENDREL_ACC(idx_tup_read);
BACKENDREL_ACC(vacuum_count);
-#undef BACKENDREL_ACC
+#undef BACKENDREL_AC
+
+#define BACKENDREL_SET(stat) \
+ if (PendingBackendStats.pending_backendrel.stat > 0) \
+ (shbackendent->stats.stat = PendingBackendStats.pending_backendrel.stat)
+
+ BACKENDREL_SET(last_vacuum);
+#undef BACKENDREL_SET
/*
* Clear out the statistics buffer, so it can be re-used.
@@ -487,3 +494,17 @@ pgstat_count_backend_rel_idx_tup_read(PgStat_Counter n)
backend_has_relstats = true;
pgstat_report_fixed = true;
}
+
+#define PGSTAT_SETTS_BACKEND_FUNC(stat) \
+void \
+CppConcat(pgstat_set_backend_rel_,stat)(TimestampTz ts) \
+{ \
+ if (!pgstat_tracks_backend_bktype(MyBackendType)) \
+ return; \
+ PendingBackendStats.pending_backendrel.stat= ts; \
+ backend_has_relstats = true; \
+ pgstat_report_fixed = true; \
+}
+
+/* pgstat_set_backend_rel_last_vacuum */
+PGSTAT_SETTS_BACKEND_FUNC(last_vacuum)
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 555f0815454..d22ee2224a7 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -259,6 +259,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
tabentry->vacuum_count++;
tabentry->total_vacuum_time += elapsedtime;
pgstat_count_backend_rel_vacuum_count();
+ pgstat_set_backend_rel_last_vacuum(ts);
}
pgstat_unlock_entry(entry_ref);
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index bff5bfc0538..8abf43185e5 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -691,7 +691,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
Datum
pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
{
-#define PG_STAT_GET_BACKEND_STATS_COLS 8
+#define PG_STAT_GET_BACKEND_STATS_COLS 9
int num_backends = pgstat_fetch_stat_numbackends();
int curr_backend;
int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -732,6 +732,11 @@ pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
values[i++] = Int64GetDatum(backend_stats->idx_tup_read);
values[i++] = Int64GetDatum(backend_stats->vacuum_count);
+ if (backend_stats->last_vacuum != 0)
+ values[i++] = TimestampTzGetDatum(backend_stats->last_vacuum);
+ else
+ nulls[i++] = true;
+
if (backend_stats->stat_reset_timestamp != 0)
values[i] = TimestampTzGetDatum(backend_stats->stat_reset_timestamp);
else
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index aae43548923..4c73c91bf84 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5646,9 +5646,9 @@
proname => 'pg_stat_get_backend_statistics', prorows => '100', proisstrict => 'f',
proretset => 't', provolatile => 's', proparallel => 'r',
prorettype => 'record', proargtypes => 'int4',
- proallargtypes => '{int4,int4,int8,int8,int8,int8,int8,int8,timestamptz}',
- proargmodes => '{i,o,o,o,o,o,o,o,o}',
- proargnames => '{pid,pid,seq_scan,seq_tup_read,idx_tup_fetch,idx_scan,idx_tup_read,vacuum_count,stats_reset}',
+ proallargtypes => '{int4,int4,int8,int8,int8,int8,int8,int8,timestamptz,timestamptz}',
+ proargmodes => '{i,o,o,o,o,o,o,o,o,o}',
+ proargnames => '{pid,pid,seq_scan,seq_tup_read,idx_tup_fetch,idx_scan,idx_tup_read,vacuum_count,last_vacuum,stats_reset}',
prosrc => 'pg_stat_get_backend_statistics' },
{ oid => '6318', descr => 'describe wait events',
proname => 'pg_get_wait_events', procost => '10', prorows => '250',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 558bfd3c123..5c1c49aaed0 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -496,6 +496,7 @@ typedef struct PgStat_Backend
PgStat_Counter idx_scan;
PgStat_Counter idx_tup_read;
PgStat_Counter vacuum_count;
+ TimestampTz last_vacuum;
} PgStat_Backend;
typedef struct PgStat_BackendRelPending
@@ -506,6 +507,7 @@ typedef struct PgStat_BackendRelPending
PgStat_Counter idx_scan;
PgStat_Counter idx_tup_read;
PgStat_Counter vacuum_count;
+ TimestampTz last_vacuum;
} PgStat_BackendRelPending;
/* ---------
@@ -591,6 +593,7 @@ extern void pgstat_count_backend_rel_idx_tup_fetch(void);
extern void pgstat_count_backend_rel_idx_scan(void);
extern void pgstat_count_backend_rel_idx_tup_read(PgStat_Counter n);
extern void pgstat_count_backend_rel_vacuum_count(void);
+extern void pgstat_set_backend_rel_last_vacuum(TimestampTz ts);
/*
* Functions in pgstat_bgwriter.c
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 7df7fe37ed4..89f03c7097c 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1854,8 +1854,9 @@ pg_stat_backend| SELECT pid,
idx_scan,
idx_tup_read,
vacuum_count,
+ last_vacuum,
stats_reset
- FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, idx_tup_fetch, idx_scan, idx_tup_read, vacuum_count, stats_reset);
+ FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, idx_tup_fetch, idx_scan, idx_tup_read, vacuum_count, last_vacuum, stats_reset);
pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_alloc() AS buffers_alloc,
--
2.34.1
>From 6aa74e9f59d343523bf7c0ecea0301a0d85f7f1e Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Fri, 8 Aug 2025 07:21:35 +0000
Subject: [PATCH v1 09/10] Adding analyze_count to pg_stat_backend
Adding per backend number of manual analyzes triggered.
XXX: Bump catversion.
---
doc/src/sgml/monitoring.sgml | 9 +++++++++
src/backend/catalog/system_views.sql | 1 +
src/backend/utils/activity/pgstat_backend.c | 4 ++++
src/backend/utils/activity/pgstat_relation.c | 1 +
src/backend/utils/adt/pgstatfuncs.c | 4 +++-
src/include/catalog/pg_proc.dat | 6 +++---
src/include/pgstat.h | 3 +++
src/test/regress/expected/rules.out | 3 ++-
8 files changed, 26 insertions(+), 5 deletions(-)
23.6% doc/src/sgml/
14.8% src/backend/utils/activity/
13.1% src/backend/utils/adt/
25.0% src/include/catalog/
10.8% src/include/
9.7% src/test/regress/expected/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index d27369a4d21..dd4e9bb486e 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1285,6 +1285,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>analyze_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ The number of manual analyzes triggered.
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 5450d60a084..62af8b4f069 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -921,6 +921,7 @@ CREATE VIEW pg_stat_backend AS
S.idx_tup_read,
S.vacuum_count,
S.last_vacuum,
+ S.analyze_count,
S.stats_reset
FROM pg_stat_get_backend_statistics(NULL) AS S;
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index c248da52bc2..cdd36160a4c 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -292,6 +292,7 @@ pgstat_flush_backend_entry_rel(PgStat_EntryRef *entry_ref)
BACKENDREL_ACC(idx_scan);
BACKENDREL_ACC(idx_tup_read);
BACKENDREL_ACC(vacuum_count);
+ BACKENDREL_ACC(analyze_count);
#undef BACKENDREL_AC
#define BACKENDREL_SET(stat) \
@@ -484,6 +485,9 @@ PGSTAT_COUNT_BACKEND_FUNC(idx_scan)
/* pgstat_count_backend_rel_vacuum_count */
PGSTAT_COUNT_BACKEND_FUNC(vacuum_count)
+/* pgstat_count_backend_rel_analayze_count */
+PGSTAT_COUNT_BACKEND_FUNC(analyze_count)
+
void
pgstat_count_backend_rel_idx_tup_read(PgStat_Counter n)
{
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index d22ee2224a7..24313c3a5ec 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -360,6 +360,7 @@ pgstat_report_analyze(Relation rel,
tabentry->last_analyze_time = ts;
tabentry->analyze_count++;
tabentry->total_analyze_time += elapsedtime;
+ pgstat_count_backend_rel_analyze_count();
}
pgstat_unlock_entry(entry_ref);
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 8abf43185e5..c47af5c1b11 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -691,7 +691,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
Datum
pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
{
-#define PG_STAT_GET_BACKEND_STATS_COLS 9
+#define PG_STAT_GET_BACKEND_STATS_COLS 10
int num_backends = pgstat_fetch_stat_numbackends();
int curr_backend;
int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -737,6 +737,8 @@ pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
else
nulls[i++] = true;
+ values[i++] = Int64GetDatum(backend_stats->analyze_count);
+
if (backend_stats->stat_reset_timestamp != 0)
values[i] = TimestampTzGetDatum(backend_stats->stat_reset_timestamp);
else
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4c73c91bf84..eb6fb292bd5 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5646,9 +5646,9 @@
proname => 'pg_stat_get_backend_statistics', prorows => '100', proisstrict => 'f',
proretset => 't', provolatile => 's', proparallel => 'r',
prorettype => 'record', proargtypes => 'int4',
- proallargtypes => '{int4,int4,int8,int8,int8,int8,int8,int8,timestamptz,timestamptz}',
- proargmodes => '{i,o,o,o,o,o,o,o,o,o}',
- proargnames => '{pid,pid,seq_scan,seq_tup_read,idx_tup_fetch,idx_scan,idx_tup_read,vacuum_count,last_vacuum,stats_reset}',
+ proallargtypes => '{int4,int4,int8,int8,int8,int8,int8,int8,timestamptz,int8,timestamptz}',
+ proargmodes => '{i,o,o,o,o,o,o,o,o,o,o}',
+ proargnames => '{pid,pid,seq_scan,seq_tup_read,idx_tup_fetch,idx_scan,idx_tup_read,vacuum_count,last_vacuum,analyze_count,stats_reset}',
prosrc => 'pg_stat_get_backend_statistics' },
{ oid => '6318', descr => 'describe wait events',
proname => 'pg_get_wait_events', procost => '10', prorows => '250',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 5c1c49aaed0..16a17fdbcad 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -497,6 +497,7 @@ typedef struct PgStat_Backend
PgStat_Counter idx_tup_read;
PgStat_Counter vacuum_count;
TimestampTz last_vacuum;
+ PgStat_Counter analyze_count;
} PgStat_Backend;
typedef struct PgStat_BackendRelPending
@@ -508,6 +509,7 @@ typedef struct PgStat_BackendRelPending
PgStat_Counter idx_tup_read;
PgStat_Counter vacuum_count;
TimestampTz last_vacuum;
+ PgStat_Counter analyze_count;
} PgStat_BackendRelPending;
/* ---------
@@ -594,6 +596,7 @@ extern void pgstat_count_backend_rel_idx_scan(void);
extern void pgstat_count_backend_rel_idx_tup_read(PgStat_Counter n);
extern void pgstat_count_backend_rel_vacuum_count(void);
extern void pgstat_set_backend_rel_last_vacuum(TimestampTz ts);
+extern void pgstat_count_backend_rel_analyze_count(void);
/*
* Functions in pgstat_bgwriter.c
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 89f03c7097c..32efc5d33e1 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1855,8 +1855,9 @@ pg_stat_backend| SELECT pid,
idx_tup_read,
vacuum_count,
last_vacuum,
+ analyze_count,
stats_reset
- FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, idx_tup_fetch, idx_scan, idx_tup_read, vacuum_count, last_vacuum, stats_reset);
+ FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, idx_tup_fetch, idx_scan, idx_tup_read, vacuum_count, last_vacuum, analyze_count, stats_reset);
pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_alloc() AS buffers_alloc,
--
2.34.1
>From a9bc7c62131c13ae99b7f62811f67485129f04ad Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Mon, 11 Aug 2025 11:23:15 +0000
Subject: [PATCH v1 10/10] Adding last_analyze to pg_stat_backend
Adding the last time that a backend triggered a manual analyze.
XXX: Bump catversion.
---
doc/src/sgml/monitoring.sgml | 9 +++++++++
src/backend/catalog/system_views.sql | 1 +
src/backend/utils/activity/pgstat_backend.c | 4 ++++
src/backend/utils/activity/pgstat_relation.c | 1 +
src/backend/utils/adt/pgstatfuncs.c | 7 ++++++-
src/include/catalog/pg_proc.dat | 6 +++---
src/include/pgstat.h | 3 +++
src/test/regress/expected/rules.out | 3 ++-
8 files changed, 29 insertions(+), 5 deletions(-)
23.1% doc/src/sgml/
12.2% src/backend/utils/activity/
17.2% src/backend/utils/adt/
25.3% src/include/catalog/
9.2% src/include/
10.6% src/test/regress/expected/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index dd4e9bb486e..ed83c36188e 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1294,6 +1294,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>last_analyze</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Last time at which this backend triggered a manual analyze.
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 62af8b4f069..ffb9bdbe92c 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -922,6 +922,7 @@ CREATE VIEW pg_stat_backend AS
S.vacuum_count,
S.last_vacuum,
S.analyze_count,
+ S.last_analyze,
S.stats_reset
FROM pg_stat_get_backend_statistics(NULL) AS S;
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index cdd36160a4c..50d08e58e4a 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -300,6 +300,7 @@ pgstat_flush_backend_entry_rel(PgStat_EntryRef *entry_ref)
(shbackendent->stats.stat = PendingBackendStats.pending_backendrel.stat)
BACKENDREL_SET(last_vacuum);
+ BACKENDREL_SET(last_analyze);
#undef BACKENDREL_SET
/*
@@ -512,3 +513,6 @@ CppConcat(pgstat_set_backend_rel_,stat)(TimestampTz ts) \
/* pgstat_set_backend_rel_last_vacuum */
PGSTAT_SETTS_BACKEND_FUNC(last_vacuum)
+
+/* pgstat_set_backend_rel_last_analyze */
+PGSTAT_SETTS_BACKEND_FUNC(last_analyze)
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index 24313c3a5ec..d9fafd6980f 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -361,6 +361,7 @@ pgstat_report_analyze(Relation rel,
tabentry->analyze_count++;
tabentry->total_analyze_time += elapsedtime;
pgstat_count_backend_rel_analyze_count();
+ pgstat_set_backend_rel_last_analyze(ts);
}
pgstat_unlock_entry(entry_ref);
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index c47af5c1b11..ae56bcf878f 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -691,7 +691,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
Datum
pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
{
-#define PG_STAT_GET_BACKEND_STATS_COLS 10
+#define PG_STAT_GET_BACKEND_STATS_COLS 11
int num_backends = pgstat_fetch_stat_numbackends();
int curr_backend;
int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -739,6 +739,11 @@ pg_stat_get_backend_statistics(PG_FUNCTION_ARGS)
values[i++] = Int64GetDatum(backend_stats->analyze_count);
+ if (backend_stats->last_analyze != 0)
+ values[i++] = TimestampTzGetDatum(backend_stats->last_analyze);
+ else
+ nulls[i++] = true;
+
if (backend_stats->stat_reset_timestamp != 0)
values[i] = TimestampTzGetDatum(backend_stats->stat_reset_timestamp);
else
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index eb6fb292bd5..330c9d93440 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5646,9 +5646,9 @@
proname => 'pg_stat_get_backend_statistics', prorows => '100', proisstrict => 'f',
proretset => 't', provolatile => 's', proparallel => 'r',
prorettype => 'record', proargtypes => 'int4',
- proallargtypes => '{int4,int4,int8,int8,int8,int8,int8,int8,timestamptz,int8,timestamptz}',
- proargmodes => '{i,o,o,o,o,o,o,o,o,o,o}',
- proargnames => '{pid,pid,seq_scan,seq_tup_read,idx_tup_fetch,idx_scan,idx_tup_read,vacuum_count,last_vacuum,analyze_count,stats_reset}',
+ proallargtypes => '{int4,int4,int8,int8,int8,int8,int8,int8,timestamptz,int8,timestamptz,timestamptz}',
+ proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o}',
+ proargnames => '{pid,pid,seq_scan,seq_tup_read,idx_tup_fetch,idx_scan,idx_tup_read,vacuum_count,last_vacuum,analyze_count,last_analyze,stats_reset}',
prosrc => 'pg_stat_get_backend_statistics' },
{ oid => '6318', descr => 'describe wait events',
proname => 'pg_get_wait_events', procost => '10', prorows => '250',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 16a17fdbcad..c8cd04af909 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -498,6 +498,7 @@ typedef struct PgStat_Backend
PgStat_Counter vacuum_count;
TimestampTz last_vacuum;
PgStat_Counter analyze_count;
+ TimestampTz last_analyze;
} PgStat_Backend;
typedef struct PgStat_BackendRelPending
@@ -510,6 +511,7 @@ typedef struct PgStat_BackendRelPending
PgStat_Counter vacuum_count;
TimestampTz last_vacuum;
PgStat_Counter analyze_count;
+ TimestampTz last_analyze;
} PgStat_BackendRelPending;
/* ---------
@@ -597,6 +599,7 @@ extern void pgstat_count_backend_rel_idx_tup_read(PgStat_Counter n);
extern void pgstat_count_backend_rel_vacuum_count(void);
extern void pgstat_set_backend_rel_last_vacuum(TimestampTz ts);
extern void pgstat_count_backend_rel_analyze_count(void);
+extern void pgstat_set_backend_rel_last_analyze(TimestampTz ts);
/*
* Functions in pgstat_bgwriter.c
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 32efc5d33e1..b31f1bc06a0 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1856,8 +1856,9 @@ pg_stat_backend| SELECT pid,
vacuum_count,
last_vacuum,
analyze_count,
+ last_analyze,
stats_reset
- FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, idx_tup_fetch, idx_scan, idx_tup_read, vacuum_count, last_vacuum, analyze_count, stats_reset);
+ FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, seq_tup_read, idx_tup_fetch, idx_scan, idx_tup_read, vacuum_count, last_vacuum, analyze_count, last_analyze, stats_reset);
pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_alloc() AS buffers_alloc,
--
2.34.1
