Re: ATTACH PARTITION cost grows linearly with pg_constraint size (seqscan in CloneFkReferenced), much worse since not-null constraints are in pg_constraint (PG 18)

Hi,

A status note for CF 7365, since the patch ended up split across two
threads.

The current version is v4, posted in the "Partial indexes on system
catalogs" thread [1], after Tom pointed out [2] that a plain non-unique
index on confrelid, like the one on contypid, is all this needs. That
is what v4 does. It also covers a lookup that v3 missed,
heap_truncate_find_FKs(), which runs on TRUNCATE and at every commit
that touches an ON COMMIT DELETE ROWS table with a foreign key or
trigger.

v4 still applies cleanly to master, make check passes, CFbot is green,
and amcheck is clean on the new index after DDL churn. On today's
master with about a million constraints, the affected operations drop
from tens of milliseconds to under two: ATTACH PARTITION, DETACH
PARTITION, CREATE TABLE ... PARTITION OF, TRUNCATE of a table with a
trigger, and ALTER TABLE ... SET UNLOGGED are all around 50-90 ms on
master and under 2 ms with the index, roughly an 80x drop. A commit
loop over an ON COMMIT DELETE ROWS table that has a foreign key goes
from about 3.5 s to a few ms. Creating not-null or foreign-key
constraints costs the same as before, and the index is about 6 MB next
to a 340 MB catalog.

The existing regression tests already run all four call sites
(foreign_key, truncate, temp, alter_table), so I don't think it needs
new tests; it needs a reviewer. Álvaro, since this got slow when
not-null constraints moved into pg_constraint, would you have time to
look at it?

[1] https://postgr.es/m/179115578677.1450953.3979833043111954204@gmail.com
[2] https://postgr.es/m/1298334.1790896057@sss.pgh.pa.us

Regards,
Manu