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

Hi David,

On Sat, Oct 24, 2026 at 11:21 PM David Rowley
<dgrowleyml(at)gmail(dot)com> wrote:
> I'm only interested in discussion here, as there are
> obviously trade-offs to adding an index.

On your schema at full scale, pg_upgrade takes 1121 s on master and
about two minutes with the index, from v10 or from your patch alike:
both use it in the same three lookups. The price is on writes, since
every constraint gets an index entry, under zero if it is not a
foreign key: creating 1,000 tables with 100 NOT NULL columns each
takes 4.5% more instructions and 5.3% more WAL.

v10, attached, is just the plain index Tom suggested in [1].
heap_truncate_find_FKs() keeps its seqscan: v9-0002 probed the index
once per relation, and that made a commit with 1,000 ON COMMIT DELETE
ROWS tables 14-24% slower on a fresh database. The stats.sql check
now reads this backend's own counter inside a transaction, so a
parallel test can't hide a revert. catversion is not bumped.
Details and scripts attached.

[1] https://www.postgresql.org/message-id/1298334.1790896057%40sss.pgh.pa.us

Regards,
Manu