From ae4fdde1352fa6b2c9123e91435efafc78c370a0 Mon Sep 17 00:00:00 2001 From: Peter Geoghegan Date: Thu, 23 Mar 2023 11:16:17 -0700 Subject: Count updates that move row to a new page. Add pgstat counter to track row updates that result in the successor version going to a new heap page, leaving behind an original version whose t_ctid points to the new version. The current count is shown by the n_tup_newpage_upd column of each of the pg_stat_*_tables views. The new n_tup_newpage_upd column complements the existing n_tup_hot_upd and n_tup_upd columns. Tables that have high n_tup_newpage_upd values (relative to n_tup_upd) are good candidates for tuning heap fillfactor. Corey Huinker, with small tweaks by me. Author: Corey Huinker Reviewed-By: Peter Geoghegan Reviewed-By: Andres Freund Discussion: https://postgr.es/m/CADkLM=ded21M9iZ36hHm-vj2rE2d=zcKpUQMds__Xm2pxLfHKA@mail.gmail.com --- src/backend/utils/adt/pgstatfuncs.c | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) (limited to 'src/backend/utils/adt/pgstatfuncs.c') diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 35c6d465553..56119737c89 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -92,6 +92,9 @@ PG_STAT_GET_RELENTRY_INT64(tuples_fetched) /* pg_stat_get_tuples_hot_updated */ PG_STAT_GET_RELENTRY_INT64(tuples_hot_updated) +/* pg_stat_get_tuples_newpage_updated */ +PG_STAT_GET_RELENTRY_INT64(tuples_newpage_updated) + /* pg_stat_get_tuples_inserted */ PG_STAT_GET_RELENTRY_INT64(tuples_inserted) @@ -1618,6 +1621,21 @@ pg_stat_get_xact_tuples_hot_updated(PG_FUNCTION_ARGS) PG_RETURN_INT64(result); } +Datum +pg_stat_get_xact_tuples_newpage_updated(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int64 result; + PgStat_TableStatus *tabentry; + + if ((tabentry = find_tabstat_entry(relid)) == NULL) + result = 0; + else + result = (int64) (tabentry->t_counts.t_tuples_newpage_updated); + + PG_RETURN_INT64(result); +} + Datum pg_stat_get_xact_blocks_fetched(PG_FUNCTION_ARGS) { -- cgit v1.2.3