From 55204b3d2f719f5dd8c308ea722606a40b3d09b8 Mon Sep 17 00:00:00 2001 From: coreyhuinker Date: Fri, 27 Jan 2023 17:56:59 -0500 Subject: [PATCH v1] Add n_tup_newpage_upd to pg_stat_all_tables and pg_stat_xact_all_tables. This value reflects the number of tuples updated where the new tuple was placed on a different page than the previous version. --- doc/src/sgml/monitoring.sgml | 9 +++++++++ src/backend/access/heap/heapam.c | 10 ++++++---- src/backend/catalog/system_views.sql | 4 +++- src/backend/utils/activity/pgstat_relation.c | 8 ++++++-- src/backend/utils/adt/pgstatfuncs.c | 18 ++++++++++++++++++ src/include/catalog/pg_proc.dat | 9 +++++++++ src/include/pgstat.h | 14 ++++++++++++-- src/test/regress/expected/rules.out | 12 +++++++++--- 8 files changed, 72 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 1756f1a4b6..f0291a21fb 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -4523,6 +4523,15 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i + + + n_tup_newpage_upd bigint + + + Number of rows updated where the new row is on a different page than the previous version + + + n_live_tup bigint diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index e6024a980b..f5aa429a9a 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -3155,7 +3155,8 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup, pagefree; bool have_tuple_lock = false; bool iscombo; - bool use_hot_update = false; + PgStat_HeapUpdateType update_type = PGSTAT_HEAPUPDATE_NON_HOT; + bool key_intact; bool all_visible_cleared = false; bool all_visible_cleared_new = false; @@ -3838,10 +3839,11 @@ l2: * changed. */ if (!bms_overlap(modified_attrs, hot_attrs)) - use_hot_update = true; + update_type = PGSTAT_HEAPUPDATE_HOT; } else { + update_type = PGSTAT_HEAPUPDATE_NEW_PAGE; /* Set a hint that the old page could use prune/defrag */ PageSetFull(page); } @@ -3875,7 +3877,7 @@ l2: */ PageSetPrunable(page, xid); - if (use_hot_update) + if (update_type == PGSTAT_HEAPUPDATE_HOT) { /* Mark the old tuple as HOT-updated */ HeapTupleSetHotUpdated(&oldtup); @@ -3986,7 +3988,7 @@ l2: if (have_tuple_lock) UnlockTupleTuplock(relation, &(oldtup.t_self), *lockmode); - pgstat_count_heap_update(relation, use_hot_update); + pgstat_count_heap_update(relation, update_type); /* * If heaptup is a private copy, release it. Don't forget to copy t_self diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 8608e3fa5b..292a9b88b3 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -665,6 +665,7 @@ CREATE VIEW pg_stat_all_tables AS pg_stat_get_tuples_updated(C.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(C.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd, + pg_stat_get_tuples_newpage_updated(C.oid) AS n_tup_newpage_upd, pg_stat_get_live_tuples(C.oid) AS n_live_tup, pg_stat_get_dead_tuples(C.oid) AS n_dead_tup, pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze, @@ -696,7 +697,8 @@ CREATE VIEW pg_stat_xact_all_tables AS pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins, pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd, pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del, - pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd + pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd, + pg_stat_get_xact_tuples_newpage_updated(C.oid) AS n_tup_newpage_upd FROM pg_class C LEFT JOIN pg_index I ON C.oid = I.indrelid LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index 2e20b93c20..920ee71e35 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -362,7 +362,7 @@ pgstat_count_heap_insert(Relation rel, PgStat_Counter n) * count a tuple update */ void -pgstat_count_heap_update(Relation rel, bool hot) +pgstat_count_heap_update(Relation rel, PgStat_HeapUpdateType hut) { if (pgstat_should_count_relation(rel)) { @@ -372,8 +372,11 @@ pgstat_count_heap_update(Relation rel, bool hot) pgstat_info->trans->tuples_updated++; /* t_tuples_hot_updated is nontransactional, so just advance it */ - if (hot) + if (hut == PGSTAT_HEAPUPDATE_HOT) pgstat_info->t_counts.t_tuples_hot_updated++; + else if (hut == PGSTAT_HEAPUPDATE_NEW_PAGE) + pgstat_info->t_counts.t_tuples_newpage_updated++; + } } @@ -793,6 +796,7 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait) tabentry->tuples_updated += lstats->t_counts.t_tuples_updated; tabentry->tuples_deleted += lstats->t_counts.t_tuples_deleted; tabentry->tuples_hot_updated += lstats->t_counts.t_tuples_hot_updated; + tabentry->tuples_newpage_updated += lstats->t_counts.t_tuples_newpage_updated; /* * If table was truncated/dropped, first reset the live/dead counters. diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 6737493402..ace9a46b6e 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) @@ -1479,6 +1482,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) { diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index c0f2a8a77c..4a7ca3e146 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5337,6 +5337,10 @@ proname => 'pg_stat_get_tuples_hot_updated', provolatile => 's', proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', prosrc => 'pg_stat_get_tuples_hot_updated' }, +{ oid => '4642', descr => 'statistics: number of tuples non-hot newpage updated', + proname => 'pg_stat_get_tuples_newpage_updated', provolatile => 's', + proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', + prosrc => 'pg_stat_get_tuples_newpage_updated' }, { oid => '2878', descr => 'statistics: number of live tuples', proname => 'pg_stat_get_live_tuples', provolatile => 's', proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', @@ -5791,6 +5795,11 @@ proname => 'pg_stat_get_xact_tuples_hot_updated', provolatile => 'v', proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', prosrc => 'pg_stat_get_xact_tuples_hot_updated' }, +{ oid => '8704', + descr => 'statistics: number of tuples newpage updated in current transaction', + proname => 'pg_stat_get_xact_tuples_newpage_updated', provolatile => 'v', + proparallel => 'r', prorettype => 'int8', proargtypes => 'oid', + prosrc => 'pg_stat_get_xact_tuples_newpage_updated' }, { oid => '3044', descr => 'statistics: number of blocks fetched in current transaction', proname => 'pg_stat_get_xact_blocks_fetched', provolatile => 'v', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 5e3326a3b9..4b21f8ffe7 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -81,6 +81,13 @@ typedef enum SessionEndType DISCONNECT_KILLED } SessionEndType; +typedef enum PgStat_HeapUpdateType +{ + PGSTAT_HEAPUPDATE_NON_HOT, /* regular update, same page */ + PGSTAT_HEAPUPDATE_HOT, /* HOT update */ + PGSTAT_HEAPUPDATE_NEW_PAGE /* regular update, new page */ +} PgStat_HeapUpdateType; + /* ---------- * The data type used for counters. * ---------- @@ -159,7 +166,7 @@ typedef struct PgStat_BackendSubEntry * the index AM, while tuples_fetched is the number of tuples successfully * fetched by heap_fetch under the control of simple indexscans for this index. * - * tuples_inserted/updated/deleted/hot_updated count attempted actions, + * tuples_inserted/updated/deleted/hot_updated/newpage_updated count attempted actions, * regardless of whether the transaction committed. delta_live_tuples, * delta_dead_tuples, and changed_tuples are set depending on commit or abort. * Note that delta_live_tuples and delta_dead_tuples can be negative! @@ -176,6 +183,7 @@ typedef struct PgStat_TableCounts PgStat_Counter t_tuples_updated; PgStat_Counter t_tuples_deleted; PgStat_Counter t_tuples_hot_updated; + PgStat_Counter t_tuples_newpage_updated; bool t_truncdropped; PgStat_Counter t_delta_live_tuples; @@ -363,6 +371,7 @@ typedef struct PgStat_StatTabEntry PgStat_Counter tuples_updated; PgStat_Counter tuples_deleted; PgStat_Counter tuples_hot_updated; + PgStat_Counter tuples_newpage_updated; PgStat_Counter live_tuples; PgStat_Counter dead_tuples; @@ -560,7 +569,8 @@ extern void pgstat_report_analyze(Relation rel, } while (0) extern void pgstat_count_heap_insert(Relation rel, PgStat_Counter n); -extern void pgstat_count_heap_update(Relation rel, bool hot); +extern void pgstat_count_heap_update(Relation rel, PgStat_HeapUpdateType hut); + extern void pgstat_count_heap_delete(Relation rel); extern void pgstat_count_truncate(Relation rel); extern void pgstat_update_heap_dead_tuples(Relation rel, int delta); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index e7a2f5856a..bdd56d6283 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1789,6 +1789,7 @@ pg_stat_all_tables| SELECT c.oid AS relid, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, + pg_stat_get_tuples_newpage_updated(c.oid) AS n_tup_newpage_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze, @@ -2134,6 +2135,7 @@ pg_stat_sys_tables| SELECT relid, n_tup_upd, n_tup_del, n_tup_hot_upd, + n_tup_newpage_upd, n_live_tup, n_dead_tup, n_mod_since_analyze, @@ -2181,6 +2183,7 @@ pg_stat_user_tables| SELECT relid, n_tup_upd, n_tup_del, n_tup_hot_upd, + n_tup_newpage_upd, n_live_tup, n_dead_tup, n_mod_since_analyze, @@ -2232,7 +2235,8 @@ pg_stat_xact_all_tables| SELECT c.oid AS relid, pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del, - pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd + pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd, + pg_stat_get_xact_tuples_newpage_updated(c.oid) AS n_tup_newpage_upd FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) @@ -2248,7 +2252,8 @@ pg_stat_xact_sys_tables| SELECT relid, n_tup_ins, n_tup_upd, n_tup_del, - n_tup_hot_upd + n_tup_hot_upd, + n_tup_newpage_upd FROM pg_stat_xact_all_tables WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); pg_stat_xact_user_functions| SELECT p.oid AS funcid, @@ -2270,7 +2275,8 @@ pg_stat_xact_user_tables| SELECT relid, n_tup_ins, n_tup_upd, n_tup_del, - n_tup_hot_upd + n_tup_hot_upd, + n_tup_newpage_upd FROM pg_stat_xact_all_tables WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); pg_statio_all_indexes| SELECT c.oid AS relid, -- 2.39.1