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

On 2026-Oct-26, Manu wrote:

> + /*
> + * Look this up through the index on confrelid rather than seqscanning all
> + * of pg_constraint. That scan grew expensive once not-null constraints
> + * started to have pg_constraint rows, making its cost scale with the
> + * total number of constraints in the database. Only foreign keys set
> + * confrelid, so filtering on contype in the loop is just
> + * belt-and-suspenders.
> + */
> + scan = systable_beginscan(pg_constraint, ConstraintConfRelidIndexId, true,
> + NULL, 1, key);

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

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"You don't solve a bad join with SELECT DISTINCT" #CupsOfFail
https://twitter.com/connor_mc_d/status/1431240081726115845