Following up on my own question upthread, which I can now answer. Tom Lane <[email protected]> writes: > The fact of the matter is that if you just need to do pg_constraint > lookups by confrelid, you could simply add a non-unique index on that > column, paralleling the one on contypid
I measured that against the breakout prototype, both on current master, and the plain index wins. With about a million constraints (ms, median of 7): ATTACH PARTITION: master 53, index 0.6, breakout 0.6 DETACH PARTITION: master 87, index 1.4, breakout 1.2 So the breakout buys nothing over the index, and it costs more where it differs: every foreign key writes a second row (about 6% more WAL per FK), readers need a second lookup to get conrelid, and in the schemas I had at hand foreign keys are 7-16% of pg_constraint, which makes the breakout larger than the index, not smaller. Moving confrelid out instead would break most client tools: 15 of the 21 I checked query the foreign-key columns of pg_constraint directly. I also misread you earlier: the zero entries were not a concern, as you had already accepted them for contypid. Doing the plain index properly turned up a fourth lookup by confrelid that the patch in CF 7365 had missed: heap_truncate_find_FKs(), whose comment says it seqscans because no index exists. It runs on TRUNCATE and, when an ON COMMIT DELETE ROWS table has a foreign key or trigger, at every commit of a transaction that used temporary tables. With a million constraints: TRUNCATE of a table with a trigger: master 58 ms, index 0.9 ms each such commit: master 34 ms, index 0.07 ms That function takes a list of relations, so it reads the index with one range scan from the smallest to the largest OID in the list; one probe per relation lost to the old seqscan on a fresh catalog with a thousand ON COMMIT DELETE ROWS tables, whereas the range scan costs at most one visit per foreign key in each pass, however long the list. The attached v4 covers all four lookups. It is the patch of CF 7365, whose thread is [1]; I've added this thread to that entry. check-world passes, pg_upgrade from master leaves the index consistent, and differential fuzzing of TRUNCATE and SET UNLOGGED against master shows no difference in behaviour. [1] https://postgr.es/m/trinity-08d3329b-f6b2-4c11-91d7-2198e50bf4fc-1790681922354@trinity-msg-rest-gmx-gmx-live-58cc8f554d-c56lm Thanks, Manu
From ffc8ecbc7fd710e433d9dc028d06dc20c7613c1c Mon Sep 17 00:00:00 2001 From: Manuel Reyes Bravo <[email protected]> Date: Sun, 4 Oct 2026 19:18:35 -0300 Subject: [PATCH v4] 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) - 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. With about a million constraints, each costs some 50-90 ms; with an ON COMMIT DELETE ROWS temporary table that has a foreign key or trigger, every commit of a transaction that used temporary tables pays more than 30 ms. 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] --- 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 0274d892f2e..a44ebe87edf 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -11355,16 +11355,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); @@ -19562,8 +19569,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 : @@ -19571,7 +19578,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))) @@ -22672,16 +22680,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); -- 2.55.0
