Hi David,
On Sat, Oct 24, 2026 at 5:35 PM Álvaro Herrera
<[email protected]> wrote:
> Yeah, we have a patch to add this already; see the thread at
> https://postgr.es/m/trinity-08d3329b-f6b2-4c11-91d7-2198e50bf4fc-1790681922354@trinity-msg-rest-gmx-gmx-live-58cc8f554d-c56lm
That is CF 7365, which started from Bernhard Wonisch's report of
ATTACH PARTITION slowing down as pg_constraint grows. v6 is attached,
rebased on master dfd7ede529f; 0001 has the same code as the version
posted in that thread. It adds a non-unique btree index on confrelid,
like the one on contypid, and uses it in the four places that look up
foreign keys by referenced relation.
Three of them are the ones your patch changes. The fourth is
heap_truncate_find_FKs(), used by TRUNCATE and by ON COMMIT DELETE
ROWS at commit. It takes a list of relations, so it does one range
scan of the index from the lowest to the highest OID in the list
instead of one probe per relation. With 95,759 constraints:
- 100 TRUNCATEs of a referenced table: master does 100 seq scans of
pg_constraint (9.6M rows read) and 2.9G instructions; v6 does no
seq scan and 0.20G.
- 100 commits that touch an ON COMMIT DELETE ROWS temp table with a
trigger: 2.8G instructions on master, 0.084G on v6 (0.081G for the
same table without a trigger, which skips the lookup).
Your repro with v6, at 1/10 of its scale (3,200 partitions, 95,756
constraints), old cluster on master, three upgrades per side:
- master: 3,200 seq scans of pg_constraint in the restored database,
one per ATTACH PARTITION; 187.7G instructions for all of pg_upgrade.
- v6: no seq scan, 3,200 scans of the new index; 100.1G instructions.
At 2/10 it is 556.2G against 206.6G: from 1/10 to 2/10 master grows
2.96x and v6 2.06x. After each v6 upgrade, from master or from 18.6,
amcheck with heapallindexed is clean on all pg_constraint indexes.
I have not re-timed it, so there is no wall-clock number yet.
On the trade-offs:
- Write cost: creating a NOT NULL constraint takes about 7.6k more
instructions, +5%. WAL when creating constraints grows 1-4%,
about 120 bytes per pg_constraint row (measured on 15 Oct).
- Size: 16 kB per database after initdb; 6.3-6.8 MB next to a 342 MB
pg_constraint with about 1M constraints (15 Oct).
- Rows that are not foreign keys go in the index under zero, as with
contypid. None of the four lookups visits them, since they all ask
for a nonzero confrelid.
0001 stands alone. 0002 is optional: a check in stats.sql that each
of the four lookups adds a scan of the new index. Reverting any one
call site to a heap scan fails exactly its own check. The OID (9370)
is in the development range for renumbering at commit, and catversion
is left to the committer.
Regards,
Manu
From 7d978d6741babe92cab56fccbd1b56eccf2e072c Mon Sep 17 00:00:00 2001
From: Manuel Reyes Bravo <[email protected]>
Date: Sun, 4 Oct 2026 19:18:35 -0300
Subject: [PATCH v6 1/2] Index pg_constraint.confrelid to avoid seqscans of the
catalog
Four places look up the foreign keys that reference a relation by
scanning pg_constraint for rows whose confrelid matches. There was no
index on confrelid, so each was a sequential scan of the whole catalog:
- CloneFkReferenced() (ATTACH PARTITION, PARTITION OF, and
so every partition pg_upgrade restores)
- GetParentedForeignKeyRefs() (DETACH PARTITION)
- ATPrepChangePersistence() (ALTER TABLE ... SET UNLOGGED)
- heap_truncate_find_FKs() (TRUNCATE, and ON COMMIT DELETE ROWS
at commit)
That was cheap while pg_constraint stayed small. Since not-null
constraints gained pg_constraint rows (commit 14e87ffa5c5), the catalog
holds a row per not-null column, and these scans grow with the total
number of constraints in the database. pg_upgrade pays one such scan
per partition it restores, and with an ON COMMIT DELETE ROWS temporary
table that has a foreign key or trigger, so does every commit of a
transaction that used temporary tables.
Add a non-unique btree index on confrelid, like the existing one on
contypid, and use it in all four places. As with contypid, the rows
that do not use the column (every constraint other than a foreign key)
are indexed under zero.
heap_truncate_find_FKs() takes a list of relations. Probing the index
once per relation would lose to the old seqscan when the catalog is
small and the list is long (many ON COMMIT DELETE ROWS tables, or a
TRUNCATE of thousands of tables), so it does a single range scan of the
index from the smallest to the largest OID in the list instead, and
skips foreign keys that reference a relation outside the list, as the
seqscan did. The rows of other constraint types sort before any
relation OID and are never visited.
Reported-by: Bernhard Wonisch <[email protected]>
Suggested-by: Tom Lane <[email protected]>
Discussion: https://postgr.es/m/trinity-08d3329b-f6b2-4c11-91d7-2198e50bf4fc-1790681922354@trinity-msg-rest-gmx-gmx-live-58cc8f554d-c56lm
Discussion: https://postgr.es/m/[email protected]
Discussion: https://postgr.es/m/CAApHDvpxGcb-_ddd+qL+F=vmv4d3wznbuci3wqyj0hcfnc9...@mail.gmail.com
---
src/backend/catalog/heap.c | 39 ++++++++++++++++++++++++++---
src/backend/commands/tablecmds.c | 37 ++++++++++++++++-----------
src/include/catalog/pg_constraint.h | 1 +
3 files changed, 59 insertions(+), 18 deletions(-)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index f0a159c1b77..b0cd3e48901 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -3830,16 +3830,28 @@ heap_truncate_find_FKs(List *relationIds)
List *parent_cons;
ListCell *cell;
ScanKeyData key;
+ ScanKeyData rangekey[2];
+ Oid minoid;
+ Oid maxoid;
Relation fkeyRel;
SysScanDesc fkeyScan;
HeapTuple tuple;
bool restart;
+ if (relationIds == NIL)
+ return NIL;
+
oids = list_copy(relationIds);
/*
- * Must scan pg_constraint. Right now, it is a seqscan because there is
- * no available index on confrelid.
+ * Must scan pg_constraint. We do a single range scan of the index on
+ * confrelid, from the smallest to the largest OID in our list. Only
+ * foreign keys have a nonzero confrelid, so the rows of all other
+ * constraints sort before the range and are never visited; foreign keys
+ * that fall inside the range but reference a relation not in our list are
+ * skipped below. Each pass costs one index probe for a single relation,
+ * and never more than visiting each foreign key once however long the
+ * list is.
*/
fkeyRel = table_open(ConstraintRelationId, AccessShareLock);
@@ -3847,8 +3859,27 @@ restart:
restart = false;
parent_cons = NIL;
- fkeyScan = systable_beginscan(fkeyRel, InvalidOid, false,
- NULL, 0, NULL);
+ minoid = maxoid = linitial_oid(oids);
+ foreach(cell, oids)
+ {
+ Oid relid = lfirst_oid(cell);
+
+ if (relid < minoid)
+ minoid = relid;
+ if (relid > maxoid)
+ maxoid = relid;
+ }
+ ScanKeyInit(&rangekey[0],
+ Anum_pg_constraint_confrelid,
+ BTGreaterEqualStrategyNumber, F_OIDGE,
+ ObjectIdGetDatum(minoid));
+ ScanKeyInit(&rangekey[1],
+ Anum_pg_constraint_confrelid,
+ BTLessEqualStrategyNumber, F_OIDLE,
+ ObjectIdGetDatum(maxoid));
+
+ fkeyScan = systable_beginscan(fkeyRel, ConstraintConfRelidIndexId, true,
+ NULL, 2, rangekey);
while (HeapTupleIsValid(tuple = systable_getnext(fkeyScan)))
{
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 97e16f3eb47..091b5831c85 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -11368,16 +11368,23 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
ScanKeyInit(&key[0],
Anum_pg_constraint_confrelid, BTEqualStrategyNumber,
F_OIDEQ, ObjectIdGetDatum(RelationGetRelid(parentRel)));
- ScanKeyInit(&key[1],
- Anum_pg_constraint_contype, BTEqualStrategyNumber,
- F_CHAREQ, CharGetDatum(CONSTRAINT_FOREIGN));
- /* This is a seqscan, as we don't have a usable index ... */
- scan = systable_beginscan(pg_constraint, InvalidOid, true,
- NULL, 2, key);
+
+ /*
+ * Look this up through the index on confrelid rather than seqscanning all
+ * of pg_constraint. That scan grew expensive once not-null constraints
+ * started to have pg_constraint rows, making its cost scale with the
+ * total number of constraints in the database. Only foreign keys set
+ * confrelid, so filtering on contype in the loop is just
+ * belt-and-suspenders.
+ */
+ scan = systable_beginscan(pg_constraint, ConstraintConfRelidIndexId, true,
+ NULL, 1, key);
while ((tuple = systable_getnext(scan)) != NULL)
{
Form_pg_constraint constrForm = (Form_pg_constraint) GETSTRUCT(tuple);
+ if (constrForm->contype != CONSTRAINT_FOREIGN)
+ continue;
clone = lappend_oid(clone, constrForm->oid);
}
systable_endscan(scan);
@@ -19575,8 +19582,8 @@ ATPrepChangePersistence(AlteredTableInfo *tab, Relation rel, bool toLogged)
pg_constraint = table_open(ConstraintRelationId, AccessShareLock);
/*
- * Scan conrelid if changing to permanent, else confrelid. This also
- * determines whether a useful index exists.
+ * Scan conrelid if changing to permanent, else confrelid. Both columns
+ * are indexed, so this also determines which index to use.
*/
ScanKeyInit(&skey[0],
toLogged ? Anum_pg_constraint_conrelid :
@@ -19584,7 +19591,8 @@ ATPrepChangePersistence(AlteredTableInfo *tab, Relation rel, bool toLogged)
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(RelationGetRelid(rel)));
scan = systable_beginscan(pg_constraint,
- toLogged ? ConstraintRelidTypidNameIndexId : InvalidOid,
+ toLogged ? ConstraintRelidTypidNameIndexId :
+ ConstraintConfRelidIndexId,
true, NULL, 1, skey);
while (HeapTupleIsValid(tuple = systable_getnext(scan)))
@@ -22690,16 +22698,17 @@ GetParentedForeignKeyRefs(Relation partition)
ScanKeyInit(&key[0],
Anum_pg_constraint_confrelid, BTEqualStrategyNumber,
F_OIDEQ, ObjectIdGetDatum(RelationGetRelid(partition)));
- ScanKeyInit(&key[1],
- Anum_pg_constraint_contype, BTEqualStrategyNumber,
- F_CHAREQ, CharGetDatum(CONSTRAINT_FOREIGN));
- /* XXX This is a seqscan, as we don't have a usable index */
- scan = systable_beginscan(pg_constraint, InvalidOid, true, NULL, 2, key);
+ /* Use the index on confrelid; only FKs set it, so filter contype below */
+ scan = systable_beginscan(pg_constraint, ConstraintConfRelidIndexId, true,
+ NULL, 1, key);
while ((tuple = systable_getnext(scan)) != NULL)
{
Form_pg_constraint constrForm = (Form_pg_constraint) GETSTRUCT(tuple);
+ if (constrForm->contype != CONSTRAINT_FOREIGN)
+ continue;
+
/*
* We only need to process constraints that are part of larger ones.
*/
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index e8d27546ed9..47d57f29488 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -185,6 +185,7 @@ DECLARE_UNIQUE_INDEX(pg_constraint_conrelid_contypid_conname_index, 2665, Constr
DECLARE_INDEX(pg_constraint_contypid_index, 2666, ConstraintTypidIndexId, pg_constraint, btree(contypid oid_ops));
DECLARE_UNIQUE_INDEX_PKEY(pg_constraint_oid_index, 2667, ConstraintOidIndexId, pg_constraint, btree(oid oid_ops));
DECLARE_INDEX(pg_constraint_conparentid_index, 2579, ConstraintParentIndexId, pg_constraint, btree(conparentid oid_ops));
+DECLARE_INDEX(pg_constraint_confrelid_index, 9370, ConstraintConfRelidIndexId, pg_constraint, btree(confrelid oid_ops));
MAKE_SYSCACHE(CONSTROID, pg_constraint_oid_index, 16);
From 437770dd62f606610e1563f201bdf1748a890202 Mon Sep 17 00:00:00 2001
From: Manuel Reyes Bravo <[email protected]>
Date: Thu, 15 Oct 2026 23:50:00 -0300
Subject: [PATCH v6 2/2] Test that the confrelid lookups use the new index
The four lookups by confrelid are covered functionally by the existing
tests, but nothing fails if one of them goes back to a sequential scan
of pg_constraint. Check in stats.sql that each of them adds an index
scan of pg_constraint_confrelid_index. The check only asks for the
counter to grow, so concurrent sessions cannot make it fail; reverting
any one of the four call sites to a heap scan flips exactly its column.
---
src/test/regress/expected/stats.out | 66 +++++++++++++++++++++++++++++
src/test/regress/sql/stats.sql | 37 ++++++++++++++++
2 files changed, 103 insertions(+)
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 8b15471248b..4522661e69f 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -2154,4 +2154,70 @@ SELECT fastpath_exceeded > :backend_fastpath_exceeded_before
(1 row)
DROP TABLE part_test;
+-- Test that the lookups of foreign keys by referenced relation use
+-- pg_constraint_confrelid_index. Each statement below must add an index
+-- scan; other sessions can only add more, so this cannot fail spuriously.
+CREATE TABLE confrelid_pk (id int PRIMARY KEY) PARTITION BY LIST (id);
+CREATE TABLE confrelid_fk (pid int REFERENCES confrelid_pk);
+CREATE TABLE confrelid_ul (a int);
+CREATE FUNCTION confrelid_idx_scan() RETURNS bigint LANGUAGE sql AS
+ $$SELECT idx_scan FROM pg_stat_sys_indexes
+ WHERE indexrelname = 'pg_constraint_confrelid_index'$$;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT confrelid_idx_scan() AS confrelid_scans \gset
+-- CloneFkReferenced()
+CREATE TABLE confrelid_pk1 PARTITION OF confrelid_pk FOR VALUES IN (1);
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT confrelid_idx_scan() > :confrelid_scans AS clone_fk_referenced,
+ confrelid_idx_scan() AS confrelid_scans \gset
+-- GetParentedForeignKeyRefs()
+ALTER TABLE confrelid_pk DETACH PARTITION confrelid_pk1;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT confrelid_idx_scan() > :confrelid_scans AS get_parented_fk_refs,
+ confrelid_idx_scan() AS confrelid_scans \gset
+-- heap_truncate_find_FKs()
+TRUNCATE confrelid_pk1;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT confrelid_idx_scan() > :confrelid_scans AS heap_truncate_find_fks,
+ confrelid_idx_scan() AS confrelid_scans \gset
+-- ATPrepChangePersistence()
+ALTER TABLE confrelid_ul SET UNLOGGED;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT confrelid_idx_scan() > :confrelid_scans AS change_persistence \gset
+SELECT :'clone_fk_referenced' AS clone_fk_referenced,
+ :'get_parented_fk_refs' AS get_parented_fk_refs,
+ :'heap_truncate_find_fks' AS heap_truncate_find_fks,
+ :'change_persistence' AS change_persistence;
+ clone_fk_referenced | get_parented_fk_refs | heap_truncate_find_fks | change_persistence
+---------------------+----------------------+------------------------+--------------------
+ t | t | t | t
+(1 row)
+
+DROP TABLE confrelid_fk, confrelid_pk, confrelid_pk1, confrelid_ul;
+DROP FUNCTION confrelid_idx_scan();
-- End of Stats Test
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 674637e172b..5953c5ae5bf 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -1070,4 +1070,41 @@ SELECT fastpath_exceeded > :backend_fastpath_exceeded_before
DROP TABLE part_test;
+-- Test that the lookups of foreign keys by referenced relation use
+-- pg_constraint_confrelid_index. Each statement below must add an index
+-- scan; other sessions can only add more, so this cannot fail spuriously.
+CREATE TABLE confrelid_pk (id int PRIMARY KEY) PARTITION BY LIST (id);
+CREATE TABLE confrelid_fk (pid int REFERENCES confrelid_pk);
+CREATE TABLE confrelid_ul (a int);
+CREATE FUNCTION confrelid_idx_scan() RETURNS bigint LANGUAGE sql AS
+ $$SELECT idx_scan FROM pg_stat_sys_indexes
+ WHERE indexrelname = 'pg_constraint_confrelid_index'$$;
+SELECT pg_stat_force_next_flush();
+SELECT confrelid_idx_scan() AS confrelid_scans \gset
+-- CloneFkReferenced()
+CREATE TABLE confrelid_pk1 PARTITION OF confrelid_pk FOR VALUES IN (1);
+SELECT pg_stat_force_next_flush();
+SELECT confrelid_idx_scan() > :confrelid_scans AS clone_fk_referenced,
+ confrelid_idx_scan() AS confrelid_scans \gset
+-- GetParentedForeignKeyRefs()
+ALTER TABLE confrelid_pk DETACH PARTITION confrelid_pk1;
+SELECT pg_stat_force_next_flush();
+SELECT confrelid_idx_scan() > :confrelid_scans AS get_parented_fk_refs,
+ confrelid_idx_scan() AS confrelid_scans \gset
+-- heap_truncate_find_FKs()
+TRUNCATE confrelid_pk1;
+SELECT pg_stat_force_next_flush();
+SELECT confrelid_idx_scan() > :confrelid_scans AS heap_truncate_find_fks,
+ confrelid_idx_scan() AS confrelid_scans \gset
+-- ATPrepChangePersistence()
+ALTER TABLE confrelid_ul SET UNLOGGED;
+SELECT pg_stat_force_next_flush();
+SELECT confrelid_idx_scan() > :confrelid_scans AS change_persistence \gset
+SELECT :'clone_fk_referenced' AS clone_fk_referenced,
+ :'get_parented_fk_refs' AS get_parented_fk_refs,
+ :'heap_truncate_find_fks' AS heap_truncate_find_fks,
+ :'change_persistence' AS change_persistence;
+DROP TABLE confrelid_fk, confrelid_pk, confrelid_pk1, confrelid_ul;
+DROP FUNCTION confrelid_idx_scan();
+
-- End of Stats Test
CF 7365 v6 -- counts on master dfd7ede529f, without and with v6-0001
Counts only, no times: pg_stat counters and user-space instructions
(perf stat -e instructions:u). Builds: gcc -O2 -g, no cassert. Three
runs per side, alternating order; every run is listed. All numbers
come from repro-counts.sh.txt (attached), run on 26 Oct 2026.
1. pg_upgrade of pg_upgrade_catalog_repro.sql (David's script)
Old cluster built with master; new cluster is master or v6. The
script's arguments are scaled down; max_locks_per_transaction = 4096
on both clusters. Instructions cover pg_upgrade and every process it
starts (both servers, pg_dump, pg_restore). Scan counters are read in
the restored database right after the upgrade.
scale 1/10: build_catalog_bloat(32, 100, 10, 397, 200)
3,200 partitions; 95,756 pg_constraint rows
(n 85,947, p 3,295, c 3,234, f 3,232, u 48)
pg_constraint confrelid_index instructions (billions)
seq_scan idx_scan run 1 run 2 run 3
master 3200 - 187.80 187.54 187.77
v6 0 3200 100.15 100.15 100.15
scale 2/10: build_catalog_bloat(64, 100, 10, 794, 200)
6,400 partitions; 191,316 pg_constraint rows
master 6400 - 556.21 556.20 556.23
v6 0 6400 206.57 206.62 206.60
master / v6: 1.87x at 1/10, 2.69x at 2/10. From 1/10 to 2/10, v6
grows 2.06x and master 2.96x. The full scale (957k rows) was not run.
One seq scan per ATTACH PARTITION on master: 3200 and 6400 are the
partition counts.
Old cluster on 18.6 instead (1/10, one run each): to master, 3200 seq
scans and 378.86 billion instructions; to v6, 0 seq scans, 3200 index
scans and 291.49 billion.
After every v6 upgrade, from master or from 18.6:
bt_index_parent_check(heapallindexed) on pg_constraint_confrelid_index
and bt_index_check(heapallindexed) on all 6 pg_constraint indexes: ok.
2. heap_truncate_find_FKs(): TRUNCATE and ON COMMIT DELETE ROWS
In the 1/10 cluster after the upgrade (95,759 pg_constraint rows).
One single-user backend per workload under perf stat; each workload
runs its statement 100 times, one transaction each. The controls do
the same work without reaching the FK lookup.
seq_scan rows read instr. (millions)
run 1 run 2 run 3
TRUNCATE pk, fk (fk references pk)
master 100 9,575,900 2915.4 2915.4 2915.1
v6 0 0 203.1 203.0 203.0
control: TRUNCATE of a table with no triggers
master 0 0 122.1 121.8 121.7
v6 0 0 122.0 121.5 121.7
INSERT into an ON COMMIT DELETE ROWS temp table with a trigger
master 100 9,575,900 2817.3 2817.3 2817.5
v6 0 0 84.2 84.3 84.5
control: same temp table, no trigger
master 0 0 80.8 80.8 80.7
v6 0 0 80.7 80.7 80.7
Above the control, per statement: TRUNCATE 27.9M instructions on
master, 0.81M on v6; commit 27.4M on master, 0.04M on v6.
3. Write cost of the index: NOT NULL constraints
Fresh initdb, single-user backend: CREATE TABLE of 5 tables x 200
NOT NULL columns, minus the same tables with nullable columns.
instructions for 1000 NOT NULL constraints (millions)
run 1 run 2 run 3
master 151.87 151.93 151.97
v6 159.58 159.54 159.43
About 7.6k more instructions per NOT NULL constraint (+5.0%); 7.5k
to 7.7k when each run is paired with the master run of the same number.
4. From the 15 Oct runs on v4 (same code as v6, other hunk offsets)
WAL when creating constraints: +1% to +4%, about 120 bytes per
pg_constraint row (WAL bytes are exact). Index size: 16 kB per
database after initdb; 6.3-6.8 MB next to a 342 MB pg_constraint with
about 1M constraints. pg_upgrade from master with 820 constraints of
all kinds: amcheck (heapallindexed) clean on all catalog indexes.
5. v6-0002 (regression test), mutation check
Reverting one call site at a time to a heap scan (InvalidOid) makes
stats.sql fail on exactly that site's column:
heap_truncate_find_FKs t t f t
CloneFkReferenced f t t t
ATPrepChangePersistence t t t f
GetParentedForeignKeyRefs t f t t
make check with v6 (cassert): 239/239.
Not measured: wall-clock time of pg_upgrade at any scale.
#!/usr/bin/env bash
# Counts, not times, for the confrelid index (CF 7365), master vs master+v6.
#
# upgrade: David Rowley's pg_upgrade_catalog_repro.sql, at 1/10 of its
# default scale, upgraded with pg_upgrade; pg_constraint scan
# counters of the restored database and user-space instructions
# of pg_upgrade and every process it starts (perf stat).
# truncate: 100 x TRUNCATE of a referenced table, and 100 commits that
# touch an ON COMMIT DELETE ROWS temp table with a trigger,
# each next to a control that skips the FK lookup; run in the
# upgraded cluster, single-user backend under perf stat.
# notnull: instructions to create 1000 NOT NULL constraints, minus the
# same tables with nullable columns, in a fresh cluster.
#
# usage (each stage is independent; N runs = call it N times):
# OLD_BIN=<master>/bin MASTER_BIN=<master>/bin V6_BIN=<v6>/bin \
# REPRO_SQL=<path>/pg_upgrade_catalog_repro.sql WORK=<scratch dir> \
# bash repro-counts.sh.txt setup
# bash repro-counts.sh.txt upgrade master|v6 <run>
# bash repro-counts.sh.txt truncate master|v6 <run> (after "upgrade" of
that side)
# bash repro-counts.sh.txt notnull master|v6 <run>
# Scale: PARENTS=32 PARTS=100 PLAIN=397 NNCOLS=200 (script defaults are
# 323/100/3970/200); the old cluster is built once, with OLD_BIN.
set -euo pipefail
: "${WORK:?}" "${OLD_BIN:?}" "${MASTER_BIN:?}" "${V6_BIN:?}"
PARENTS=${PARENTS:-32} PARTS=${PARTS:-100} PLAIN=${PLAIN:-397}
NNCOLS=${NNCOLS:-200}
PORT=${PORT:-55432}
mkdir -p "$WORK"
side_bin() { case $1 in master) echo "$MASTER_BIN";; v6) echo "$V6_BIN";; *)
exit 2;; esac; }
instr() { awk -F, '$3 ~ /^instructions/ {print $1}' "$1"; }
# one value from a single-user backend's output: val <file> <column>
val() { sed -n "s/^[[:space:]]*[0-9]*: $2 = \"\([^\"]*\)\".*/\1/p" "$1" | tail
-1; }
case ${1:?stage} in
setup)
: "${REPRO_SQL:?}"
n=$(grep -c '^CALL public\.build_catalog_bloat();' "$REPRO_SQL" || true)
[ "$n" = 1 ] || { echo "expected one CALL line in $REPRO_SQL"; exit 1; }
rm -rf "$WORK/old"
"$OLD_BIN/initdb" -D "$WORK/old" -U postgres -A trust >/dev/null
cat >>"$WORK/old/postgresql.conf" <<EOF
max_locks_per_transaction = 4096
fsync = off
synchronous_commit = off
autovacuum = off
listen_addresses = ''
EOF
"$OLD_BIN/pg_ctl" -D "$WORK/old" -l "$WORK/old.log" -w -o "-p $PORT -k $WORK"
start >/dev/null
"$OLD_BIN/createdb" -h "$WORK" -p $PORT -U postgres test
sed "s/^CALL public\.build_catalog_bloat();/CALL
public.build_catalog_bloat($PARENTS, $PARTS, 10, $PLAIN, $NNCOLS);/" \
"$REPRO_SQL" | "$OLD_BIN/psql" -h "$WORK" -p $PORT -U postgres -d test -X
-q \
-v ON_ERROR_STOP=1 -f - >"$WORK/setup.out" 2>&1
echo "old cluster: scale $PARENTS/$PARTS/10/$PLAIN/$NNCOLS"
"$OLD_BIN/psql" -h "$WORK" -p $PORT -U postgres -d test -X -qtA -F' ' -c \
"SELECT coalesce(contype::text, 'all'), count(*) FROM pg_constraint
GROUP BY ROLLUP (contype) ORDER BY 2 DESC" | tr '\n' ' '; echo
"$OLD_BIN/psql" -h "$WORK" -p $PORT -U postgres -d test -X -qtA -c \
"SELECT 'partitions: ' || count(*) FROM pg_class WHERE relpartbound IS NOT
NULL
AND relkind IN ('r', 'p')"
"$OLD_BIN/pg_ctl" -D "$WORK/old" -m fast -w stop >/dev/null
;;
upgrade)
S=${2:?side}; R=${3:?run}; B=$(side_bin "$S"); D=$WORK/new-$S; U=$WORK/up-$S
rm -rf "$D" "$U"; mkdir -p "$U"
"$B/initdb" -D "$D" -U postgres -A trust >/dev/null
echo "max_locks_per_transaction = 4096" >>"$D/postgresql.conf"
cd "$U"
perf stat -x, -e instructions:u -o "$U/perf.csv" -- \
"$B/pg_upgrade" -b "$OLD_BIN" -B "$B" -d "$WORK/old" -D "$D" -U postgres \
--no-sync -s "$U" -p $PORT -P $((PORT + 1)) >"$U/pg_upgrade.out" 2>&1 \
|| { tail -20 "$U/pg_upgrade.out"; exit 1; }
"$B/pg_ctl" -D "$D" -l "$U/server.log" -w -o "-p $PORT -k $U -c
listen_addresses=''" start >/dev/null
q() { "$B/psql" -h "$U" -p $PORT -U postgres -d test -X -qtA -F' ' -c "$1"; }
c=$(q "SELECT seq_scan, seq_tup_read, coalesce(idx_scan, 0) FROM
pg_stat_sys_tables
WHERE relname = 'pg_constraint'")
i=$(q "SELECT coalesce((SELECT idx_scan || ' ' || idx_tup_read FROM
pg_stat_sys_indexes
WHERE indexrelname = 'pg_constraint_confrelid_index'), '- -')")
n=$(q "SELECT count(*) FROM pg_constraint")
a=""
if [ "$S" = v6 ]; then
q "CREATE EXTENSION amcheck" >/dev/null
a=$(q "SELECT bt_index_parent_check('pg_constraint_confrelid_index', true)"
>/dev/null &&
q "SELECT count(*) FROM pg_index i, LATERAL
bt_index_check(i.indexrelid, true)
WHERE i.indrelid = 'pg_constraint'::regclass" | sed
's/^/amcheck_ok_indexes=/')
q "DROP EXTENSION amcheck" >/dev/null
fi
"$B/pg_ctl" -D "$D" -m fast -w stop >/dev/null
set -- $c; set -- "$@" $i
echo "upgrade $S run=$R instructions=$(instr "$U/perf.csv")
pg_constraint_rows=$n" \
"seq_scan=$1 seq_tup_read=$2 idx_scan=$3 confrelid_idx_scan=$4" \
"confrelid_idx_tup_read=$5 $a"
;;
truncate)
S=${2:?side}; R=${3:?run}; B=$(side_bin "$S"); D=$WORK/new-$S; T=$WORK/tr-$S
[ -d "$D" ] || { echo "run upgrade $S first"; exit 1; }
mkdir -p "$T"
sb() { "$B/postgres" --single -j -D "$D" -c stats_fetch_consistency=none
test; }
sb >/dev/null <<'SQL'
DROP TABLE IF EXISTS tr_fk, tr_pk, tr_plain;
CREATE TABLE tr_pk (id int PRIMARY KEY);
CREATE TABLE tr_fk (id int REFERENCES tr_pk);
CREATE TABLE tr_plain (id int);
SQL
# workload <name> <setup sql> <one statement, run 100 times>
workload() {
local f=$T/$1.sql
{ printf 'SELECT pg_stat_force_next_flush();\n\n'
printf 'SELECT 1;\n\n'
printf '%s\n\n' "$2"
printf 'SELECT pg_stat_force_next_flush();\n\n'
printf 'SELECT 1;\n\n'
printf 'SELECT seq_scan AS s0, seq_tup_read AS t0 FROM pg_stat_sys_tables
WHERE relname = %s;\n\n' "'pg_constraint'"
for _ in $(seq 100); do printf '%s\n\n' "$3"; done
printf 'SELECT pg_stat_force_next_flush();\n\n'
printf 'SELECT 1;\n\n'
printf 'SELECT seq_scan AS s1, seq_tup_read AS t1 FROM pg_stat_sys_tables
WHERE relname = %s;\n\n' "'pg_constraint'"
} >"$f"
perf stat -x, -e instructions:u -o "$T/$1.perf" -- \
"$B/postgres" --single -j -D "$D" -c stats_fetch_consistency=none test
<"$f" >"$T/$1.out" 2>&1
echo "$(instr "$T/$1.perf") $(( $(val "$T/$1.out" s1) - $(val "$T/$1.out"
s0) )) $(( $(val "$T/$1.out" t1) - $(val "$T/$1.out" t0) ))"
}
set -- $(workload trunc_fk 'SELECT 1;' 'TRUNCATE tr_pk, tr_fk;') \
$(workload trunc_plain 'SELECT 1;' 'TRUNCATE tr_plain;') \
$(workload commit_trg 'CREATE TEMP TABLE tt (a int) ON COMMIT DELETE
ROWS;
CREATE FUNCTION pg_temp.f() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN RETURN
NEW; END$$;
CREATE TRIGGER t BEFORE INSERT ON tt FOR EACH ROW EXECUTE FUNCTION
pg_temp.f();' 'INSERT INTO tt VALUES (1);') \
$(workload commit_plain 'CREATE TEMP TABLE tt (a int) ON COMMIT DELETE
ROWS;' 'INSERT INTO tt VALUES (1);')
rows=$(sb <<<'SELECT count(*) AS n FROM pg_constraint;' 2>/dev/null | sed -n
's/.*n = "\([0-9]*\)".*/\1/p')
sb >/dev/null <<<'DROP TABLE tr_fk, tr_pk, tr_plain;'
echo "truncate $S run=$R pg_constraint_rows=$rows" \
"x100 TRUNCATE pk,fk: instructions=$1 seq_scan=$2 seq_tup_read=$3;" \
"x100 TRUNCATE plain (control): instructions=$4 seq_scan=$5
seq_tup_read=$6;" \
"x100 commit, on-commit temp w/ trigger: instructions=$7 seq_scan=$8
seq_tup_read=$9;" \
"x100 commit, on-commit temp plain (control): instructions=${10}
seq_scan=${11} seq_tup_read=${12}"
;;
notnull)
S=${2:?side}; R=${3:?run}; B=$(side_bin "$S"); D=$WORK/nn-$S
rm -rf "$D"; "$B/initdb" -D "$D" -U postgres -A trust >/dev/null
# 5 tables x 200 columns, NOT NULL or nullable, in one transaction each
mk() {
local f=$D.$1.sql
for t in 1 2 3 4 5; do
printf 'CREATE TABLE nn%s (%s);\n\n' $t \
"$(seq -s, -f "c%g int $2" 1 200 | sed 's/,$//')"
done >"$f"
perf stat -x, -e instructions:u -o "$D.$1.perf" -- \
"$B/postgres" --single -j -D "$D" postgres <"$f" >"$D.$1.out" 2>&1
"$B/postgres" --single -j -D "$D" postgres >/dev/null 2>&1 \
<<<'DROP TABLE nn1, nn2, nn3, nn4, nn5;'
instr "$D.$1.perf"
}
nn=$(mk notnull 'NOT NULL'); nl=$(mk nullable '')
echo "notnull $S run=$R fresh initdb: 1000 NOT NULL cols instructions=$nn," \
"1000 nullable cols instructions=$nl, difference=$((nn - nl))"
rm -rf "$D" "$D".*
;;
*) echo "stage?"; exit 2;;
esac
