Hi Álvaro,
On Mon, Oct 26, 2026 at 9:40 PM Álvaro Herrera
<[email protected]> wrote:
> This comment is not useful. Other catalog indexscans elsewhere in the
> code base don't explain what happened before the index was created. You
> could just say "...the index on confrelid, which is only set nonzero by
> FK constraints. Filtering on contype is ..."
Done; v9 uses your wording, and GetParentedForeignKeyRefs() now has
the same comment.
On Mon, Oct 26, 2026 at 10:17 PM Álvaro Herrera
<[email protected]> wrote:
> I'm unconvinced that this min/max optimization strategy is really very
> useful; if you have a table built early in your app development it
> initially works very well, but if you add a new FK months later which
> lands at the other end of the Oid range, it'll now be very slow.
>
> Can we use bloomfilter.c or something else that doesn't degrade as
> badly, instead?
Agreed. With 100,000 foreign keys between an old table and a new
one, the v7 range scan costs more than master's seqscan (62M vs 26M
instructions per TRUNCATE).
v9 drops the range and probes the index once per relation in the
list, so it only reads the foreign keys that point at the list: that
case goes down to 1.0M, whatever the number of keys in between.
bloomfilter.c wouldn't help here: it only makes the membership test
cheaper, and the range scan would still visit the same rows.
Two side effects. TRUNCATE ... CASCADE now looks up only the
relations added in the previous round, since otherwise every level
would probe the whole list again; there is a new test where the
cascade loops back. And the fixed cost per probe shows in a nearly
empty database: a commit with 1,000 ON COMMIT DELETE ROWS temp tables
is 26% slower than master with 194 constraints, even at about 1,200,
and twice as fast at 10,400. Details in the attached measurements.txt.
I split the patch: 0001 is the index and the lookups that need
nothing else, so it doesn't have to wait on 0002, which is the
heap_truncate_find_FKs change.
Regards,
Manu
From 3fafc04c37206d84ecbaa21fd005b4be27a79bfb Mon Sep 17 00:00:00 2001
From: Manuel Reyes Bravo <[email protected]>
Date: Sun, 4 Oct 2026 19:18:35 -0300
Subject: [PATCH v9 1/2] 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 only asks for the counter to grow, so concurrent sessions
cannot make it fail; for the same reason it can miss a revert while a
test running in parallel uses the same lookup.
heap_truncate_find_FKs() also scans pg_constraint by confrelid, but it
takes a list of relations; it is changed separately.
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/commands/tablecmds.c | 46 ++++++++++++++----------
src/include/catalog/pg_constraint.h | 1 +
src/test/regress/expected/stats.out | 55 +++++++++++++++++++++++++++++
src/test/regress/sql/stats.sql | 31 ++++++++++++++++
4 files changed, 115 insertions(+), 18 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 97e16f3eb47..60207049a35 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. Both columns
+ * are indexed, 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..8cecd41bda8 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -2154,4 +2154,59 @@ 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
+-- 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,
+ :'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..dbd12527e0a 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -1070,4 +1070,35 @@ 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
+-- 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,
+ :'change_persistence' AS change_persistence;
+DROP TABLE confrelid_fk, confrelid_pk, confrelid_pk1, confrelid_ul;
+DROP FUNCTION confrelid_idx_scan();
+
-- End of Stats Test
From d9a1a699113bcb3e20ca66f828ceaf689cf0a6ef Mon Sep 17 00:00:00 2001
From: Manuel Reyes Bravo <[email protected]>
Date: Tue, 27 Oct 2026 01:07:13 -0300
Subject: [PATCH v9 2/2] Use the confrelid index in heap_truncate_find_FKs()
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
heap_truncate_find_FKs(), used by TRUNCATE and by ON COMMIT DELETE ROWS
at commit, still scanned all of pg_constraint. It takes a list of
relations, so probe the index once for each of them: that visits only
the foreign keys that reference the list, however far apart the OIDs
of the relations are. When it climbs to a partitioned parent and
restarts, it only looks up the relations added since the previous pass.
TRUNCATE ... CASCADE called heap_truncate_find_FKs() once per level of
references with its whole, growing list of relations, which with a
probe per relation would look up every relation again at every level.
Pass it only the relations added at the previous level instead; the
referencers of the others are already in the list. Add a test where
the cascade reaches a cycle of foreign keys, the case in which that
loop has to skip relations it already has.
Each probe has a fixed cost, so in a nearly empty database a long list,
such as many ON COMMIT DELETE ROWS temporary tables with triggers, costs
more than the old seqscan.
Also check in stats.sql that the lookup scans the new index.
Reviewed-by: Ãlvaro Herrera <[email protected]>
Discussion: https://postgr.es/m/CAApHDvpxGcb-_ddd+qL+F=vmv4d3wznbuci3wqyj0hcfnc9...@mail.gmail.com
---
src/backend/catalog/heap.c | 69 +++++++++++++++-----------
src/backend/commands/tablecmds.c | 18 +++++--
src/test/regress/expected/stats.out | 17 +++++--
src/test/regress/expected/truncate.out | 26 ++++++++++
src/test/regress/sql/stats.sql | 6 +++
src/test/regress/sql/truncate.sql | 18 +++++++
6 files changed, 117 insertions(+), 37 deletions(-)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index f0a159c1b77..29ce2ec9f35 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -3834,12 +3834,15 @@ heap_truncate_find_FKs(List *relationIds)
SysScanDesc fkeyScan;
HeapTuple tuple;
bool restart;
+ int nprobed = 0;
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. Look up each relation in our list through the
+ * index on confrelid, which is only set nonzero by FK constraints, so we
+ * visit just the foreign keys that reference our list. A restart only
+ * needs to look up the relations added since the previous pass.
*/
fkeyRel = table_open(ConstraintRelationId, AccessShareLock);
@@ -3847,40 +3850,46 @@ restart:
restart = false;
parent_cons = NIL;
- fkeyScan = systable_beginscan(fkeyRel, InvalidOid, false,
- NULL, 0, NULL);
-
- while (HeapTupleIsValid(tuple = systable_getnext(fkeyScan)))
+ for_each_from(cell, oids, nprobed)
{
- Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
+ ScanKeyInit(&key,
+ Anum_pg_constraint_confrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(lfirst_oid(cell)));
- /* Not a foreign key */
- if (con->contype != CONSTRAINT_FOREIGN)
- continue;
+ fkeyScan = systable_beginscan(fkeyRel, ConstraintConfRelidIndexId,
+ true, NULL, 1, &key);
- /* Not referencing one of our list of tables */
- if (!list_member_oid(oids, con->confrelid))
- continue;
+ while (HeapTupleIsValid(tuple = systable_getnext(fkeyScan)))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
- /*
- * If this constraint has a parent constraint which we have not seen
- * yet, keep track of it for the second loop, below. Tracking parent
- * constraints allows us to climb up to the top-level constraint and
- * look for all possible relations referencing the partitioned table.
- */
- if (OidIsValid(con->conparentid) &&
- !list_member_oid(parent_cons, con->conparentid))
- parent_cons = lappend_oid(parent_cons, con->conparentid);
+ /* Not a foreign key */
+ if (con->contype != CONSTRAINT_FOREIGN)
+ continue;
- /*
- * Add referencer to result, unless present in input list. (Don't
- * worry about dupes: we'll fix that below).
- */
- if (!list_member_oid(relationIds, con->conrelid))
- result = lappend_oid(result, con->conrelid);
- }
+ /*
+ * If this constraint has a parent constraint which we have not
+ * seen yet, keep track of it for the second loop, below. Tracking
+ * parent constraints allows us to climb up to the top-level
+ * constraint and look for all possible relations referencing the
+ * partitioned table.
+ */
+ if (OidIsValid(con->conparentid) &&
+ !list_member_oid(parent_cons, con->conparentid))
+ parent_cons = lappend_oid(parent_cons, con->conparentid);
- systable_endscan(fkeyScan);
+ /*
+ * Add referencer to result, unless present in input list. (Don't
+ * worry about dupes: we'll fix that below).
+ */
+ if (!list_member_oid(relationIds, con->conrelid))
+ result = lappend_oid(result, con->conrelid);
+ }
+
+ systable_endscan(fkeyScan);
+ }
+ nprobed = list_length(oids);
/*
* Process each parent constraint we found to add the list of referenced
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 60207049a35..81d568954a8 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -2041,19 +2041,28 @@ ExecuteTruncateGuts(List *explicit_rels,
rels = list_copy(explicit_rels);
if (behavior == DROP_CASCADE)
{
- for (;;)
+ List *pending = list_copy(relids);
+
+ /*
+ * Each iteration only needs the referencers of the rels added by the
+ * previous one; those of older rels were already added.
+ */
+ while (pending != NIL)
{
List *newrelids;
- newrelids = heap_truncate_find_FKs(relids);
- if (newrelids == NIL)
- break; /* nothing else to add */
+ newrelids = heap_truncate_find_FKs(pending);
+ list_free(pending);
+ pending = NIL;
foreach(cell, newrelids)
{
Oid relid = lfirst_oid(cell);
Relation rel;
+ if (list_member_oid(relids, relid))
+ continue;
+
rel = table_open(relid, AccessExclusiveLock);
ereport(NOTICE,
(errmsg("truncate cascades to table \"%s\"",
@@ -2063,6 +2072,7 @@ ExecuteTruncateGuts(List *explicit_rels,
truncate_check_activity(rel);
rels = lappend(rels, rel);
relids = lappend_oid(relids, relid);
+ pending = lappend_oid(pending, relid);
/* Log this relation only if needed for logical decoding */
if (RelationIsLogicallyLogged(rel))
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 8cecd41bda8..acc317b86ea 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -2190,6 +2190,16 @@ 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_pk, confrelid_fk;
+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();
@@ -2201,10 +2211,11 @@ 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;
- clone_fk_referenced | get_parented_fk_refs | change_persistence
----------------------+----------------------+--------------------
- t | t | t
+ 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;
diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.out
index 1e88e867bf2..5d83da6e8a2 100644
--- a/src/test/regress/expected/truncate.out
+++ b/src/test/regress/expected/truncate.out
@@ -138,6 +138,32 @@ SELECT * FROM trunc_e;
(0 rows)
DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE;
+-- TRUNCATE ... CASCADE reaching a cycle of foreign keys: trunc_cyc_b is
+-- found through trunc_cyc_a, and trunc_cyc_a references it back
+CREATE TABLE trunc_cyc_r (id int PRIMARY KEY);
+CREATE TABLE trunc_cyc_a (id int PRIMARY KEY, r int REFERENCES trunc_cyc_r, b int);
+CREATE TABLE trunc_cyc_b (id int PRIMARY KEY, a int REFERENCES trunc_cyc_a);
+ALTER TABLE trunc_cyc_a ADD FOREIGN KEY (b) REFERENCES trunc_cyc_b;
+INSERT INTO trunc_cyc_r VALUES (1);
+INSERT INTO trunc_cyc_a VALUES (1, 1, NULL);
+INSERT INTO trunc_cyc_b VALUES (1, 1);
+UPDATE trunc_cyc_a SET b = 1;
+TRUNCATE TABLE trunc_cyc_r CASCADE;
+NOTICE: truncate cascades to table "trunc_cyc_a"
+NOTICE: truncate cascades to table "trunc_cyc_b"
+SELECT count(*) FROM trunc_cyc_r
+ UNION ALL
+ SELECT count(*) FROM trunc_cyc_a
+ UNION ALL
+ SELECT count(*) FROM trunc_cyc_b;
+ count
+-------
+ 0
+ 0
+ 0
+(3 rows)
+
+DROP TABLE trunc_cyc_r, trunc_cyc_a, trunc_cyc_b;
-- Test TRUNCATE with inheritance
CREATE TABLE trunc_f (col1 integer primary key);
INSERT INTO trunc_f VALUES (1);
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index dbd12527e0a..b627b38f233 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -1091,12 +1091,18 @@ 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_pk, confrelid_fk;
+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();
diff --git a/src/test/regress/sql/truncate.sql b/src/test/regress/sql/truncate.sql
index 54f26e3077a..0a902bd3c84 100644
--- a/src/test/regress/sql/truncate.sql
+++ b/src/test/regress/sql/truncate.sql
@@ -78,6 +78,24 @@ SELECT * FROM trunc_e;
DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE;
+-- TRUNCATE ... CASCADE reaching a cycle of foreign keys: trunc_cyc_b is
+-- found through trunc_cyc_a, and trunc_cyc_a references it back
+CREATE TABLE trunc_cyc_r (id int PRIMARY KEY);
+CREATE TABLE trunc_cyc_a (id int PRIMARY KEY, r int REFERENCES trunc_cyc_r, b int);
+CREATE TABLE trunc_cyc_b (id int PRIMARY KEY, a int REFERENCES trunc_cyc_a);
+ALTER TABLE trunc_cyc_a ADD FOREIGN KEY (b) REFERENCES trunc_cyc_b;
+INSERT INTO trunc_cyc_r VALUES (1);
+INSERT INTO trunc_cyc_a VALUES (1, 1, NULL);
+INSERT INTO trunc_cyc_b VALUES (1, 1);
+UPDATE trunc_cyc_a SET b = 1;
+TRUNCATE TABLE trunc_cyc_r CASCADE;
+SELECT count(*) FROM trunc_cyc_r
+ UNION ALL
+ SELECT count(*) FROM trunc_cyc_a
+ UNION ALL
+ SELECT count(*) FROM trunc_cyc_b;
+DROP TABLE trunc_cyc_r, trunc_cyc_a, trunc_cyc_b;
+
-- Test TRUNCATE with inheritance
CREATE TABLE trunc_f (col1 integer primary key);
CF 7365 v9: heap_truncate_find_FKs() with the index on confrelid
================================================================
Builds, all on master dfd7ede529f, production (-O2), no cassert:
master no index; the lookup is a seqscan of pg_constraint
range v6/v7: one range scan of the index from the lowest to the
highest OID in the list
probe v9 (0001+0002): one index probe per relation in the list
Method: single-user backend under perf stat (user-space instructions),
plus the pg_constraint and pg_constraint_confrelid_index scan counters.
v9 is v8 split in two patches, with the same code once both are
applied; these counts were taken with that code (as v8).
Counts, not times. 3 runs per side in alternating order; median, with
min-max in brackets when the spread matters. Produced by
fk-lookup-counts.sh.txt (attached); section 3 uses the
repro-counts.sh.txt posted with v6.
1. Alvaro's case: an old table and a new one, with many FKs in between
-----------------------------------------------------------------------
An old table and its FK; then K foreign keys from other tables, to
tables created later; then a new table and its FK. 100 x
TRUNCATE old_pk, old_fk, new_pk, new_fk;
"Lookup" = instructions per TRUNCATE minus a TRUNCATE of four tables
without triggers (which skips the lookup). In this schema almost every
pg_constraint row is a foreign key.
K FKs pg_constraint rows read per TRUNCATE lookup, instr.
between rows master range probe master range probe
1,000 1,220 1,220 1,002 2 1.23M 1.54M 0.95M
10,000 10,400 10,400 10,002 2 3.55M 7.09M 1.00M
100,000 102,200 102,200 100,002 2 26.40M 62.04M 1.00M
The range scan reads every FK to a table created between the two, and
here costs more than master's seqscan. The probes read the 2 FKs that
reference the list, whatever K is. amcheck (heapallindexed) is clean on
all 6 pg_constraint indexes after the 100,000 runs with v9.
2. Long lists in a fresh cluster (the case that favours one range scan)
------------------------------------------------------------------------
TRUNCATE of N tables in one statement, each with a trigger, so the
lookup runs and finds nothing; pg_constraint has 194 rows, no FKs.
Instructions per TRUNCATE statement (whole statement):
N master range probe
1 0.963M 0.923M 0.929M
10 3.923M 3.885M 3.947M
100 25.54M 25.52M 26.18M
1000 262.1M [256-265] 262.6M [258-263] 263.4M [263-271]
At N = 100 the probes add about 6,600 instructions per relation, 2.5% of
the statement; TRUNCATE itself spends about 255,000 per relation. At
N = 1000 the run-to-run spread (about 3%) is larger than any difference.
3. The load measured for v6 (1/10 of David's repro, 95,759 constraints)
------------------------------------------------------------------------
100 x TRUNCATE of a referenced table and its FK:
master 2.915G, range 0.203G, probe 0.204G
100 commits touching an ON COMMIT DELETE ROWS temp table with a
trigger:
master 2.818G, range 0.084G, probe 0.084G
4. bloomfilter.c (measurement-only variants of v7, not proposed)
-----------------------------------------------------------------
bloom-range v7's range scan; the filter is built from the list with
bloom_create(list_length(oids), work_mem, 0), and each row
is tested with bloom_lacks_element() before
list_member_oid()
bloom-all the same filter, but the scan key is confrelid > 0 (every
foreign key) instead of the range
Alvaro's case, lookup per TRUNCATE rows read bloom-range bloom-all
K = 1,000 1,002 1.72M 1.72M
K = 10,000 10,002 8.58M 8.57M
K = 100,000 100,002 76.67M 76.65M
(range 1.54M / 7.09M / 62.04M; probe 0.95M / 1.00M / 1.00M)
Load of section 3 bloom-range bloom-all
100 x TRUNCATE of a referenced table 0.206G 0.452G
100 commits, ON COMMIT temp with trigger 0.087G 0.336G
(range 0.203G / 0.084G; probe 0.204G / 0.084G)
With the range, the filter does not change which entries are read, and
bloom_create() zeroes a bitset of at least 1MB on every call
(bloomfilter.c). Without the range, the cost no longer depends on the
OIDs, but every call reads every foreign key in the database: 3,232 of
them in the section 3 cluster.
5. TRUNCATE ... CASCADE down a chain of N tables
-------------------------------------------------
c1 <- c2 <- ... <- cN, each referencing the previous one; 3 x
TRUNCATE c1 CASCADE;
in a fresh cluster. ExecuteTruncateGuts() looks up the referencers once
per level, so there are N lookups. "probe, v8a" is an earlier version
with the same probes but without the change to that loop, which passed
the whole, growing list each time.
Per TRUNCATE:
N rows read instructions
master range probe,v8a probe master range probe,v8a probe
10 2,230 54 54 9 20.5M 20.3M 20.7M 20.3M
100 49,300 5,049 5,049 99 83.9M 75.8M 112.5M 69.5M
1000 3,193,000 500,499 500,499 999 4.07G 2.69G 6.60G 0.65G
Master reads all of pg_constraint at each level; the range scan and the
probes without the loop change read about N^2/2 rows. With it, v9 reads
the N-1 foreign keys once. After each run, one more TRUNCATE ... CASCADE
of the chain with a row in every table leaves 0 rows on every side.
6. ON COMMIT DELETE ROWS with N temp tables, by catalog size
------------------------------------------------------------
N temp tables, each with a trigger; every commit that touches temp
tables truncates all of them and looks up their referencers. Catalog
of the section 1 schema with K FKs (0 = fresh database). Instructions
per commit, from (100 - 10) commits:
pg_constraint N = 100 N = 1000
rows master range probe master range probe
194 3.53M 3.49M 4.20M 33.9M 33.8M 42.6M
1,220 4.28M 3.51M 4.16M 39.1M 33.8M 40.4M
10,400 10.9M 3.50M 4.30M 86.1M 33.9M 42.1M
102,200 76.8M 3.52M 4.36M 557M 33.9M 41.8M
The probes cost about 7,000-9,000 instructions per temp table per
commit, whatever the catalog size. Master's seqscan grows with the
catalog and, through list_member_oid(), with N. So the probes lose
only in a nearly empty database: up to 26% with 194 rows, even at about
1,200 rows, ahead from there on. The range scan is cheapest here
because the temp tables have consecutive OIDs; section 1 is its weak
case. Holding the index lock across the probes (a variant, not
proposed) only saved about 1,300 instructions per probe.
7. Other shapes
---------------
Per statement, fresh cluster, master / range / probe:
TRUNCATE w0 CASCADE, N tables referencing w0
N = 1000 647M / 625M / 654M (spread 605-654)
TRUNCATE pp CASCADE, pp partitioned in 100 partitions, 10 FKs to it
82.0M / 84.8M / 82.7M
TRUNCATE pp_1 CASCADE, same (climbs to pp and restarts)
19.1M / 20.9M / 17.5M
TRUNCATE of 1,000 tables failing on the last one (error path of
heap_truncate_check_FKs(), one more lookup per relation)
637M / 54.9M / 62.6M
Every CASCADE run is followed by one with a row in every table, which
leaves 0 rows on every side.
8. Concurrency, pg_upgrade
---------------------------
- Isolation spec (TRUNCATE ... CASCADE against ALTER TABLE ... ADD
FOREIGN KEY on a relation it reaches): the new FK waits for the
TRUNCATE, and a TRUNCATE that has to wait for the new FK sees it once
it gets the lock and cascades to its table. Same output on master
and v9.
- David's repro at 1/10, upgraded with v9: no seqscan of pg_constraint,
3,200 scans of the index, amcheck clean on all 6 indexes, as with v6.
9. Not covered
--------------
- No wall-clock times.
- With ignore_system_indexes, systable_beginscan() turns each probe into
a heap scan with a key, so a long list costs one seqscan per relation
instead of one in total (from reading genam.c; not measured).
- meson/clang builds were not tried; gcc only.
#!/usr/bin/env bash
# Counts, not times, for heap_truncate_find_FKs() with the confrelid index
# (CF 7365): master (seqscan), v6/v7 (one range scan from the lowest to the
# highest OID in the list) and v8 (one index probe per relation in the list).
# Every number is from a single-user backend under perf stat (user-space
# instructions) plus the pg_constraint / confrelid index scan counters.
#
# spread: Alvaro's case. An old table and its FK, K foreign keys from
# other tables to tables created later, then a new table and its
# FK. 100 x TRUNCATE of the four, next to 100 x TRUNCATE of four
# tables without triggers (that skips the lookup).
# K grows 1000 -> 10000 -> 100000 in the same cluster; the new
# table is dropped and created again at each step, so it always
# has the highest OID.
# list: TRUNCATE of N tables in one statement, each with a trigger (so
# the FK lookup runs, and finds nothing), in a fresh cluster;
# control: the same N tables without triggers.
# cascade: TRUNCATE c1 CASCADE on a chain of N tables, each referencing
# the previous one (N levels of references), in a fresh cluster;
# then a check that it empties all N tables.
#
# usage:
# MASTER_BIN=.. RANGE_BIN=.. PROBE_BIN=.. WORK=<scratch dir> bash
fk-lookup-counts.sh.txt
# spread-setup master|idx <K> (idx: catalog shared by range and
probe)
# spread master|range|probe <K> <run>
# list-setup master|idx <N>
# list master|range|probe <N> <run>
# cascade-setup master|idx <N>
# cascade master|range|probe <N> <run>
set -euo pipefail
: "${WORK:?}" "${MASTER_BIN:?}" "${RANGE_BIN:?}" "${PROBE_BIN:?}"
mkdir -p "$WORK"
bin() { case $1 in master) echo "$MASTER_BIN";; range) echo "$RANGE_BIN";;
probe|idx) echo "$PROBE_BIN";; *) exit 2;; esac; }
cat_of() { case $1 in master) echo master;; *) echo idx;; esac; }
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"
}
COUNTERS="SELECT seq_scan AS SX, seq_tup_read AS TX,
(SELECT idx_tup_read FROM pg_stat_sys_indexes
WHERE indexrelname = 'pg_constraint_confrelid_index') AS IX
FROM pg_stat_sys_tables WHERE relname = 'pg_constraint';"
# measure <bin> <datadir> <out prefix> <reps> <statement>: instructions seq_scan
# seq_tup_read confrelid_idx_tup_read for <reps> runs of <statement>
# optional: $6 = SQL run first in the same session (temp tables);
# 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 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; }
local i0 i1; i0=$(val "$3.out" i0); i1=$(val "$3.out" i1)
echo "$(instr "$3.perf") $(( $(val "$3.out" s1) - $(val "$3.out" s0) ))" \
"$(( $(val "$3.out" t1) - $(val "$3.out" t0) )) $(( ${i1:-0} - ${i0:-0}
))"
}
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-$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 $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-$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 $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-$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 $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;')
# one row in every table, TRUNCATE c1 CASCADE, rows left must be 0
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-$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 $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-$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 $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-$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 $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:?}; B=$(bin "$S"); D=$WORK/oc-$(cat_of "$S");
O=$WORK/oc-$S-$N-$R
mk=$(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 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-$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 $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
