Hi David,
On Sat, Oct 24, 2026 at 11:21 PM David Rowley
<[email protected]> wrote:
> I'm only interested in discussion here, as there are
> obviously trade-offs to adding an index.
On your schema at full scale, pg_upgrade takes 1121 s on master and
about two minutes with the index, from v10 or from your patch alike:
both use it in the same three lookups. The price is on writes, since
every constraint gets an index entry, under zero if it is not a
foreign key: creating 1,000 tables with 100 NOT NULL columns each
takes 4.5% more instructions and 5.3% more WAL.
v10, attached, is just the plain index Tom suggested in [1].
heap_truncate_find_FKs() keeps its seqscan: v9-0002 probed the index
once per relation, and that made a commit with 1,000 ON COMMIT DELETE
ROWS tables 14-24% slower on a fresh database. The stats.sql check
now reads this backend's own counter inside a transaction, so a
parallel test can't hide a revert. catversion is not bumped.
Details and scripts attached.
[1] https://www.postgresql.org/message-id/1298334.1790896057%40sss.pgh.pa.us
Regards,
Manu
From b5b972a4bb9a6b5383ab5ad3340fd87aa77f7d30 Mon Sep 17 00:00:00 2001
From: Manuel Reyes Bravo <[email protected]>
Date: Sun, 4 Oct 2026 19:18:35 -0300
Subject: [PATCH v10] Index pg_constraint.confrelid to avoid seqscans of the
catalog
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Three places look up the foreign keys that reference one relation by
scanning all of pg_constraint for a matching confrelid:
- CloneFkReferenced() (ATTACH PARTITION, PARTITION OF, and
so every partition pg_upgrade restores)
- GetParentedForeignKeyRefs() (DETACH PARTITION)
- ATPrepChangePersistence() (ALTER TABLE ... SET UNLOGGED)
That was cheap while pg_constraint stayed small. Since not-null
constraints gained pg_constraint rows (commit 14e87ffa5c5), these scans
grow with the total number of constraints in the database, and
pg_upgrade pays one per partition it restores.
Add a non-unique btree index on confrelid, like the existing one on
contypid, and use it in those three places. As with contypid, the rows
of constraints other than foreign keys are indexed under zero, so every
constraint created, including each not-null constraint, pays for one
more index insertion.
Check in stats.sql that each of the three lookups scans the new index.
The check reads this backend's counters for the current transaction,
so the tests running in parallel, which attach and detach partitions
too, cannot hide a revert.
heap_truncate_find_FKs() also scans pg_constraint by confrelid, but for
a whole list of relations. Probing the index once per relation costs
more than one pass over a small catalog, so it keeps its seqscan.
Reported-by: Bernhard Wonisch <[email protected]>
Suggested-by: Tom Lane <[email protected]>
Reviewed-by: Ãlvaro Herrera <[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 | 5 ++--
src/backend/commands/tablecmds.c | 46 ++++++++++++++++++-----------
src/include/catalog/pg_constraint.h | 1 +
src/test/regress/expected/stats.out | 32 ++++++++++++++++++++
src/test/regress/sql/stats.sql | 28 ++++++++++++++++++
5 files changed, 92 insertions(+), 20 deletions(-)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index ce3d3466126..cce8f2d139e 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -3848,8 +3848,9 @@ heap_truncate_find_FKs(List *relationIds)
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. It is a seqscan although confrelid is
+ * indexed, because probing the index once for each relation in the list
+ * costs more than one pass over a small catalog.
*/
fkeyRel = table_open(ConstraintRelationId, AccessShareLock);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 97e16f3eb47..9939d8de5b1 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -11350,7 +11350,7 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
AttrMap *attmap;
ListCell *cell;
SysScanDesc scan;
- ScanKeyData key[2];
+ ScanKeyData key;
HeapTuple tuple;
List *clone = NIL;
Relation trigrel;
@@ -11365,19 +11365,23 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
* scan could return children first.)
*/
pg_constraint = table_open(ConstraintRelationId, RowShareLock);
- ScanKeyInit(&key[0],
+ ScanKeyInit(&key,
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);
+
+ /*
+ * Scan pg_constraint using the index on confrelid, which is only set
+ * nonzero by FK constraints. Filtering on contype in the loop is
+ * therefore just a safety check.
+ */
+ 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 +19579,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. Each column
+ * leads an index, so this also determines which index to use.
*/
ScanKeyInit(&skey[0],
toLogged ? Anum_pg_constraint_conrelid :
@@ -19584,7 +19588,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)))
@@ -22673,7 +22678,7 @@ GetParentedForeignKeyRefs(Relation partition)
Relation pg_constraint;
HeapTuple tuple;
SysScanDesc scan;
- ScanKeyData key[2];
+ ScanKeyData key;
List *constraints = NIL;
/*
@@ -22687,19 +22692,24 @@ GetParentedForeignKeyRefs(Relation partition)
/* Search for constraints referencing this table */
pg_constraint = table_open(ConstraintRelationId, AccessShareLock);
- ScanKeyInit(&key[0],
+ ScanKeyInit(&key,
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);
+ /*
+ * Scan pg_constraint using the index on confrelid, which is only set
+ * nonzero by FK constraints. Filtering on contype in the loop is
+ * therefore just a safety check.
+ */
+ 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);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 8b15471248b..9a7a8c50e76 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -2154,4 +2154,36 @@ 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. The counter is this backend's, for the
+-- current transaction, so other sessions cannot change the result.
+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 pg_stat_get_xact_idx_numscans('pg_constraint_confrelid_index'::regclass)$$;
+BEGIN;
+SELECT confrelid_idx_scan() AS confrelid_scans \gset
+-- CloneFkReferenced()
+CREATE TABLE confrelid_pk1 PARTITION OF confrelid_pk FOR VALUES IN (1);
+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 confrelid_idx_scan() > :confrelid_scans AS get_parented_fk_refs,
+ confrelid_idx_scan() AS confrelid_scans \gset
+-- ATPrepChangePersistence()
+ALTER TABLE confrelid_ul SET UNLOGGED;
+SELECT confrelid_idx_scan() > :confrelid_scans AS change_persistence \gset
+COMMIT;
+SELECT :'clone_fk_referenced' AS clone_fk_referenced,
+ :'get_parented_fk_refs' AS get_parented_fk_refs,
+ :'change_persistence' AS change_persistence;
+ clone_fk_referenced | get_parented_fk_refs | change_persistence
+---------------------+----------------------+--------------------
+ 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..3fbe32274fe 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -1070,4 +1070,32 @@ 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. The counter is this backend's, for the
+-- current transaction, so other sessions cannot change the result.
+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 pg_stat_get_xact_idx_numscans('pg_constraint_confrelid_index'::regclass)$$;
+BEGIN;
+SELECT confrelid_idx_scan() AS confrelid_scans \gset
+-- CloneFkReferenced()
+CREATE TABLE confrelid_pk1 PARTITION OF confrelid_pk FOR VALUES IN (1);
+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 confrelid_idx_scan() > :confrelid_scans AS get_parented_fk_refs,
+ confrelid_idx_scan() AS confrelid_scans \gset
+-- ATPrepChangePersistence()
+ALTER TABLE confrelid_ul SET UNLOGGED;
+SELECT confrelid_idx_scan() > :confrelid_scans AS change_persistence \gset
+COMMIT;
+SELECT :'clone_fk_referenced' AS clone_fk_referenced,
+ :'get_parented_fk_refs' AS get_parented_fk_refs,
+ :'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 v10: measurements
=========================
All on master a054b77e40d, production builds (-O2 -g, no cassert):
master, and master + v10-0001. Counts are user-space instructions under
perf stat, 3 runs per build in rotated order, median. The scripts are
attached (david-repro.sh.txt, ops-counts.sh.txt, fk-lookup-counts.sh.txt).
1. David's schema at full scale
-------------------------------
957,311 constraints (859,329 NOT NULL, 32,686 PK, 32,625 CHECK,
32,623 FK, 48 UNIQUE) over 32,300 partitions; every FK references the
same table, dim_ref, so master's scans read every row and find nothing.
pg_upgrade from master, on a 24-thread laptop in light desktop use:
master, 0001 and the index from David's last message, 3 rounds in
rotated order, then 3 more master runs. A run counts only if the 1-min
load average stayed below 5 (sampled every 30 s; for the last three
runs, read at the end of each run). Three master runs overlapped load
spikes of 12 to 56 and are listed but not counted:
build started restore total load
master 11-03 18:43 1443.5s 1481.3s 56.54 disturbed, not
counted
0001 11-03 19:08 94.2s 116.9s 3.36
David's patch 11-03 19:11 85.5s 118.3s 2.51
0001 11-03 19:13 93.6s 115.7s 2.45
David's patch 11-03 19:15 86.5s 122.1s 3.12
master 11-03 19:17 1085.6s 1120.6s 3.65
David's patch 11-03 19:36 85.9s 119.6s 3.04
master 11-03 19:39 1411.5s 1447.0s 55.23 disturbed, not
counted
0001 11-03 20:04 79.0s 99.7s 1.90
master 11-03 21:21 983.0s 1015.0s 1.27
master 11-03 21:38 1462.5s 1501.7s 12.12 disturbed, not
counted
master 11-03 22:05 1093.3s 1128.8s 3.18
median of the quiet runs:
master n=3 restore 1085.6s total 1120.6s (total range
1015.0-1128.8)
0001 n=3 restore 93.6s total 115.7s (total range 99.7-116.9)
David's patch n=3 restore 85.9s total 119.6s (total range
118.3-122.1)
master / 0001: 11.6x the restore, 9.7x the whole pg_upgrade
The same schema with a plain pg_restore --schema-only into a fresh
cluster, instructions of the whole server:
master rc=0 pg_constraint rows 957,311 server instructions 9.937T
0001 rc=0 pg_constraint rows 957,311 server instructions 0.667T
2. The three lookups, and the writes, by size of pg_constraint
--------------------------------------------------------------
0001 against master, 50 times each operation; nn = David's mix (90% NOT
NULL, 3% FK), fk = almost only FKs. attach = CREATE + ATTACH PARTITION
+ DETACH + DROP of a partition of a referenced table; partof = CREATE
... PARTITION OF + DETACH + DROP; detachc = DETACH ... CONCURRENTLY;
unlogged = SET UNLOGGED and LOGGED of a referenced table; fkadd = ADD +
DROP of a FOREIGN KEY; dropfk = CREATE + DROP of a table with 10 FKs.
mix rows attach partof detachc unlogged fkadd dropfk
nn 205 -2.0% -2.2% -2.4% -1.6% +0.4% +0.4%
fk 10,405 -50.1% -51.8% -49.9% -39.1% +0.4% +0.5%
nn 10,211 -48.6% -50.1% -48.2% -37.4% +0.4% +0.4%
fk 102,205 -90.5% -91.0% -90.4% -85.8% +0.4% +0.4%
nn 100,271 -90.0% -90.6% -89.9% -85.1% +0.4% +0.1%
nn 1,000,871 -99.0% -99.1% -99.0% -98.5% +0.5% +0.5%
100,000 NOT NULL (1,000 tables x 100 columns), 3 runs each, min-max;
churn = 5 x (drop all 1,000 tables, create them again):
instructions master 19.91G-19.91G 0001 20.80G-20.80G +4.5%
WAL bytes master 121.68M-121.68M 0001 128.17M-128.17M +5.3%
sizes pg_constraint 17.47M, confrelid index 0.66M
churn heap 17.47M -> 104.62M, index 0.66M -> 3.88M
On David's 957,311 rows (924,688 with confrelid = 0) a btree on
confrelid is 6.65 MB, 21.55 MB without deduplication; pg_constraint
itself is 184 MB.
3. heap_truncate_find_FKs() is not changed
------------------------------------------
TRUNCATE and ON COMMIT DELETE ROWS keep their seqscan; on a fresh
database (194 rows) 0001 reads the same rows as master and the same
instructions, within noise:
master 0001 rows m/0001, idx
ON COMMIT, N=1, per commit 0.14M 0.14M +0.1% 19400/19400, 0
ON COMMIT, N=100, per commit 3.48M 3.48M -0.0% 19400/19400, 0
ON COMMIT, N=1000, per commit 33.91M 33.91M +0.0% 19400/19400, 0
TRUNCATE N=1, per stmt 0.96M 0.96M +0.2% 19400/19400, 0
TRUNCATE N=10, per stmt 3.92M 3.92M +0.1% 19400/19400, 0
TRUNCATE N=100, per stmt 25.54M 25.51M -0.1% 19400/19400, 0
TRUNCATE N=1000, per stmt 262.16M 263.06M +0.3% 1940/1940, 0
CASCADE chain 10 20.53M 20.63M +0.5% 6690/6690, 0
CASCADE chain 100 83.91M 83.95M +0.0% 147900/147900, 0
CASCADE chain 1000 4.07G 4.07G +0.0% 9579000/9579000, 0
4. Correctness, in short
------------------------
- make check 239/239, cassert, 0 warnings; meson + clang with -Dwerror
and gcc with -Werror clean; headerscheck and cpluspluscheck add no
lines; pgindent clean.
- The stats.sql check reads this backend's counter inside a transaction.
On a build with CloneFkReferenced() back to a heap scan, and another
session attaching and detaching an indexed partition in a loop, the
old check (global idx_scan) said "index used" in 34 of 50 runs, the new
one in 0 of 50.
- Mutants: each lookup back to a heap scan, the key on the wrong column,
the wrong index, the index on another column: all fail make check.
- The whole regression suite with ignore_system_indexes = on, one test
at a time: master and 0001 fail the same tests with the same diffs,
except stats.sql (its index check) and toast OIDs in two warnings.
- isolation: ATTACH, DETACH CONCURRENTLY and TRUNCATE CASCADE against a
concurrent ADD FOREIGN KEY, 6 permutations: same output as master.
- pg_upgrade from 14, 17, 18.6, 19beta3 and master (David's schema at
1/10, plus FKs to partitioned tables and a cycle): pg_dump
--schema-only identical, amcheck clean on all 6 pg_constraint indexes;
from 18 on, the user constraints are identical too (from 14 and 17 the
not-null rows are new, and the clones of FKs onto referenced
partitions get master's names).
#!/usr/bin/env bash
# David Rowley's pg_upgrade case at full scale (his script, his defaults:
# 323 partitioned tables x 100 partitions, 3,970 tables x 200 NOT NULL
# columns), upgraded from master to each build.
#
# setup build the old cluster once, with master's binaries
# upgrade <build> <run> pg_upgrade -b master -B <build>; every output line
# gets a timestamp, like "| ts" in David's mail
#
# OLD_BIN=<master bindir> BINS="master=<dir> v10=<dir> ..." WORK=<dir>
# REPRO_SQL=<path to pg_upgrade_catalog_repro.sql> bash david-repro.sh.txt ...
#
# With VACLOOP=1, the run also does what David tried as a workaround: VACUUM
# (FREEZE, SKIP_DATABASE_STATS) pg_constraint in the new cluster every 3 s
# while pg_upgrade restores the schema.
#
# The restore time is the one David quoted: from the line "Restoring global
# objects in the new cluster ... ok" to the line "Restoring database schemas
# in the new cluster ... ok". pg_upgrade runs with --no-sync, so the fsync of
# the new cluster at the end does not enter the total either.
set -euo pipefail
: "${WORK:?}" "${OLD_BIN:?}" "${BINS:?}"
PORT=${PORT:-61540}
mkdir -p "$WORK"
bin() { local kv; for kv in $BINS; do [ "${kv%%=*}" = "$1" ] && { echo
"${kv#*=}"; return; }; done; exit 2; }
stamp() { perl -MTime::HiRes=time -MPOSIX=strftime -ne \
'BEGIN{$|=1} $t=time; printf "[%s.%03d] %s", strftime("%H:%M:%S", localtime
$t), ($t-int $t)*1000, $_'; }
case ${1:?stage} in
setup)
: "${REPRO_SQL:?}"
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
tr -d '\r' <"$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
q() { "$OLD_BIN/psql" -h "$WORK" -p $PORT -U postgres -d test -X -qtA -F' '
-c "$1"; }
echo "old cluster: $("$OLD_BIN/postgres" --version)"
echo "pg_constraint by contype: $(q "SELECT coalesce(contype::text, 'all'),
count(*) FROM pg_constraint
GROUP BY ROLLUP (contype) ORDER BY 2 DESC" | tr '\n' ' ')"
echo "partitions: $(q "SELECT count(*) FROM pg_class WHERE relpartbound IS
NOT NULL")" \
"partitioned tables: $(q "SELECT count(*) FROM pg_class WHERE relkind =
'p'")"
"$OLD_BIN/pg_ctl" -D "$WORK/old" -m fast -w stop >/dev/null
;;
upgrade)
S=${2:?build}; R=${3:?run}; B=$(bin "$S"); D=$WORK/new; U=$WORK/up-$S-$R
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"
# RSS of the servers pg_upgrade starts (old and new cluster) and their
children only,
# not of other clusters on the machine
( while sleep 5; do
pids=$(for f in "$WORK/old/postmaster.pid" "$D/postmaster.pid"; do
if [ -f "$f" ]; then p=$(head -1 "$f"); echo "$p"; ps --ppid
"$p" -o pid= || :; fi; done)
if [ -n "$pids" ]; then ps -o rss= -p "$(echo $pids | tr ' ' ',')" | awk
'{s+=$1} END{print s+0}' || :; fi
done ) >"$U/rss.log" &
rp=$!
vp=""
if [ -n "${VACLOOP:-}" ]; then
( while sleep 3; do
"$B/psql" -h "$U" -p $((PORT + 1)) -U postgres -d test -X -q \
-c "VACUUM (FREEZE, SKIP_DATABASE_STATS) pg_constraint"
>>"$U/vacloop.log" 2>&1 &&
echo "VACUUM done $(date +%T)" >>"$U/vacloop.log" || :
done ) &
vp=$!
fi
t0=$(date +%s.%N)
"$B/pg_upgrade" -b "$OLD_BIN" -B "$B" -d "$WORK/old" -D "$D" -U postgres \
--no-sync -s "$U" -p $PORT -P $((PORT + 1)) 2>&1 | stamp
>"$U/pg_upgrade.out" || true
t1=$(date +%s.%N)
kill $rp $vp 2>/dev/null || true
grep -q 'Upgrade Complete' "$U/pg_upgrade.out" || { tail -20
"$U/pg_upgrade.out"; exit 1; }
secs() { sed -n "s/^\[\([0-9:.]*\)\] $1.*/\1/p" "$U/pg_upgrade.out" |
awk -F: '{print $1*3600 + $2*60 + $3}'; }
g=$(secs 'Restoring global objects in the new cluster'); s=$(secs 'Restoring
database schemas in the new cluster')
restore=$(awk -v a="$g" -v b="$s" 'BEGIN{d=b-a; if (d<0) d+=86400; printf
"%.1f", d}')
total=$(awk -v a="$t0" -v b="$t1" 'BEGIN{printf "%.1f", b-a}')
"$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"; }
n=$(q "SELECT count(*) FROM pg_constraint")
sz=$(q "SELECT pg_relation_size('pg_constraint') || ' ' ||
coalesce((SELECT pg_relation_size(indexrelid)::text FROM pg_index
WHERE indrelid = 'pg_constraint'::regclass
AND indexrelid::regclass::text =
'pg_constraint_confrelid_index'), '-')")
"$B/pg_ctl" -D "$D" -m fast -w stop >/dev/null
set -- $sz
[ -n "${VACLOOP:-}" ] && S="$S+vacfreeze($(grep -c '^VACUUM done'
"$U/vacloop.log")vacuums)"
echo "upgrade $S run=$R restore_schemas_s=$restore pg_upgrade_total_s=$total"
\
"pg_constraint_rows=$n pg_constraint_bytes=$1 confrelid_index_bytes=$2" \
"peak_postgres_rss_kb=$(sort -n "$U/rss.log" | tail -1)"
;;
*) echo "stage?"; exit 2;;
esac
#!/usr/bin/env bash
# Counts, not times, for the operations that CF 7365 touches or that pay for
# the new index (section 5 of our notes). Single-user backend under perf
# stat (user-space instructions), as in fk-lookup-counts.sh.txt.
#
# BINS="master=<bindir> v10=<bindir>" CATS="master=master v10=idx"
WORK=<dir>
#
# cat-setup <build> <mix> <K> catalog with about K pg_constraint rows:
# mix nn = 90% NOT NULL, 3% FK (David's mix)
# mix fk = almost only FKs
# plus the objects the operations below use
# ops <build> <mix> <K> <run> each operation, 50 times, in that catalog
# write <build> <run> cost of creating 100,000 NOT NULL
# constraints (1,000 tables x 100 columns):
# instructions, WAL bytes, sizes after
# churn <build> create and drop those tables 5 times:
# index size before/after, then VACUUM
set -euo pipefail
: "${WORK:?}" "${BINS:?}" "${CATS:?}"
mkdir -p "$WORK"
lookup() { local k=$1 kv; shift; for kv in $*; do [ "${kv%%=*}" = "$k" ] && {
echo "${kv#*=}"; return; }; done
echo "unknown build $k" >&2; exit 2; }
bin() { lookup "$1" $BINS; }
cat_of() { lookup "$1" $CATS; }
instr() { awk -F, '$3 ~ /^instructions/ {print $1}' "$1"; }
val() { sed -n "s/^[[:space:]]*[0-9]*: $2 = \"\([^\"]*\)\".*/\1/p" "$1" | tail
-1; }
sb() { "$1/postgres" --single -j -D "$2" -c stats_fetch_consistency=none
postgres; }
newdb() {
rm -rf "$1"; "$2/initdb" -D "$1" -U postgres -A trust >/dev/null
printf 'fsync = off\nautovacuum = off\nmax_locks_per_transaction = 1024\n'
>>"$1/postgresql.conf"
}
# run <bin> <datadir> <out> <sql file>: instructions of the whole session
run() { perf stat -x, -e instructions:u -o "$3.perf" -- \
"$1/postgres" --single -j -D "$2" -c stats_fetch_consistency=none
postgres <"$4" >"$3.out" 2>&1
local ne; ne=$(grep -c 'ERROR:' "$3.out" || true)
[ "$ne" = 0 ] || { echo "errors=$ne"; grep -m3 'ERROR:' "$3.out"; exit
1; }
instr "$3.perf"; }
rep() { local n=$1 i; shift; for i in $(seq "$n"); do printf "$@"; printf
'\n\n'; done; }
size_sql="SELECT pg_relation_size('pg_constraint') AS heapb,
coalesce((SELECT pg_relation_size(indexrelid) FROM pg_index WHERE
indexrelid::regclass::text
= 'pg_constraint_confrelid_index'), 0) AS idxb,
(SELECT count(*) FROM pg_constraint) AS nrows;"
case ${1:?stage} in
cat-setup)
C=${2:?}; MIX=${3:?}; K=${4:?}; B=$(bin "$C"); D=$WORK/cat-$(cat_of
"$C")-$MIX-$K
newdb "$D" "$B"
{
# the objects of the operations
printf 'CREATE TABLE pk (id int PRIMARY KEY);\n\n'
printf 'CREATE TABLE pp (id int PRIMARY KEY) PARTITION BY RANGE (id);\n\n'
printf 'CREATE TABLE pp_0 PARTITION OF pp FOR VALUES FROM (0) TO (10);\n\n'
printf 'CREATE TABLE ppref (p int REFERENCES pp);\n\n'
printf 'CREATE TABLE ul (id int PRIMARY KEY);\n\nCREATE TABLE ulref (u int
REFERENCES ul);\n\n'
printf 'CREATE TABLE fkc (id int);\n\n'
# bulk: 100-constraint tables up to K rows
t=$(( K / 100 ))
for i in $(seq "$t"); do
if [ "$MIX" = nn ] && [ $(( i % 30 )) -ne 0 ]; then
printf 'CREATE TABLE b%s (%s);\n\n' "$i" "$(seq -s, -f 'c%g int NOT
NULL' 1 100 | sed 's/,$//')"
else
printf 'CREATE TABLE m%s (id int PRIMARY KEY);\n\n' "$i"
printf 'CREATE TABLE b%s (%s);\n\n' "$i" "$(seq -s, -f "c%g int
REFERENCES m$i" 1 100 | sed 's/,$//')"
fi
done; } | sb "$B" "$D" >/dev/null
printf '%s\n' "$size_sql" | sb "$B" "$D" >"$D.size" 2>/dev/null
echo "cat-setup $(cat_of "$C") mix=$MIX K=$K rows=$(val "$D.size" nrows)" \
"fk=$(sb "$B" "$D" <<<"SELECT count(*) AS f FROM pg_constraint WHERE
contype = 'f';" 2>/dev/null | sed -n 's/.*f = "\([0-9]*\)".*/\1/p')" \
"pg_constraint_bytes=$(val "$D.size" heapb) confrelid_index_bytes=$(val
"$D.size" idxb)"
;;
ops)
S=${2:?}; MIX=${3:?}; K=${4:?}; R=${5:?}; B=$(bin "$S"); D=$WORK/cat-$(cat_of
"$S")-$MIX-$K
O=$WORK/ops-$S-$MIX-$K-$R; mkdir -p "$O"
N=50
# attach: CREATE + ATTACH (CloneFkReferenced) + DETACH
(GetParentedForeignKeyRefs) + DROP
rep $N 'CREATE TABLE pp_x (id int NOT NULL);\n\nALTER TABLE pp ATTACH
PARTITION pp_x FOR VALUES FROM (10) TO (20);\n\nALTER TABLE pp DETACH PARTITION
pp_x;\n\nDROP TABLE pp_x;' >"$O/attach.sql"
rep $N 'CREATE TABLE pp_x (id int NOT NULL);\n\nDROP TABLE pp_x;'
>"$O/attach0.sql"
# partof: CREATE TABLE ... PARTITION OF a referenced table
(CloneFkReferenced),
# DETACH, DROP
rep $N 'CREATE TABLE pp_x PARTITION OF pp FOR VALUES FROM (10) TO
(20);\n\nALTER TABLE pp DETACH PARTITION pp_x;\n\nDROP TABLE pp_x;'
>"$O/partof.sql"
# detachc: ATTACH + DETACH CONCURRENTLY
rep $N 'CREATE TABLE pp_x (id int NOT NULL);\n\nALTER TABLE pp ATTACH
PARTITION pp_x FOR VALUES FROM (10) TO (20);\n\nALTER TABLE pp DETACH PARTITION
pp_x CONCURRENTLY;\n\nDROP TABLE pp_x;' >"$O/detachc.sql"
# unlogged: SET UNLOGGED (confrelid lookup) + SET LOGGED, on a referenced
table
# (ul is referenced by ulref, so both must be switched together)
rep $N 'ALTER TABLE ulref SET UNLOGGED;\n\nALTER TABLE ul SET
UNLOGGED;\n\nALTER TABLE ul SET LOGGED;\n\nALTER TABLE ulref SET LOGGED;'
>"$O/unlogged.sql"
# fkadd: ADD + DROP of a FOREIGN KEY constraint
rep $N 'ALTER TABLE fkc ADD CONSTRAINT fkc_f FOREIGN KEY (id) REFERENCES
pk;\n\nALTER TABLE fkc DROP CONSTRAINT fkc_f;' >"$O/fkadd.sql"
# dropfk: CREATE + DROP of a table with 10 FKs
rep $N 'CREATE TABLE dfk (%s);\n\nDROP TABLE dfk;' "$(seq -s, -f 'c%g int
REFERENCES pk' 1 10 | sed 's/,$//')" >"$O/dropfk.sql"
line="ops $S mix=$MIX K=$K run=$R x$N:"
for op in attach attach0 partof detachc unlogged fkadd dropfk; do
line+=" $op=$(run "$B" "$D" "$O/$op" "$O/$op.sql")"
done
echo "$line"
;;
write)
S=${2:?}; R=${3:?}; B=$(bin "$S"); D=$WORK/write-$S-$R; O=$WORK/wr-$S-$R
newdb "$D" "$B"
{ printf 'SELECT pg_current_wal_insert_lsn() AS l0;\n\n'
for i in $(seq 1000); do
printf 'CREATE TABLE nn%s (%s);\n\n' "$i" "$(seq -s, -f 'c%g int NOT
NULL' 1 100 | sed 's/,$//')"
done
printf 'SELECT pg_current_wal_insert_lsn() AS l1;\n\n%s\n\n' "$size_sql"; }
>"$O.sql"
{ for i in $(seq 1000); do
printf 'CREATE TABLE nn%s (%s);\n\n' "$i" "$(seq -s, -f 'c%g int' 1 100 |
sed 's/,$//')"
done; } >"$O-ctl.sql"
i1=$(run "$B" "$D" "$O" "$O.sql")
wal=$(sb "$B" "$D" <<<"SELECT pg_wal_lsn_diff('$(val "$O.out" l1)', '$(val
"$O.out" l0)')::bigint AS w;" 2>/dev/null | sed -n 's/.*w =
"\([0-9]*\)".*/\1/p')
newdb "$D" "$B"
i0=$(run "$B" "$D" "$O-ctl" "$O-ctl.sql")
echo "write $S run=$R 1000 tables x 100 NOT NULL: instructions=$i1
control(nullable)=$i0" \
"per constraint=$(( (i1 - i0) / 100000 )) wal_bytes=$wal
pg_constraint_rows=$(val "$O.out" nrows)" \
"pg_constraint_bytes=$(val "$O.out" heapb) confrelid_index_bytes=$(val
"$O.out" idxb)"
rm -rf "$D"
;;
churn)
S=${2:?}; B=$(bin "$S"); D=$WORK/churn-$S; O=$WORK/ch-$S
newdb "$D" "$B"
mk() { for i in $(seq 1000); do
printf 'CREATE TABLE nn%s (%s);\n\n' "$i" "$(seq -s, -f 'c%g int NOT
NULL' 1 100 | sed 's/,$//')"; done; }
dr() { for i in $(seq 1000); do printf 'DROP TABLE nn%s;\n\n' "$i"; done; }
mk | sb "$B" "$D" >/dev/null
printf '%s\n' "$size_sql" | sb "$B" "$D" >"$O.0" 2>/dev/null
for c in 1 2 3 4 5; do dr | sb "$B" "$D" >/dev/null; mk | sb "$B" "$D"
>/dev/null; done
printf '%s\n' "$size_sql" | sb "$B" "$D" >"$O.5" 2>/dev/null
printf 'VACUUM pg_constraint;\n\n%s\n' "$size_sql" | sb "$B" "$D" >"$O.v"
2>/dev/null
echo "churn $S 100,000 NOT NULL created, then 5 x (drop all, create all):" \
"start heap=$(val "$O.0" heapb) idx=$(val "$O.0" idxb);" \
"after heap=$(val "$O.5" heapb) idx=$(val "$O.5" idxb);" \
"after VACUUM heap=$(val "$O.v" heapb) idx=$(val "$O.v" idxb)"
;;
*) echo "stage?"; exit 2;;
esac
#!/usr/bin/env bash
# Counts, not times, for the pg_constraint lookups by confrelid (CF 7365).
# Same method as the v9 measurements: a single-user backend under perf stat
# (user-space instructions), plus the pg_constraint seqscan counters and the
# confrelid index counter. Any number of builds, by name:
#
# BINS="master=<bindir> probe=<bindir> any=<bindir>"
# CATS="master=master probe=idx any=idx" (builds that share a catalog)
# WORK=<scratch dir>
# SBOPTS="-c ignore_system_indexes=on" (optional: the no-index path)
#
# spread-setup <cat-build> <K> old table + FK, K FKs between, new table + FK
# spread <build> <K> <run> 100 x TRUNCATE old_pk, old_fk, new_pk, new_fk
# list-setup <cat-build> <N> N tables with a trigger (+ N without)
# list <build> <N> <run> TRUNCATE of the N tables in one statement
# cascade-setup/cascade <N> TRUNCATE c1 CASCADE down a chain of N tables
# wide-setup/wide <N> TRUNCATE w0 CASCADE, N tables reference w0
# part-setup/part <P> <Q> partitioned pp (P parts) referenced by Q
tables
# oncommit-setup <cat-build>
# oncommit <build> <N> <run> [<K>] N ON COMMIT DELETE ROWS temp tables
# with a trigger; with K, in the spread catalog of that K
# errpath-setup/errpath <N> TRUNCATE e1..eN that fails on eN
set -euo pipefail
: "${WORK:?}" "${BINS:?}" "${CATS:?}"
mkdir -p "$WORK"
lookup() { local k=$1 kv; shift; for kv in $*; do [ "${kv%%=*}" = "$k" ] && {
echo "${kv#*=}"; return; }; done
echo "unknown build $k" >&2; exit 2; }
bin() { lookup "$1" $BINS; }
cat_of() { lookup "$1" $CATS; }
instr() { awk -F, '$3 ~ /^instructions/ {print $1}' "$1"; }
val() { sed -n "s/^[[:space:]]*[0-9]*: $2 = \"\([^\"]*\)\".*/\1/p" "$1" | tail
-1; }
sb() { "$1/postgres" --single -j -D "$2" -c stats_fetch_consistency=none
${SBOPTS:-} postgres; }
newdb() {
rm -rf "$1"; "$2/initdb" -D "$1" -U postgres -A trust >/dev/null
printf 'fsync = off\nautovacuum = off\nmax_locks_per_transaction = 1024\n'
>>"$1/postgresql.conf"
}
COUNTERS="SELECT seq_scan AS SX, seq_tup_read AS TX,
coalesce((SELECT idx_tup_read FROM pg_stat_sys_indexes
WHERE indexrelname = 'pg_constraint_confrelid_index'), 0) AS IX
FROM pg_stat_sys_tables WHERE relname = 'pg_constraint';"
# measure <bin> <datadir> <out prefix> <reps> <statement> [<sql first>]:
# prints: instructions seq_scan seq_tup_read confrelid_idx_tup_read
# MEASURE_ERRORS=<n>: each statement is expected to fail, n errors in all
measure() {
local f=$3.sql c0 c1
c0=${COUNTERS//X/0}; c1=${COUNTERS//X/1}
{ [ -n "${6:-}" ] && printf '%s\n\n' "$6"
printf 'SELECT pg_stat_force_next_flush();\n\nSELECT 1;\n\n%s\n\n' "$c0"
for _ in $(seq "$4"); do printf '%s\n\n' "$5"; done
printf 'SELECT pg_stat_force_next_flush();\n\nSELECT 1;\n\n%s\n\n' "$c1"
} >"$f"
perf stat -x, -e instructions:u -o "$3.perf" -- \
"$1/postgres" --single -j -D "$2" -c stats_fetch_consistency=none
${SBOPTS:-} postgres \
<"$f" >"$3.out" 2>&1
local ne; ne=$(grep -c 'ERROR:' "$3.out" || true)
[ "$ne" = "${MEASURE_ERRORS:-0}" ] || { echo "errors=$ne"; grep -m3 'ERROR:'
"$3.out"; exit 1; }
echo "$(instr "$3.perf") $(( $(val "$3.out" s1) - $(val "$3.out" s0) ))" \
"$(( $(val "$3.out" t1) - $(val "$3.out" t0) )) $(( $(val "$3.out" i1) -
$(val "$3.out" i0) ))"
}
fkrows() { sb "$1" "$2" <<<"SELECT count(*) AS n, count(*) FILTER (WHERE
contype = 'f') AS f FROM pg_constraint;" 2>/dev/null |
sed -n 's/.* [nf] = "\([0-9]*\)".*/\1/p' | paste -sd/; }
case ${1:?stage} in
spread-setup)
C=${2:?}; K=${3:?}; B=$(bin "$C"); D=$WORK/spread-$(cat_of "$C")
if [ ! -d "$D" ]; then
newdb "$D" "$B"
sb "$B" "$D" >/dev/null <<'SQL'
CREATE TABLE old_pk (id int PRIMARY KEY);
CREATE TABLE old_fk (id int REFERENCES old_pk);
CREATE TABLE plain1 (id int);
CREATE TABLE plain2 (id int);
CREATE TABLE plain3 (id int);
CREATE TABLE plain4 (id int);
SQL
fi
have=$(sb "$B" "$D" <<<"SELECT count(*) AS n FROM pg_class WHERE relkind =
'r' AND relname LIKE 'mid\_%';" 2>/dev/null |
sed -n 's/.*n = "\([0-9]*\)".*/\1/p')
# one referenced table per 100 foreign keys; each gets 100 FKs from its own
table
for j in $(seq $((have + 1)) $((K / 100))); do
printf 'CREATE TABLE mid_%s (id int PRIMARY KEY);\n\n' "$j"
printf 'CREATE TABLE ref_%s (%s);\n\n' "$j" \
"$(seq -s, -f "c%g int REFERENCES mid_$j" 1 100 | sed 's/,$//')"
done | sb "$B" "$D" >/dev/null
sb "$B" "$D" >/dev/null <<'SQL'
DROP TABLE IF EXISTS new_fk, new_pk;
CREATE TABLE new_pk (id int PRIMARY KEY);
CREATE TABLE new_fk (id int REFERENCES new_pk);
SQL
echo "spread-setup $(cat_of "$C") K=$K pg_constraint rows/fk=$(fkrows "$B"
"$D")"
;;
spread)
S=${2:?}; K=${3:?}; R=${4:?}; B=$(bin "$S"); D=$WORK/spread-$(cat_of "$S");
O=$WORK/sp-$S-$K-$R
set -- $(measure "$B" "$D" "$O-fk" 100 'TRUNCATE old_pk, old_fk, new_pk,
new_fk;') \
$(measure "$B" "$D" "$O-plain" 100 'TRUNCATE plain1, plain2, plain3,
plain4;')
echo "spread $S K=$K run=$R x100 TRUNCATE old+new: instructions=$1
seq_scan=$2" \
"seq_tup_read=$3 confrelid_idx_tup_read=$4; control: instructions=$5;" \
"lookup per TRUNCATE=$(( ($1 - $5) / 100 ))"
;;
list-setup)
C=${2:?}; N=${3:?}; B=$(bin "$C"); D=$WORK/list-$(cat_of "$C")-$N
newdb "$D" "$B"
{ printf 'CREATE FUNCTION f() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN
RETURN NEW; END$$;\n\n'
for i in $(seq "$N"); do
printf 'CREATE TABLE t%s (id int);\n\nCREATE TRIGGER g BEFORE INSERT ON
t%s FOR EACH ROW EXECUTE FUNCTION f();\n\n' "$i" "$i"
printf 'CREATE TABLE p%s (id int);\n\n' "$i"
done; } | sb "$B" "$D" >/dev/null
echo "list-setup $(cat_of "$C") N=$N pg_constraint rows/fk=$(fkrows "$B"
"$D")"
;;
list)
S=${2:?}; N=${3:?}; R=${4:?}; B=$(bin "$S"); D=$WORK/list-$(cat_of "$S")-$N;
O=$WORK/li-$S-$N-$R
reps=100; [ "$N" -ge 1000 ] && reps=10
set -- $(measure "$B" "$D" "$O-trg" $reps "TRUNCATE $(seq -s, -f 't%g' 1 "$N"
| sed 's/,$//');") \
$(measure "$B" "$D" "$O-plain" $reps "TRUNCATE $(seq -s, -f 'p%g' 1
"$N" | sed 's/,$//');")
echo "list $S N=$N run=$R x$reps TRUNCATE: instructions=$1 seq_tup_read=$3" \
"confrelid_idx_tup_read=$4; control: instructions=$5;" \
"per TRUNCATE: total=$(( $1 / reps )) lookup=$(( ($1 - $5) / reps ))"
;;
cascade-setup)
C=${2:?}; N=${3:?}; B=$(bin "$C"); D=$WORK/cascade-$(cat_of "$C")-$N
newdb "$D" "$B"
{ printf 'CREATE TABLE c1 (id int PRIMARY KEY);\n\n'
for i in $(seq 2 "$N"); do
printf 'CREATE TABLE c%s (id int PRIMARY KEY, p int REFERENCES c%s);\n\n'
"$i" $((i - 1))
done; } | sb "$B" "$D" >/dev/null
echo "cascade-setup $(cat_of "$C") N=$N pg_constraint rows/fk=$(fkrows "$B"
"$D")"
;;
cascade)
S=${2:?}; N=${3:?}; R=${4:?}; B=$(bin "$S"); D=$WORK/cascade-$(cat_of
"$S")-$N; O=$WORK/ca-$S-$N-$R
set -- $(measure "$B" "$D" "$O" 3 'TRUNCATE c1 CASCADE;')
left=$({ printf 'INSERT INTO c1 VALUES (1);\n\n'
for i in $(seq 2 "$N"); do printf 'INSERT INTO c%s VALUES (1,
1);\n\n' "$i"; done
printf 'TRUNCATE c1 CASCADE;\n\nSELECT %s AS left_rows;\n\n' \
"$(seq -s+ -f '(SELECT count(*) FROM c%g)' 1 "$N")"; } |
sb "$B" "$D" 2>/dev/null | sed -n 's/.*left_rows =
"\([0-9]*\)".*/\1/p')
echo "cascade $S N=$N run=$R x3 TRUNCATE c1 CASCADE (chain of $N tables):" \
"instructions=$1 seq_scan=$2 seq_tup_read=$3 confrelid_idx_tup_read=$4;"
\
"per TRUNCATE=$(( $1 / 3 )); rows left after one more=$left"
;;
wide-setup)
C=${2:?}; N=${3:?}; B=$(bin "$C"); D=$WORK/wide-$(cat_of "$C")-$N
newdb "$D" "$B"
{ printf 'CREATE TABLE w0 (id int PRIMARY KEY);\n\n'
for i in $(seq "$N"); do
printf 'CREATE TABLE w%s (id int PRIMARY KEY, p int REFERENCES w0);\n\n'
"$i"
done; } | sb "$B" "$D" >/dev/null
echo "wide-setup $(cat_of "$C") N=$N pg_constraint rows/fk=$(fkrows "$B"
"$D")"
;;
wide)
S=${2:?}; N=${3:?}; R=${4:?}; B=$(bin "$S"); D=$WORK/wide-$(cat_of "$S")-$N;
O=$WORK/wi-$S-$N-$R
set -- $(measure "$B" "$D" "$O" 3 'TRUNCATE w0 CASCADE;')
left=$({ printf 'INSERT INTO w0 VALUES (1);\n\n'
for i in $(seq "$N"); do printf 'INSERT INTO w%s VALUES (1, 1);\n\n'
"$i"; done
printf 'TRUNCATE w0 CASCADE;\n\nSELECT %s AS left_rows;\n\n' \
"$(seq -s+ -f '(SELECT count(*) FROM w%g)' 0 "$N")"; } |
sb "$B" "$D" 2>/dev/null | sed -n 's/.*left_rows =
"\([0-9]*\)".*/\1/p')
echo "wide $S N=$N run=$R x3 TRUNCATE w0 CASCADE ($N tables reference w0):" \
"instructions=$1 seq_scan=$2 seq_tup_read=$3 confrelid_idx_tup_read=$4;"
\
"per TRUNCATE=$(( $1 / 3 )); rows left after one more=$left"
;;
part-setup)
# pp partitioned in P partitions, referenced by Q tables: every FK has a
# row per partition with conparentid set, which is what makes
# heap_truncate_find_FKs() climb to the parent and restart
C=${2:?}; P=${3:?}; Q=${4:?}; B=$(bin "$C"); D=$WORK/part-$(cat_of "$C")-$P-$Q
newdb "$D" "$B"
{ printf 'CREATE TABLE pp (id int PRIMARY KEY) PARTITION BY RANGE (id);\n\n'
for i in $(seq "$P"); do
printf 'CREATE TABLE pp_%s PARTITION OF pp FOR VALUES FROM (%s) TO
(%s);\n\n' \
"$i" $(( i * 10 )) $(( i * 10 + 10 ))
done
for j in $(seq "$Q"); do
printf 'CREATE TABLE pr_%s (id int, p int REFERENCES pp);\n\n' "$j"
done; } | sb "$B" "$D" >/dev/null
echo "part-setup $(cat_of "$C") P=$P Q=$Q pg_constraint rows/fk=$(fkrows "$B"
"$D")"
;;
part)
S=${2:?}; P=${3:?}; Q=${4:?}; R=${5:?}; B=$(bin "$S"); D=$WORK/part-$(cat_of
"$S")-$P-$Q
O=$WORK/pa-$S-$P-$Q-$R
set -- $(measure "$B" "$D" "$O-all" 3 'TRUNCATE pp CASCADE;') \
$(measure "$B" "$D" "$O-one" 3 'TRUNCATE pp_1 CASCADE;')
left=$({ printf 'INSERT INTO pp VALUES (10);\n\n'
for j in $(seq "$Q"); do printf 'INSERT INTO pr_%s VALUES (1,
10);\n\n' "$j"; done
printf 'TRUNCATE pp_1 CASCADE;\n\nSELECT %s AS left_rows;\n\n' \
"(SELECT count(*) FROM pp)+$(seq -s+ -f '(SELECT count(*) FROM
pr_%g)' 1 "$Q")"; } |
sb "$B" "$D" 2>/dev/null | sed -n 's/.*left_rows =
"\([0-9]*\)".*/\1/p')
echo "part $S P=$P Q=$Q run=$R x3 TRUNCATE pp CASCADE: instructions=$1
seq_scan=$2" \
"seq_tup_read=$3 confrelid_idx_tup_read=$4; x3 TRUNCATE pp_1 CASCADE:" \
"instructions=$5 seq_scan=$6 seq_tup_read=$7 confrelid_idx_tup_read=$8;"
\
"rows left after one more=$left"
;;
oncommit-setup)
C=${2:?}; B=$(bin "$C"); D=$WORK/oc-$(cat_of "$C")
newdb "$D" "$B"
sb "$B" "$D" >/dev/null <<'SQL'
CREATE FUNCTION f() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN RETURN NEW;
END$$;
SQL
echo "oncommit-setup $(cat_of "$C") pg_constraint rows/fk=$(fkrows "$B" "$D")"
;;
oncommit)
# N ON COMMIT DELETE ROWS temp tables with a trigger, all truncated at
# every commit that touches temp tables; per commit = (100 - 10) commits
S=${2:?}; N=${3:?}; R=${4:?}; K=${5:-}; B=$(bin "$S")
if [ -n "$K" ]; then D=$WORK/spread-$(cat_of "$S"); else D=$WORK/oc-$(cat_of
"$S"); fi
O=$WORK/oc-$S-$N-$R-${K:-0}
mk=$( [ -n "$K" ] && printf 'CREATE OR REPLACE FUNCTION f() RETURNS trigger
LANGUAGE plpgsql AS $$BEGIN RETURN NEW; END$$;\n\n'
for i in $(seq "$N"); do
printf 'CREATE TEMP TABLE tt%s (a int) ON COMMIT DELETE ROWS;\n\n' "$i"
printf 'CREATE TRIGGER g BEFORE INSERT ON tt%s FOR EACH ROW EXECUTE
FUNCTION f();\n\n' "$i"
done)
set -- $(measure "$B" "$D" "$O-100" 100 'INSERT INTO tt1 VALUES (1);' "$mk") \
$(measure "$B" "$D" "$O-10" 10 'INSERT INTO tt1 VALUES (1);' "$mk")
echo "oncommit $S N=$N K=${K:-0} run=$R: x100 instructions=$1 seq_scan=$2
seq_tup_read=$3" \
"confrelid_idx_tup_read=$4; x10 instructions=$5;" \
"per commit=$(( ($1 - $5) / 90 ))"
;;
errpath-setup)
# e1..eN with a trigger each; eN referenced by ex from outside the list
C=${2:?}; N=${3:?}; B=$(bin "$C"); D=$WORK/err-$(cat_of "$C")-$N
newdb "$D" "$B"
{ printf 'CREATE FUNCTION f() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN
RETURN NEW; END$$;\n\n'
for i in $(seq "$N"); do
printf 'CREATE TABLE e%s (id int PRIMARY KEY);\n\nCREATE TRIGGER g BEFORE
INSERT ON e%s FOR EACH ROW EXECUTE FUNCTION f();\n\n' "$i" "$i"
done
printf 'CREATE TABLE ex (id int REFERENCES e%s);\n\n' "$N"; } | sb "$B"
"$D" >/dev/null
echo "errpath-setup $(cat_of "$C") N=$N pg_constraint rows/fk=$(fkrows "$B"
"$D")"
;;
errpath)
# TRUNCATE e1..eN fails on eN, the last one heap_truncate_check_FKs() tries
S=${2:?}; N=${3:?}; R=${4:?}; B=$(bin "$S"); D=$WORK/err-$(cat_of "$S")-$N;
O=$WORK/er-$S-$N-$R
set -- $(MEASURE_ERRORS=10 measure "$B" "$D" "$O" 10 \
"TRUNCATE $(seq -s, -f 'e%g' 1 "$N" | sed 's/,$//');")
echo "errpath $S N=$N run=$R x10 failing TRUNCATE: instructions=$1
seq_scan=$2" \
"seq_tup_read=$3 confrelid_idx_tup_read=$4; per TRUNCATE=$(( $1 / 10 ))"
;;
*) echo "stage?"; exit 2;;
esac
