From a5b543258aa29e181abe59b3183b685650d24651 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Mon, 6 Oct 2025 15:31:21 +0900 Subject: [PATCH] Add stats_reset to pg_stat_all_{tables,indexes} and related views It is possible to call pg_stat_reset_single_table_counters() on a relation (index or table) but the reset time was missing from the system views showing their statistics. This commit adds the reset time as an attribute of pg_stat_all_tables, pg_stat_all_indexes, and other relations related to them. Bump catalog version. Bump PGSTAT_FILE_FORMAT_ID, as a result of the new field added to PgStat_StatTabEntry. Author: Bertrand Drouvot Reviewed-by: Sami Imseih Reviewed-by: Michael Paquier Discussion: https://postgr.es/m/aN8l182jKxEq1h9f@paquier.xyz --- doc/src/sgml/monitoring.sgml | 36 ++++++++++++++++++++ src/backend/catalog/system_views.sql | 12 ++++--- src/backend/utils/activity/pgstat.c | 1 + src/backend/utils/activity/pgstat_relation.c | 6 ++++ src/backend/utils/adt/pgstatfuncs.c | 3 ++ src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.dat | 4 +++ src/include/pgstat.h | 4 ++- src/include/utils/pgstat_internal.h | 1 + src/test/regress/expected/rules.out | 36 +++++++++++++------- src/test/regress/expected/stats.out | 34 +++++++++++------- src/test/regress/sql/stats.sql | 11 ++++-- 12 files changed, 117 insertions(+), 33 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 3f4a27a736e..786aa2ac5f6 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -4115,6 +4115,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage cost-based delays.) + + + + stats_reset timestamp with time zone + + + Time at which these statistics were last reset + + @@ -4235,6 +4244,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage index + + + + stats_reset timestamp with time zone + + + Time at which these statistics were last reset + + @@ -4432,6 +4450,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage Number of buffer hits in this table's TOAST table indexes (if any) + + + + stats_reset timestamp with time zone + + + Time at which these statistics were last reset + + @@ -4532,6 +4559,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage Number of buffer hits in this index + + + + stats_reset timestamp with time zone + + + Time at which these statistics were last reset + + diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index c77fa0234bb..884b6a23817 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -716,7 +716,8 @@ CREATE VIEW pg_stat_all_tables AS pg_stat_get_total_vacuum_time(C.oid) AS total_vacuum_time, pg_stat_get_total_autovacuum_time(C.oid) AS total_autovacuum_time, pg_stat_get_total_analyze_time(C.oid) AS total_analyze_time, - pg_stat_get_total_autoanalyze_time(C.oid) AS total_autoanalyze_time + pg_stat_get_total_autoanalyze_time(C.oid) AS total_autoanalyze_time, + pg_stat_get_stat_reset_time(C.oid) AS stats_reset FROM pg_class C LEFT JOIN pg_index I ON C.oid = I.indrelid LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) @@ -778,7 +779,8 @@ CREATE VIEW pg_statio_all_tables AS pg_stat_get_blocks_hit(T.oid) AS toast_blks_read, pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit, X.idx_blks_read AS tidx_blks_read, - X.idx_blks_hit AS tidx_blks_hit + X.idx_blks_hit AS tidx_blks_hit, + pg_stat_get_stat_reset_time(C.oid) AS stats_reset FROM pg_class C LEFT JOIN pg_class T ON C.reltoastrelid = T.oid LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) @@ -818,7 +820,8 @@ CREATE VIEW pg_stat_all_indexes AS pg_stat_get_numscans(I.oid) AS idx_scan, pg_stat_get_lastscan(I.oid) AS last_idx_scan, pg_stat_get_tuples_returned(I.oid) AS idx_tup_read, - pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch + pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch, + pg_stat_get_stat_reset_time(I.oid) AS stats_reset FROM pg_class C JOIN pg_index X ON C.oid = X.indrelid JOIN pg_class I ON I.oid = X.indexrelid @@ -844,7 +847,8 @@ CREATE VIEW pg_statio_all_indexes AS I.relname AS indexrelname, pg_stat_get_blocks_fetched(I.oid) - pg_stat_get_blocks_hit(I.oid) AS idx_blks_read, - pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit + pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit, + pg_stat_get_stat_reset_time(I.oid) AS stats_reset FROM pg_class C JOIN pg_index X ON C.oid = X.indrelid JOIN pg_class I ON I.oid = X.indexrelid diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c index 44621653d8d..48f57e408e1 100644 --- a/src/backend/utils/activity/pgstat.c +++ b/src/backend/utils/activity/pgstat.c @@ -313,6 +313,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE] .flush_pending_cb = pgstat_relation_flush_cb, .delete_pending_cb = pgstat_relation_delete_pending_cb, + .reset_timestamp_cb = pgstat_relation_reset_timestamp_cb, }, [PGSTAT_KIND_FUNCTION] = { diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index b5e3903a290..1de477cbeeb 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -910,6 +910,12 @@ pgstat_relation_delete_pending_cb(PgStat_EntryRef *entry_ref) pgstat_unlink_relation(pending->relation); } +void +pgstat_relation_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts) +{ + ((PgStatShared_Relation *) header)->stats.stat_reset_time = ts; +} + /* * Find or create a PgStat_TableStatus entry for rel. New entry is created and * initialized if not exists. diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index c756c2bebaa..7e89a8048d5 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -168,6 +168,9 @@ PG_STAT_GET_RELENTRY_TIMESTAMPTZ(last_vacuum_time) /* pg_stat_get_lastscan */ PG_STAT_GET_RELENTRY_TIMESTAMPTZ(lastscan) +/* pg_stat_get_stat_reset_time */ +PG_STAT_GET_RELENTRY_TIMESTAMPTZ(stat_reset_time) + Datum pg_stat_get_function_calls(PG_FUNCTION_ARGS) { diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 4e7d5ceb7b8..6d6daf1d5a7 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202509291 +#define CATALOG_VERSION_NO 202510061 #endif diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 01eba3b5a19..5d5a9483fec 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5537,6 +5537,10 @@ proname => 'pg_stat_get_lastscan', provolatile => 's', proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid', prosrc => 'pg_stat_get_lastscan' }, +{ oid => '9127', descr => 'statistics: last reset for a relation', + proname => 'pg_stat_get_stat_reset_time', provolatile => 's', + proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid', + prosrc => 'pg_stat_get_stat_reset_time' }, { oid => '1929', descr => 'statistics: number of tuples read by seqscan', proname => 'pg_stat_get_tuples_returned', provolatile => 's', proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index e4a59a30b8c..8e8adb01176 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -212,7 +212,7 @@ typedef struct PgStat_TableXactStatus * ------------------------------------------------------------ */ -#define PGSTAT_FILE_FORMAT_ID 0x01A5BCB7 +#define PGSTAT_FILE_FORMAT_ID 0x01A5BCB8 typedef struct PgStat_ArchiverStats { @@ -454,6 +454,8 @@ typedef struct PgStat_StatTabEntry PgStat_Counter total_autovacuum_time; PgStat_Counter total_analyze_time; PgStat_Counter total_autoanalyze_time; + + TimestampTz stat_reset_time; } PgStat_StatTabEntry; /* ------ diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index 88d09ea20ba..dc42d8043b5 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -716,6 +716,7 @@ extern void PostPrepare_PgStat_Relations(PgStat_SubXactStatus *xact_state); extern bool pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait); extern void pgstat_relation_delete_pending_cb(PgStat_EntryRef *entry_ref); +extern void pgstat_relation_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts); /* diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 35e8aad7701..7f1cb3bb4af 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1798,7 +1798,8 @@ pg_stat_all_indexes| SELECT c.oid AS relid, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_lastscan(i.oid) AS last_idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, - pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch + pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch, + pg_stat_get_stat_reset_time(i.oid) AS stats_reset FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) @@ -1833,7 +1834,8 @@ pg_stat_all_tables| SELECT c.oid AS relid, pg_stat_get_total_vacuum_time(c.oid) AS total_vacuum_time, pg_stat_get_total_autovacuum_time(c.oid) AS total_autovacuum_time, pg_stat_get_total_analyze_time(c.oid) AS total_analyze_time, - pg_stat_get_total_autoanalyze_time(c.oid) AS total_autoanalyze_time + pg_stat_get_total_autoanalyze_time(c.oid) AS total_autoanalyze_time, + pg_stat_get_stat_reset_time(c.oid) AS stats_reset FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) @@ -2200,7 +2202,8 @@ pg_stat_sys_indexes| SELECT relid, idx_scan, last_idx_scan, idx_tup_read, - idx_tup_fetch + idx_tup_fetch, + stats_reset FROM pg_stat_all_indexes WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); pg_stat_sys_tables| SELECT relid, @@ -2232,7 +2235,8 @@ pg_stat_sys_tables| SELECT relid, total_vacuum_time, total_autovacuum_time, total_analyze_time, - total_autoanalyze_time + total_autoanalyze_time, + stats_reset FROM pg_stat_all_tables WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); pg_stat_user_functions| SELECT p.oid AS funcid, @@ -2252,7 +2256,8 @@ pg_stat_user_indexes| SELECT relid, idx_scan, last_idx_scan, idx_tup_read, - idx_tup_fetch + idx_tup_fetch, + stats_reset FROM pg_stat_all_indexes WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); pg_stat_user_tables| SELECT relid, @@ -2284,7 +2289,8 @@ pg_stat_user_tables| SELECT relid, total_vacuum_time, total_autovacuum_time, total_analyze_time, - total_autoanalyze_time + total_autoanalyze_time, + stats_reset FROM pg_stat_all_tables WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); pg_stat_wal| SELECT wal_records, @@ -2370,7 +2376,8 @@ pg_statio_all_indexes| SELECT c.oid AS relid, c.relname, i.relname AS indexrelname, (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, - pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit + pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit, + pg_stat_get_stat_reset_time(i.oid) AS stats_reset FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) @@ -2394,7 +2401,8 @@ pg_statio_all_tables| SELECT c.oid AS relid, (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, x.idx_blks_read AS tidx_blks_read, - x.idx_blks_hit AS tidx_blks_hit + x.idx_blks_hit AS tidx_blks_hit, + pg_stat_get_stat_reset_time(c.oid) AS stats_reset FROM ((((pg_class c LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) @@ -2413,7 +2421,8 @@ pg_statio_sys_indexes| SELECT relid, relname, indexrelname, idx_blks_read, - idx_blks_hit + idx_blks_hit, + stats_reset FROM pg_statio_all_indexes WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); pg_statio_sys_sequences| SELECT relid, @@ -2433,7 +2442,8 @@ pg_statio_sys_tables| SELECT relid, toast_blks_read, toast_blks_hit, tidx_blks_read, - tidx_blks_hit + tidx_blks_hit, + stats_reset FROM pg_statio_all_tables WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); pg_statio_user_indexes| SELECT relid, @@ -2442,7 +2452,8 @@ pg_statio_user_indexes| SELECT relid, relname, indexrelname, idx_blks_read, - idx_blks_hit + idx_blks_hit, + stats_reset FROM pg_statio_all_indexes WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); pg_statio_user_sequences| SELECT relid, @@ -2462,7 +2473,8 @@ pg_statio_user_tables| SELECT relid, toast_blks_read, toast_blks_hit, tidx_blks_read, - tidx_blks_hit + tidx_blks_hit, + stats_reset FROM pg_statio_all_tables WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); pg_stats| SELECT n.nspname AS schemaname, diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index ea3c4287ca7..67e1860e984 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -666,16 +666,24 @@ SELECT last_seq_scan, last_idx_scan FROM pg_stat_all_tables WHERE relid = 'test_ (1 row) COMMIT; +SELECT stats_reset IS NOT NULL AS has_stats_reset + FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass; + has_stats_reset +----------------- + f +(1 row) + SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass); pg_stat_reset_single_table_counters ------------------------------------- (1 row) -SELECT seq_scan, idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass; - seq_scan | idx_scan -----------+---------- - 0 | 0 +SELECT seq_scan, idx_scan, stats_reset IS NOT NULL AS has_stats_reset + FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass; + seq_scan | idx_scan | has_stats_reset +----------+----------+----------------- + 0 | 0 | t (1 row) -- ensure we start out with exactly one index and sequential scan @@ -851,11 +859,12 @@ FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass; (1 row) -- check the stats in pg_stat_all_indexes -SELECT idx_scan, :'test_last_idx' < last_idx_scan AS idx_ok +SELECT idx_scan, :'test_last_idx' < last_idx_scan AS idx_ok, + stats_reset IS NOT NULL AS has_stats_reset FROM pg_stat_all_indexes WHERE indexrelid = 'test_last_scan_pkey'::regclass; - idx_scan | idx_ok -----------+-------- - 3 | t + idx_scan | idx_ok | has_stats_reset +----------+--------+----------------- + 3 | t | f (1 row) -- check that the stats in pg_stat_all_indexes are reset @@ -865,10 +874,11 @@ SELECT pg_stat_reset_single_table_counters('test_last_scan_pkey'::regclass); (1 row) -SELECT idx_scan FROM pg_stat_all_indexes WHERE indexrelid = 'test_last_scan_pkey'::regclass; - idx_scan ----------- - 0 +SELECT idx_scan, stats_reset IS NOT NULL AS has_stats_reset + FROM pg_stat_all_indexes WHERE indexrelid = 'test_last_scan_pkey'::regclass; + idx_scan | has_stats_reset +----------+----------------- + 0 | t (1 row) ----- diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 73bdd11d654..8768e0f27fd 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -312,8 +312,11 @@ SELECT pg_stat_force_next_flush(); SELECT last_seq_scan, last_idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass; COMMIT; +SELECT stats_reset IS NOT NULL AS has_stats_reset + FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass; SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass); -SELECT seq_scan, idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass; +SELECT seq_scan, idx_scan, stats_reset IS NOT NULL AS has_stats_reset + FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass; -- ensure we start out with exactly one index and sequential scan BEGIN; @@ -383,13 +386,15 @@ SELECT seq_scan, :'test_last_seq' = last_seq_scan AS seq_ok, idx_scan, :'test_la FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass; -- check the stats in pg_stat_all_indexes -SELECT idx_scan, :'test_last_idx' < last_idx_scan AS idx_ok +SELECT idx_scan, :'test_last_idx' < last_idx_scan AS idx_ok, + stats_reset IS NOT NULL AS has_stats_reset FROM pg_stat_all_indexes WHERE indexrelid = 'test_last_scan_pkey'::regclass; -- check that the stats in pg_stat_all_indexes are reset SELECT pg_stat_reset_single_table_counters('test_last_scan_pkey'::regclass); -SELECT idx_scan FROM pg_stat_all_indexes WHERE indexrelid = 'test_last_scan_pkey'::regclass; +SELECT idx_scan, stats_reset IS NOT NULL AS has_stats_reset + FROM pg_stat_all_indexes WHERE indexrelid = 'test_last_scan_pkey'::regclass; ----- -- Test reset of some stats for shared table -- 2.47.3