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/[email protected] [2] https://postgr.es/m/[email protected] Regards, Manu
