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

On 2026-Nov-04, Manu wrote:

> 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.

I have pushed this with a catversion bump, but omitted the new stats.sql
test. (I also renamed the new index.)

I think the case in heap_truncate_check_FKs() could be made faster by
using a ScalarArrayOpExpr (SK_SEARCHARRAY in nbtree), but I think we
don't have any precedent where we manufacture that kind of thing for DDL
use.

Thanks

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"The ability of users to misuse tools is, of course, legendary" (David Steele)
https://postgr.es/m/11b38a96-6ded-4668-b772-40f992132797@pgmasters.net