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

On 2026-Oct-26, Manu wrote:

> @@ -3830,16 +3830,28 @@ heap_truncate_find_FKs(List *relationIds)
> List *parent_cons;
> ListCell *cell;
> ScanKeyData key;
> + ScanKeyData rangekey[2];
> + Oid minoid;
> + Oid maxoid;
> Relation fkeyRel;
> SysScanDesc fkeyScan;
> HeapTuple tuple;
> bool restart;
>
> + if (relationIds == NIL)
> + return NIL;
> +
> oids = list_copy(relationIds);
>
> /*
> - * Must scan pg_constraint. Right now, it is a seqscan because there is
> - * no available index on confrelid.
> + * Must scan pg_constraint. We do a single range scan of the index on
> + * confrelid, from the smallest to the largest OID in our list. Only
> + * foreign keys have a nonzero confrelid, so the rows of all other
> + * constraints sort before the range and are never visited; foreign keys
> + * that fall inside the range but reference a relation not in our list are
> + * skipped below. Each pass costs one index probe for a single relation,
> + * and never more than visiting each foreign key once however long the
> + * list is.

I'm unconvinced that this min/max optimization strategy is really very
useful; if you have a table built early in your app development it
initially works very well, but if you add a new FK months later which
lands at the other end of the Oid range, it'll now be very slow.

Can we use bloomfilter.c or something else that doesn't degrade as
badly, instead?

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Maybe there's lots of data loss but the records of data loss are also lost.
(Lincoln Yeoh)