Hi, On Fri, Oct 03, 2025 at 10:24:39AM +0900, Michael Paquier wrote: > On Thu, Oct 02, 2025 at 05:27:06PM -0500, Sami Imseih wrote: > > +1. This field should clearly be there. >
Thank you both for looking at it! > One question would be if we need to worry about the additional bytes > of this field, but seeing the size of PgStat_StatTabEntry currently > I'm going to answer "no" to my own question in advance. Yeah, I was thinking the same and reached the same conclusion. > > Nothing jumped out at me in the code. Although, I think we should add > > at least one test where pg_stat_reset_single_table_counters() is called > > with an index OID. There isn't a difference in the way the stats are > > reset for indexes and tables, but they are presented in different views, > > so it makes sense to add test coverage. > > Makes sense to me. This matters in terms of coverage for HEAD, > being outside of the scope of this proposal. Added one test on pg_stat_all_indexes in v2 attached. That's the first test on "pg_stat_all_indexes" in .sql files. It just tests the new stats_reset field, I think it's sufficient for the purpose of this patch. > > On a side note: I really think pg_stat_reset_single_table_counters is > > the wrong name here, since other OIDs can be used here; indexes > > or materialized views, etc. Maybe pg_stat_reset_single_relation_counters > > will be better? > > It's mostly a historical artifact at this stage, Yeah, it comes from 083e1b0f27df and the associated discussion is [1]. From what I can see, at that time the struct that was holding the table and index stats was "PgStat_TableCounts". So the naming "pg_stat_reset_single_table_counters" somehow made more sense at that time. > and the function is > documented as being usable for an index or a table. Using "relation" > would be more consistent, indeed. I am not sure if it's worth > bothering, though. It's done and documented that way since 2010, so I'm also not sure it's worth bothering. > What's the point of having tests for two tables? Shouldn't the one > based on test_last_scan be enough? The one on pg_shdescription may > actually fail on repeated runs, may it not? It is a shared catalog. Yeah this one may need to be done differently. I just removed it as it does not provide extra value here. [1]: https://www.postgresql.org/message-id/flat/9837222c1001240837r5c103519lc6a74c37be5f1831%40mail.gmail.com Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
>From feb5d53a73ab632b5ee5483afc69efc1930ae603 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Thu, 2 Oct 2025 07:55:21 +0000 Subject: [PATCH v2] Add stats_reset to pg_stat_all_tables|indexes and related views It's possible to call pg_stat_reset_single_table_counters() on a relation but the reset time is not reported in the related views. Adding stats_reset to the related views for consistency with other stat kinds that also report reset times. This new field is not included into the pg_stat_xact_* views because the 0 values in transaction-local stats have nothing to do with reset operations. XXX: Bumps catversion XXX: Bumps PGSTAT_FILE_FORMAT_ID --- 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/pg_proc.dat | 4 +++ src/include/pgstat.h | 2 ++ src/include/utils/pgstat_internal.h | 1 + src/test/regress/expected/rules.out | 36 +++++++++++++------- src/test/regress/expected/stats.out | 33 +++++++++++++++--- src/test/regress/sql/stats.sql | 8 ++++- 11 files changed, 121 insertions(+), 21 deletions(-) 22.2% doc/src/sgml/ 11.8% src/backend/catalog/ 4.1% src/backend/utils/activity/ 4.8% src/include/catalog/ 39.8% src/test/regress/expected/ 13.0% src/test/regress/sql/ 4.0% src/ 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.) </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> @@ -4235,6 +4244,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage index </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> @@ -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) </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> @@ -4532,6 +4559,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage Number of buffer hits in this index </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> 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..dcf2b00e648 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -1004,3 +1004,9 @@ restore_truncdrop_counters(PgStat_TableXactStatus *trans) trans->tuples_deleted = trans->deleted_pre_truncdrop; } } + +void +pgstat_relation_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts) +{ + ((PgStatShared_Relation *) header)->stats.stat_reset_time = ts; +} 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/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..5829af0fbf4 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -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 605f5070376..b15aab666df 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -666,16 +666,22 @@ SELECT last_seq_scan, last_idx_scan FROM pg_stat_all_tables WHERE relid = 'test_ (1 row) COMMIT; +SELECT stats_reset IS NULL AS has_no_stats_reset FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass; + has_no_stats_reset +-------------------- + t +(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 @@ -1262,6 +1268,25 @@ SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid); t (1 row) +-- check that the stats are reset +SELECT stats_reset IS NULL AS has_no_stats_reset FROM pg_stat_all_indexes WHERE indexrelid = :stats_test_idx1_oid; + has_no_stats_reset +-------------------- + t +(1 row) + +SELECT pg_stat_reset_single_table_counters(:stats_test_idx1_oid); + pg_stat_reset_single_table_counters +------------------------------------- + +(1 row) + +SELECT stats_reset IS NOT NULL AS has_stats_reset FROM pg_stat_all_indexes WHERE indexrelid = :stats_test_idx1_oid; + has_stats_reset +----------------- + t +(1 row) + -- pg_stat_have_stats returns false for dropped index with stats SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid); pg_stat_have_stats diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 54e72866344..44954a4d910 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -312,8 +312,9 @@ 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 NULL AS has_no_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; @@ -572,6 +573,11 @@ SET enable_seqscan TO off; select a from stats_test_tab1 where a = 3; SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid); +-- check that the stats are reset +SELECT stats_reset IS NULL AS has_no_stats_reset FROM pg_stat_all_indexes WHERE indexrelid = :stats_test_idx1_oid; +SELECT pg_stat_reset_single_table_counters(:stats_test_idx1_oid); +SELECT stats_reset IS NOT NULL AS has_stats_reset FROM pg_stat_all_indexes WHERE indexrelid = :stats_test_idx1_oid; + -- pg_stat_have_stats returns false for dropped index with stats SELECT pg_stat_have_stats('relation', :dboid, :stats_test_idx1_oid); DROP index stats_test_idx1; -- 2.34.1
