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)

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

Right. v2 attached points three confrelid scans at the index:

- CloneFkReferenced() on ATTACH PARTITION / PARTITION OF
- GetParentedForeignKeyRefs() on DETACH PARTITION
- ATPrepChangePersistence() on ALTER TABLE ... SET UNLOGGED

The third is the "maybe others": grepping Anum_pg_constraint_confrelid
turns up its else branch, which scans confrelid on SET UNLOGGED and
whose own comment already noted it had no usable index -- it does now.
The other two share CloneFkReferenced's shape, so they scan on confrelid
alone and filter contype in the loop.

On a catalog bloated to ~250k rows, each of the three drops from a
pg_constraint seqscan to an index scan (seq_scan delta 1-2 -> 0), and
ATTACH stays at the ~25 ms -> 0.3 ms per partition from before. make
check is clean.

> I mentioned the syscache because I think I wanted to add a syscache on
> top of such index for some reason.

No need for these three -- they all go through systable_beginscan, so a
plain index is enough; I left the syscache out.

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

Works for me -- a catalog change is master-only anyway.

--
Manu