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(-)
