Do we need an index on pg_constraint.confrelid?

I received an internal request to look into "slow pg_upgrade", with
the only information being a list of catalog table row estimates and
table sizes. I used AI to create the attached script to
reverse-engineer a schema with catalog tables that size (I ended up
trimming table counts a bit so it wasn't excessively slow).

I discovered that the slow part is the restoring of the objects into
the target instance:

pg_upgrade -d pgdata -D pgdata_new/ -b ~/pg/bin -B ~/pg/bin | ts
'[%Y-%m-%d %H:%M:%S]'

[2026-10-24 19:38:01] Restoring global objects in the new cluster
ok
[2026-10-24 20:18:05] Restoring database schemas in the new cluster
ok (40 mins 4 sec)

Looking at perf top on the new instance, I see:

27.14% postgres [.] XidInMVCCSnapshot
22.07% postgres [.] nocachegetattr
8.93% postgres [.] HeapTupleSatisfiesMVCC.isra.0
7.43% postgres [.] heap_getattr

That's coming from CloneFkReferenced()'s seq scan on pg_constraint.
It's searching for records with the given confrelid, which isn't
indexed.

I did get the time down to 28 mins by doing "VACUUM (FREEZE,
SKIP_DATABASE_STATS) pg_constraint;" every 3 seconds during the
restore to avoid those slow visibility checks. Autovacuum doesn't run
in binary upgrade mode at all. (Perhaps it's safe to run it on catalog
tables as those are not being upgraded from the old instance.)

I then tried out patching Postgres to add an index on
pg_constraint(confrelid). The times I got were:

[2026-10-24 22:18:40] Restoring global objects in the new cluster
ok
[2026-10-24 22:21:13] Restoring database schemas in the new cluster
ok (2 mins 33 secs) 15.7x master.

Given that pg_constraint now stores NOT NULL constraint details, I
feel this problem must be worse than it was previously.

A few schema details from the schema created by the script:

postgres=# select count(*) from pg_class where relpartbound is not null;
count
-------
32300

postgres=# select count(*) from pg_class where relkind = 'p';
count
-------
323

postgres=# select contype,count(*) from pg_constraint group by rollup
(1) order by 2 desc;
contype | count
---------+--------
| 957311
n | 859329
p | 32686
c | 32625
f | 32623
u | 48

I'm told there have been multiple reports of slow pg_upgrades, but I
don't have details on any of the others.

I've included the patch to add the index, but that's only to allow
people to try this for themselves. I'm not seeking reviews on the
patch itself. I'm only interested in discussion here, as there are
obviously trade-offs to adding an index.

David