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

Hi Álvaro,

On Mon, Oct 26, 2026 at 9:40 PM Álvaro Herrera
<alvherre(at)kurilemu(dot)de> wrote:
> This comment is not useful. Other catalog indexscans elsewhere in the
> code base don't explain what happened before the index was created. You
> could just say "...the index on confrelid, which is only set nonzero by
> FK constraints. Filtering on contype is ..."

Done; v9 uses your wording, and GetParentedForeignKeyRefs() now has
the same comment.

On Mon, Oct 26, 2026 at 10:17 PM Álvaro Herrera
<alvherre(at)kurilemu(dot)de> wrote:
> 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?

Agreed. With 100,000 foreign keys between an old table and a new
one, the v7 range scan costs more than master's seqscan (62M vs 26M
instructions per TRUNCATE).

v9 drops the range and probes the index once per relation in the
list, so it only reads the foreign keys that point at the list: that
case goes down to 1.0M, whatever the number of keys in between.
bloomfilter.c wouldn't help here: it only makes the membership test
cheaper, and the range scan would still visit the same rows.

Two side effects. TRUNCATE ... CASCADE now looks up only the
relations added in the previous round, since otherwise every level
would probe the whole list again; there is a new test where the
cascade loops back. And the fixed cost per probe shows in a nearly
empty database: a commit with 1,000 ON COMMIT DELETE ROWS temp tables
is 26% slower than master with 194 constraints, even at about 1,200,
and twice as fast at 10,400. Details in the attached measurements.txt.

I split the patch: 0001 is the index and the lookups that need
nothing else, so it doesn't have to wait on 0002, which is the
heap_truncate_find_FKs change.

Regards,
Manu