Optimize query for information_schema.constraint_column_usage

petere · petere · commit e3a58c8835a2 · 2017-02-17T19:32:15.000-05:00
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 &lt;bashtanov@imap.cc&gt;
Reviewed-by: Ashutosh Bapat &lt;ashutosh.bapat@enterprisedb.com&gt;
diff --git 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')
