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)

On 2026-Sep-29, Manu wrote:

> 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.

If I recall correctly, there are other pg_constraint scans that could
benefit from this index -- GetParentedForeignKeyRefs() at least; maybe
others? I couldn't find anything in a quick grep.

I mentioned the syscache because I think I wanted to add a syscache on
top of such index for some reason. It might well be that I'm
remembering a syscache that I wanted to add on some other column, maybe
even on a different catalog altogether :-)

> 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.

Hmm, I'm not eager to backpatch anything here, I'd rather go with a
master-only solution.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)