postgres.git / summary / log / commit / refs

commit    e3a58c8835a2cd428b8534dd8df30a7cb96c976b
Author:   Peter Eisentraut <peter_e@gmx.net>
Date:     Sat Feb 18 00:32:15 2017 +0000

    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 <bashtanov@imap.cc>
    Reviewed-by: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>


 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 9a53003ecff..51795cd6ded 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')


[parent: 68f3dbc5525a]