Hello hackers,The view information_schema.constraint_column_usage becomes slow when the number of columns and constraints raise to substantial values. This is because of a join condition that allows only join filter to enforce. The patch is to optimize it. See many_constraints.sql file attached for a performance test: create 3000 tables with 10 columns and a PK each and select * from the view. The last statement works for 22 seconds on master branch, 34 milliseconds optimized on my laptop.
Best Regards, Alexey Bashtanov
many-constraints.sql
Description: application/sql
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 00550eb..ffb1564 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -801,8 +801,8 @@ CREATE VIEW constraint_column_usage AS
WHERE nr.oid = r.relnamespace
AND r.oid = a.attrelid
AND nc.oid = c.connamespace
- AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
- ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
+ AND r.oid = CASE c.contype WHEN 'f' THEN c.confrelid ELSE c.conrelid END
+ AND a.attnum = ANY (CASE c.contype WHEN 'f' THEN c.confkey ELSE c.conkey END)
AND NOT a.attisdropped
AND c.contype IN ('p', 'u', 'f')
AND r.relkind = 'r'
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
