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)
