> 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
