This patch adds the n_tup_newpage_upd to all the table stat views.
Just as we currently track HOT updates, it should be beneficial to track
updates where the new tuple cannot fit on the existing page and must go to
a different one.
Hopefully this can give users some insight as to whether their current
fillfactor settings need to be adjusted.
My chosen implementation replaces the hot-update boolean with an
update_type which is currently a three-value enum. I favored that
only slightly over adding a separate newpage-update boolean because the two
events are mutually exclusive and fewer parameters is less overhead and one
less assertion check. The relative wisdom of this choice may not come to
light until we add a new measurement and see whether that new measurement
overlaps either is-hot or is-new-page.
From 55204b3d2f719f5dd8c308ea722606a40b3d09b8 Mon Sep 17 00:00:00 2001
From: coreyhuinker <corey.huin...@gmail.com>
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
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>n_tup_newpage_upd</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of rows updated where the new row is on a different page than the previous version
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>n_live_tup</structfield> <type>bigint</type>
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
