Index pg_constraint.confrelid to avoid seqscans

Since commit 14e87ffa5c54 added pg_constraint rows for not-null
constraints, the pg_constraint seqscans in various operations became
slower, sometimes painfully so; pg_upgrade and ATTACH/DETACH PARTITION
have been reported as particularly problematic.  This new index should
speed up those operations.

Truncating tables involved in FK relationships also have a seqscan of
pg_constraint, but that's not modified by this patch because fixing that
case (heap_truncate_find_FKs()) is not as straightforward.

This new index, like the ones on conrelid and contypid, has a lot of
useless entries where the indexed column is just zeroes, both bloating
the index and slowing down operations that needn't be slowed down.
Someday it might be nice to avoid indexing those, but that's a larger
patch that requires more discussion.

Author: Manuel Reyes Bravo <[email protected]>
Reported-by: Bernhard Wonisch <[email protected]>
Reported-by: David Rowley <[email protected]>
Suggested-by: Tom Lane <[email protected]>
Reviewed-by: Álvaro Herrera <[email protected]>
Discussion: 
https://postgr.es/m/trinity-08d3329b-f6b2-4c11-91d7-2198e50bf4fc-1790681922354@trinity-msg-rest-gmx-gmx-live-58cc8f554d-c56lm
Discussion: https://postgr.es/m/[email protected]
Discussion: 
https://postgr.es/m/CAApHDvpxGcb-_ddd+qL+F=vmv4d3wznbuci3wqyj0hcfnc9...@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/b0496e30830b7cee6d17485404dd4c51d7aea286

Modified Files
--------------
src/backend/catalog/heap.c          |  5 ++--
src/backend/commands/tablecmds.c    | 46 ++++++++++++++++++++++---------------
src/include/catalog/catversion.h    |  2 +-
src/include/catalog/pg_constraint.h |  1 +
4 files changed, 33 insertions(+), 21 deletions(-)

Reply via email to