> I vaguely recall looking into adding such an index, and finding out > that we don't support partial indexes on catalogs. Is that doable in > some clean way nowadays? (Or maybe the index worked fine, and what > failed was adding a syscache on top of it? Not sure.)
Thanks for the pointer -- it sent me to check, and your recollection holds up. It's the same "no usable index" seqscan from back then; it only stayed cheap while pg_constraint was small, and not-null constraints moving into it for 18 is what surfaced it. On whether a partial index is doable cleanly: still not, and there are two separate walls, not one. Declaration: the bootstrap grammar has no place for a predicate. Boot_DeclareIndexStmt in src/backend/bootstrap/bootparse.y is just "DECLARE INDEX name oid ON table USING am ( params )", no WHERE. genbki does pass the predicate string through into postgres.bki, so the build succeeds, but initdb then fails with a syntax error at that line. Maintenance: even past that, the catalog insert path assumes non-partial. CatalogIndexInsert() in src/backend/catalog/indexing.c: /* * Expressional and partial indexes on system catalogs are not * supported, nor exclusion constraints, nor deferred uniqueness */ Assert(indexInfo->ii_Predicate == NIL); It never evaluates a predicate. Forcing a partial index in with allow_system_table_mods confirms it: after ~1M not-null rows the "WHERE confrelid <> 0" index holds all of them rather than the one FK row, silently on a non-assert build. So a clean partial catalog index would mean teaching both the bootstrap grammar and CatalogIndexInsert to carry and evaluate a predicate. The syscache isn't the blocker here. CloneFkReferenced() scans with systable_beginscan(pg_constraint, InvalidOid, true, ...), not a syscache lookup, so a plain (non-unique) index on confrelid is picked up just by passing its OID in place of InvalidOid; no syscache involved. So I went with a full index on pg_constraint(confrelid), which is declarable today, and pointed the scan at it (one scankey on confrelid, contype filtered in the loop). That's the attached v1. With the catalog grown to ~1M not-null rows, ms per ATTACH goes from about 25 ms (growing linearly) to 0.27 ms and stays flat as the catalog grows; make check is clean. The cost is that a full index also covers every not-null/pk/check row, so it is ~6 MB rather than the ~16 kB a confrelid<>0 partial would be, and adds ~5% to bulk DDL on pg_constraint. That size gap is exactly what makes the partial version attractive, and exactly what can't be declared. Glad to drop it for the trigger-based early-exit instead if you'd rather not add a catalog index; that route also has the advantage of being backpatchable, which a catalog change is not. -- Manu
From d7e94aba8eea2fc5d18a670cfa5e068bdcbcdf26 Mon Sep 17 00:00:00 2001 From: Manu <[email protected]> Date: Tue, 29 Sep 2026 13:01:10 -0300 Subject: [PATCH v1] Index pg_constraint.confrelid to avoid a seqscan in ATTACH PARTITION CloneFkReferenced() collects the foreign keys that reference a partitioned table by looking for pg_constraint rows whose confrelid is the table. pg_constraint had no index on confrelid, so this was a sequential scan of the whole catalog on every ATTACH PARTITION. 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 the scan's cost now grows with the total number of constraints in the database. Attaching partitions to a table in a large schema became noticeably slow as a result. Add a btree index on pg_constraint.confrelid and scan through it in CloneFkReferenced(). Only foreign keys set confrelid, so the scan keys on confrelid alone and filters contype in the loop. With ~1M not-null constraints this drops the per-ATTACH time from ~25 ms to ~0.3 ms and keeps it flat as the catalog grows. Catalog indexes cannot be partial, so the index covers every row, including the confrelid = 0 majority; a confrelid <> 0 partial index would be far smaller but is not supported by the bootstrap and CatalogIndexInsert paths. Reported-by: Bernhard Wonisch <[email protected]> Discussion: https://postgr.es/m/trinity-08d3329b-f6b2-4c11-91d7-2198e50bf4fc-1790681922354@trinity-msg-rest-gmx-gmx-live-58cc8f554d-c56lm --- src/backend/commands/tablecmds.c | 17 +++++++++++------ src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_constraint.h | 1 + 3 files changed, 13 insertions(+), 7 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 0274d892f2e..c246fa475d5 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -11355,16 +11355,21 @@ 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); diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 6f3e526de96..d636e7a7f30 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202609152 +#define CATALOG_VERSION_NO 202609291 #endif 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
