Re: ATTACH PARTITION cost grows linearly with pg_constraint size (seqscan in CloneFkReferenced), much worse since not-null constraints are in pg_constraint (PG 18)

> 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