Re: Do we need an index on pg_constraint.confrelid?

Hi David,

On Sat, Oct 24, 2026 at 5:35 PM Álvaro Herrera
<alvherre(at)kurilemu(dot)de> wrote:
> Yeah, we have a patch to add this already; see the thread at
> https://postgr.es/m/trinity-08d3329b-f6b2-4c11-91d7-2198e50bf4fc-1790681922354@trinity-msg-rest-gmx-gmx-live-58cc8f554d-c56lm

That is CF 7365, which started from Bernhard Wonisch's report of
ATTACH PARTITION slowing down as pg_constraint grows. v6 is attached,
rebased on master dfd7ede529f; 0001 has the same code as the version
posted in that thread. It adds a non-unique btree index on confrelid,
like the one on contypid, and uses it in the four places that look up
foreign keys by referenced relation.

Three of them are the ones your patch changes. The fourth is
heap_truncate_find_FKs(), used by TRUNCATE and by ON COMMIT DELETE
ROWS at commit. It takes a list of relations, so it does one range
scan of the index from the lowest to the highest OID in the list
instead of one probe per relation. With 95,759 constraints:
- 100 TRUNCATEs of a referenced table: master does 100 seq scans of
pg_constraint (9.6M rows read) and 2.9G instructions; v6 does no
seq scan and 0.20G.
- 100 commits that touch an ON COMMIT DELETE ROWS temp table with a
trigger: 2.8G instructions on master, 0.084G on v6 (0.081G for the
same table without a trigger, which skips the lookup).

Your repro with v6, at 1/10 of its scale (3,200 partitions, 95,756
constraints), old cluster on master, three upgrades per side:
- master: 3,200 seq scans of pg_constraint in the restored database,
one per ATTACH PARTITION; 187.7G instructions for all of pg_upgrade.
- v6: no seq scan, 3,200 scans of the new index; 100.1G instructions.
At 2/10 it is 556.2G against 206.6G: from 1/10 to 2/10 master grows
2.96x and v6 2.06x. After each v6 upgrade, from master or from 18.6,
amcheck with heapallindexed is clean on all pg_constraint indexes.
I have not re-timed it, so there is no wall-clock number yet.

On the trade-offs:
- Write cost: creating a NOT NULL constraint takes about 7.6k more
instructions, +5%. WAL when creating constraints grows 1-4%,
about 120 bytes per pg_constraint row (measured on 15 Oct).
- Size: 16 kB per database after initdb; 6.3-6.8 MB next to a 342 MB
pg_constraint with about 1M constraints (15 Oct).
- Rows that are not foreign keys go in the index under zero, as with
contypid. None of the four lookups visits them, since they all ask
for a nonzero confrelid.

0001 stands alone. 0002 is optional: a check in stats.sql that each
of the four lookups adds a scan of the new index. Reverting any one
call site to a heap scan fails exactly its own check. The OID (9370)
is in the development range for renumbering at commit, and catversion
is left to the committer.

Regards,
Manu