Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit ae4fdde

Browse filesBrowse files
petergeogheganpetergeoghegan
committed
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 <corey.huinker@gmail.com> Reviewed-By: Peter Geoghegan <pg@bowt.ie> Reviewed-By: Andres Freund <andres@anarazel.de> Discussion: https://postgr.es/m/CADkLM=ded21M9iZ36hHm-vj2rE2d=zcKpUQMds__Xm2pxLfHKA@mail.gmail.com
1 parent 3b50275 commit ae4fdde
Copy full SHA for ae4fdde

File tree

9 files changed

+80
-16
lines changed
Filter options

9 files changed

+80
-16
lines changed

‎doc/src/sgml/monitoring.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/monitoring.sgml
+23-5Lines changed: 23 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -4789,7 +4789,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
47894789
<structfield>n_tup_ins</structfield> <type>bigint</type>
47904790
</para>
47914791
<para>
4792-
Number of rows inserted
4792+
Total number of rows inserted
47934793
</para></entry>
47944794
</row>
47954795

@@ -4798,7 +4798,10 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
47984798
<structfield>n_tup_upd</structfield> <type>bigint</type>
47994799
</para>
48004800
<para>
4801-
Number of rows updated (includes <link linkend="storage-hot">HOT updated rows</link>)
4801+
Total number of rows updated. (This includes row updates
4802+
counted in <structfield>n_tup_hot_upd</structfield> and
4803+
<structfield>n_tup_newpage_upd</structfield>, and remaining
4804+
non-<acronym>HOT</acronym> updates.)
48024805
</para></entry>
48034806
</row>
48044807

@@ -4807,7 +4810,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
48074810
<structfield>n_tup_del</structfield> <type>bigint</type>
48084811
</para>
48094812
<para>
4810-
Number of rows deleted
4813+
Total number of rows deleted
48114814
</para></entry>
48124815
</row>
48134816

@@ -4816,8 +4819,23 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
48164819
<structfield>n_tup_hot_upd</structfield> <type>bigint</type>
48174820
</para>
48184821
<para>
4819-
Number of rows HOT updated (i.e., with no separate index
4820-
update required)
4822+
Number of rows <link linkend="storage-hot">HOT updated</link>.
4823+
These are updates where no successor versions are required in
4824+
indexes.
4825+
</para></entry>
4826+
</row>
4827+
4828+
<row>
4829+
<entry role="catalog_table_entry"><para role="column_definition">
4830+
<structfield>n_tup_newpage_upd</structfield> <type>bigint</type>
4831+
</para>
4832+
<para>
4833+
Number of rows updated where the successor version goes onto a
4834+
<emphasis>new</emphasis> heap page, leaving behind an original
4835+
version with a
4836+
<link linkend="storage-tuple-layout"><structfield>t_ctid</structfield>
4837+
field</link> that points to a different heap page. These are
4838+
always non-<acronym>HOT</acronym> updates.
48214839
</para></entry>
48224840
</row>
48234841

‎src/backend/access/heap/heapam.c

Copy file name to clipboardExpand all lines: src/backend/access/heap/heapam.c
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3803,7 +3803,7 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
38033803
if (have_tuple_lock)
38043804
UnlockTupleTuplock(relation, &(oldtup.t_self), *lockmode);
38053805

3806-
pgstat_count_heap_update(relation, use_hot_update);
3806+
pgstat_count_heap_update(relation, use_hot_update, newbuf != buffer);
38073807

38083808
/*
38093809
* If heaptup is a private copy, release it. Don't forget to copy t_self

‎src/backend/catalog/system_views.sql

Copy file name to clipboardExpand all lines: src/backend/catalog/system_views.sql
+3-1Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -665,6 +665,7 @@ CREATE VIEW pg_stat_all_tables AS
665665
pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,
666666
pg_stat_get_tuples_deleted(C.oid) AS n_tup_del,
667667
pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd,
668+
pg_stat_get_tuples_newpage_updated(C.oid) AS n_tup_newpage_upd,
668669
pg_stat_get_live_tuples(C.oid) AS n_live_tup,
669670
pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
670671
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
696697
pg_stat_get_xact_tuples_inserted(C.oid) AS n_tup_ins,
697698
pg_stat_get_xact_tuples_updated(C.oid) AS n_tup_upd,
698699
pg_stat_get_xact_tuples_deleted(C.oid) AS n_tup_del,
699-
pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd
700+
pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd,
701+
pg_stat_get_xact_tuples_newpage_updated(C.oid) AS n_tup_newpage_upd
700702
FROM pg_class C LEFT JOIN
701703
pg_index I ON C.oid = I.indrelid
702704
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)

‎src/backend/utils/activity/pgstat_relation.c

Copy file name to clipboardExpand all lines: src/backend/utils/activity/pgstat_relation.c
+10-2Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -373,18 +373,25 @@ pgstat_count_heap_insert(Relation rel, PgStat_Counter n)
373373
* count a tuple update
374374
*/
375375
void
376-
pgstat_count_heap_update(Relation rel, bool hot)
376+
pgstat_count_heap_update(Relation rel, bool hot, bool newpage)
377377
{
378+
Assert(!(hot && newpage));
379+
378380
if (pgstat_should_count_relation(rel))
379381
{
380382
PgStat_TableStatus *pgstat_info = rel->pgstat_info;
381383

382384
ensure_tabstat_xact_level(pgstat_info);
383385
pgstat_info->trans->tuples_updated++;
384386

385-
/* t_tuples_hot_updated is nontransactional, so just advance it */
387+
/*
388+
* t_tuples_hot_updated and t_tuples_newpage_updated counters are
389+
* nontransactional, so just advance them
390+
*/
386391
if (hot)
387392
pgstat_info->t_counts.t_tuples_hot_updated++;
393+
else if (newpage)
394+
pgstat_info->t_counts.t_tuples_newpage_updated++;
388395
}
389396
}
390397

@@ -804,6 +811,7 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
804811
tabentry->tuples_updated += lstats->t_counts.t_tuples_updated;
805812
tabentry->tuples_deleted += lstats->t_counts.t_tuples_deleted;
806813
tabentry->tuples_hot_updated += lstats->t_counts.t_tuples_hot_updated;
814+
tabentry->tuples_newpage_updated += lstats->t_counts.t_tuples_newpage_updated;
807815

808816
/*
809817
* If table was truncated/dropped, first reset the live/dead counters.

‎src/backend/utils/adt/pgstatfuncs.c

Copy file name to clipboardExpand all lines: src/backend/utils/adt/pgstatfuncs.c
+18Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -92,6 +92,9 @@ PG_STAT_GET_RELENTRY_INT64(tuples_fetched)
9292
/* pg_stat_get_tuples_hot_updated */
9393
PG_STAT_GET_RELENTRY_INT64(tuples_hot_updated)
9494

95+
/* pg_stat_get_tuples_newpage_updated */
96+
PG_STAT_GET_RELENTRY_INT64(tuples_newpage_updated)
97+
9598
/* pg_stat_get_tuples_inserted */
9699
PG_STAT_GET_RELENTRY_INT64(tuples_inserted)
97100

@@ -1618,6 +1621,21 @@ pg_stat_get_xact_tuples_hot_updated(PG_FUNCTION_ARGS)
16181621
PG_RETURN_INT64(result);
16191622
}
16201623

1624+
Datum
1625+
pg_stat_get_xact_tuples_newpage_updated(PG_FUNCTION_ARGS)
1626+
{
1627+
Oid relid = PG_GETARG_OID(0);
1628+
int64 result;
1629+
PgStat_TableStatus *tabentry;
1630+
1631+
if ((tabentry = find_tabstat_entry(relid)) == NULL)
1632+
result = 0;
1633+
else
1634+
result = (int64) (tabentry->t_counts.t_tuples_newpage_updated);
1635+
1636+
PG_RETURN_INT64(result);
1637+
}
1638+
16211639
Datum
16221640
pg_stat_get_xact_blocks_fetched(PG_FUNCTION_ARGS)
16231641
{

‎src/include/catalog/catversion.h

Copy file name to clipboardExpand all lines: src/include/catalog/catversion.h
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202303181
60+
#define CATALOG_VERSION_NO 202303231
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

Copy file name to clipboardExpand all lines: src/include/catalog/pg_proc.dat
+10Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5360,6 +5360,11 @@
53605360
proname => 'pg_stat_get_tuples_hot_updated', provolatile => 's',
53615361
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
53625362
prosrc => 'pg_stat_get_tuples_hot_updated' },
5363+
{ oid => '8614',
5364+
descr => 'statistics: number of tuples updated onto a new page',
5365+
proname => 'pg_stat_get_tuples_newpage_updated', provolatile => 's',
5366+
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
5367+
prosrc => 'pg_stat_get_tuples_newpage_updated' },
53635368
{ oid => '2878', descr => 'statistics: number of live tuples',
53645369
proname => 'pg_stat_get_live_tuples', provolatile => 's', proparallel => 'r',
53655370
prorettype => 'int8', proargtypes => 'oid',
@@ -5823,6 +5828,11 @@
58235828
proname => 'pg_stat_get_xact_tuples_hot_updated', provolatile => 'v',
58245829
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
58255830
prosrc => 'pg_stat_get_xact_tuples_hot_updated' },
5831+
{ oid => '8615',
5832+
descr => 'statistics: number of tuples updated onto a new page in current transaction',
5833+
proname => 'pg_stat_get_xact_tuples_newpage_updated', provolatile => 'v',
5834+
proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
5835+
prosrc => 'pg_stat_get_xact_tuples_newpage_updated' },
58265836
{ oid => '3044',
58275837
descr => 'statistics: number of blocks fetched in current transaction',
58285838
proname => 'pg_stat_get_xact_blocks_fetched', provolatile => 'v',

‎src/include/pgstat.h

Copy file name to clipboardExpand all lines: src/include/pgstat.h
+5-3Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -151,8 +151,8 @@ typedef struct PgStat_BackendSubEntry
151151
* the index AM, while tuples_fetched is the number of tuples successfully
152152
* fetched by heap_fetch under the control of simple indexscans for this index.
153153
*
154-
* tuples_inserted/updated/deleted/hot_updated count attempted actions,
155-
* regardless of whether the transaction committed. delta_live_tuples,
154+
* tuples_inserted/updated/deleted/hot_updated/newpage_updated count attempted
155+
* actions, regardless of whether the transaction committed. delta_live_tuples,
156156
* delta_dead_tuples, and changed_tuples are set depending on commit or abort.
157157
* Note that delta_live_tuples and delta_dead_tuples can be negative!
158158
* ----------
@@ -168,6 +168,7 @@ typedef struct PgStat_TableCounts
168168
PgStat_Counter t_tuples_updated;
169169
PgStat_Counter t_tuples_deleted;
170170
PgStat_Counter t_tuples_hot_updated;
171+
PgStat_Counter t_tuples_newpage_updated;
171172
bool t_truncdropped;
172173

173174
PgStat_Counter t_delta_live_tuples;
@@ -401,6 +402,7 @@ typedef struct PgStat_StatTabEntry
401402
PgStat_Counter tuples_updated;
402403
PgStat_Counter tuples_deleted;
403404
PgStat_Counter tuples_hot_updated;
405+
PgStat_Counter tuples_newpage_updated;
404406

405407
PgStat_Counter live_tuples;
406408
PgStat_Counter dead_tuples;
@@ -616,7 +618,7 @@ extern void pgstat_report_analyze(Relation rel,
616618
} while (0)
617619

618620
extern void pgstat_count_heap_insert(Relation rel, PgStat_Counter n);
619-
extern void pgstat_count_heap_update(Relation rel, bool hot);
621+
extern void pgstat_count_heap_update(Relation rel, bool hot, bool newpage);
620622
extern void pgstat_count_heap_delete(Relation rel);
621623
extern void pgstat_count_truncate(Relation rel);
622624
extern void pgstat_update_heap_dead_tuples(Relation rel, int delta);

‎src/test/regress/expected/rules.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/rules.out
+9-3Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1789,6 +1789,7 @@ pg_stat_all_tables| SELECT c.oid AS relid,
17891789
pg_stat_get_tuples_updated(c.oid) AS n_tup_upd,
17901790
pg_stat_get_tuples_deleted(c.oid) AS n_tup_del,
17911791
pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd,
1792+
pg_stat_get_tuples_newpage_updated(c.oid) AS n_tup_newpage_upd,
17921793
pg_stat_get_live_tuples(c.oid) AS n_live_tup,
17931794
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
17941795
pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze,
@@ -2146,6 +2147,7 @@ pg_stat_sys_tables| SELECT relid,
21462147
n_tup_upd,
21472148
n_tup_del,
21482149
n_tup_hot_upd,
2150+
n_tup_newpage_upd,
21492151
n_live_tup,
21502152
n_dead_tup,
21512153
n_mod_since_analyze,
@@ -2193,6 +2195,7 @@ pg_stat_user_tables| SELECT relid,
21932195
n_tup_upd,
21942196
n_tup_del,
21952197
n_tup_hot_upd,
2198+
n_tup_newpage_upd,
21962199
n_live_tup,
21972200
n_dead_tup,
21982201
n_mod_since_analyze,
@@ -2244,7 +2247,8 @@ pg_stat_xact_all_tables| SELECT c.oid AS relid,
22442247
pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins,
22452248
pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd,
22462249
pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del,
2247-
pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd
2250+
pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd,
2251+
pg_stat_get_xact_tuples_newpage_updated(c.oid) AS n_tup_newpage_upd
22482252
FROM ((pg_class c
22492253
LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
22502254
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
@@ -2260,7 +2264,8 @@ pg_stat_xact_sys_tables| SELECT relid,
22602264
n_tup_ins,
22612265
n_tup_upd,
22622266
n_tup_del,
2263-
n_tup_hot_upd
2267+
n_tup_hot_upd,
2268+
n_tup_newpage_upd
22642269
FROM pg_stat_xact_all_tables
22652270
WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
22662271
pg_stat_xact_user_functions| SELECT p.oid AS funcid,
@@ -2282,7 +2287,8 @@ pg_stat_xact_user_tables| SELECT relid,
22822287
n_tup_ins,
22832288
n_tup_upd,
22842289
n_tup_del,
2285-
n_tup_hot_upd
2290+
n_tup_hot_upd,
2291+
n_tup_newpage_upd
22862292
FROM pg_stat_xact_all_tables
22872293
WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
22882294
pg_statio_all_indexes| SELECT c.oid AS relid,

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.