Hi Bernhard,
Good report. I reproduced it and measured a few things around it.
The slowdown reproduces on 18.6. With your script, ms per ATTACH goes
from 0.07 (empty) to 20.7 at 1M pg_constraint rows, the nullable control
stays flat, and pg_stat_sys_tables shows exactly one pg_constraint
seqscan per ATTACH. My absolute numbers are lower than yours (faster
machine) but the shape is the same.
I also ran your script on 17.11, which you hadn't. It stays flat, 0.06
to 0.26 ms per ATTACH, and pg_constraint stays at ~110 rows the whole
way. The seqscan happens on 17 too, but with almost nothing in
pg_constraint it costs nothing. So this is 14e87ffa5 feeding a scan
that was always there, as you say.
On your option (c), one thing makes it cheaper than it looks: only
foreign-key rows have confrelid <> 0. I checked every contype, and
not-null, check, primary-key and unique constraints all store
confrelid = 0, so a partial index
CREATE INDEX ... ON pg_constraint (confrelid) WHERE confrelid <> 0;
covers only the FK rows, not the not-null rows that dominate the
catalog. On a database with 1,000,198 constraints of which 1 is an FK,
that index is 16 kB, and the lookup CloneFkReferenced() does drops from
a 58 ms seqscan (1M rows filtered) to a 0.03 ms index scan. The partial
predicate also answers the maintenance worry you raised for (c): the
not-null inserts that make up the bulk match confrelid = 0, so they
never touch the index.
One correction on the DETACH path: I could not reproduce a slowdown
there. Detaching 50 partitions with 1M pg_constraint rows stays at
0.06 ms each and adds zero pg_constraint seqscans, so
GetParentedForeignKeyRefs() does not seem to be reached on a plain
DETACH with no foreign key involved. ATTACH is where the cost is.
I'd be glad to put a patch together once there's a sense of the
preferred direction. The partial index is the smallest change, but
whether a new catalog index is the way to go, versus the pg_depend
lookup or the trigger-based early exit, is more your and the
committers' call. Happy to test any of them against the reproducer.
Regards,
Manu
