From e3a58c8835a2cd428b8534dd8df30a7cb96c976b Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Fri, 17 Feb 2017 19:32:15 -0500 Subject: [PATCH] Optimize query for information_schema.constraint_column_usage The way the old query was written prevented some join optimizations because the join conditions were hidden inside a CASE expression. With a large number of constraints, the query became unreasonably slow. The new query performs much better. From: Alexey Bashtanov Reviewed-by: Ashutosh Bapat --- src/backend/catalog/information_schema.sql | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 9a53003ecf..51795cd6de 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 IN ('r', 'P') -- 2.40.0