BUG #12011: information_schema.constraint_column_usage is slow

The following bug has been logged on the website:

Bug reference: 12011
Logged by: Alexey Bashtanov
Email address: bashtanov(at)imap(dot)cc
PostgreSQL version: 9.3.4
Operating system: CentOS Linux 6.5
Description:

Hello!

The view information_schema.constraint_column_usage is slow when there are
lots of columns and lots of constraints (190504 and 16394 respectively in my
database).
The reason is attributes and constraints are joined using a complicated
boolean expression, join filter is used.
I rewritten the SQL to use hash join, performance increased dramatically
(100 times faster).

Patch, old and new plans follow.

Best Regards,
Alexey Bashtanov

=== PATCH ===
diff --git a/src/backend/catalog/information_schema.sql
b/src/backend/catalog/information_schema.sql
index a036c62..897e5c3 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -793,8 +793,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'

== OLD PLAN ===

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=14100.54..2830972.86 rows=22889301 width=320) (actual
time=534.023..96264.643 rows=53529 loops=1)
-> Append (cost=14100.54..2315963.58 rows=22889301 width=320) (actual
time=533.996..96172.468 rows=53529 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=14100.54..14116.92
rows=819 width=320) (actual time=533.995..539.931 rows=14469 loops=1)
-> HashAggregate (cost=14100.54..14108.73 rows=819
width=324) (actual time=533.995..538.259 rows=14469 loops=1)
-> Hash Join (cost=1327.08..14088.26 rows=819
width=324) (actual time=10.962..503.846 rows=28938 loops=1)
Hash Cond: (c.connamespace = nc.oid)
-> Hash Join (cost=1323.11..14073.03 rows=819
width=264) (actual time=10.921..494.087 rows=28938 loops=1)
Hash Cond: (r.relnamespace = nr.oid)
-> Nested Loop (cost=1319.14..14057.80
rows=819 width=204) (actual time=10.894..481.604 rows=28938 loops=1)
-> Nested Loop
(cost=1318.86..13439.51 rows=1115 width=140) (actual time=10.875..331.391
rows=68009 loops=1)
Join Filter: (r.oid =
a.attrelid)
-> Hash Join
(cost=1318.43..8904.01 rows=4747 width=88) (actual time=10.829..95.562
rows=68009 loops=1)
Hash Cond: (d.refobjid =
r.oid)
-> Seq Scan on pg_depend
d (cost=0.00..7301.66 rows=63051 width=12) (actual time=0.780..55.853
rows=68020 loops=1)
Filter:
((refclassid = 1259::oid) AND (classid = 2606::oid))
Rows Removed by
Filter: 243541
-> Hash
(cost=1299.46..1299.46 rows=1518 width=76) (actual time=10.032..10.032
rows=3664 loops=1)
Buckets: 1024
Batches: 1 Memory Usage: 387kB
-> Seq Scan on
pg_class r (cost=0.00..1299.46 rows=1518 width=76) (actual
time=0.224..9.083 rows=3664 loops=1)
Filter:
(pg_has_role(relowner, 'USAGE'::text) AND (relkind = 'r'::"char"))
Rows Removed
by Filter: 12818
-> Index Scan using
pg_attribute_relid_attnum_index on pg_attribute a (cost=0.42..0.94 rows=1
width=70) (actual time=0.003..0.003 rows=1 loops=68009)
Index Cond: ((attrelid =
d.refobjid) AND (attnum = d.refobjsubid))
Filter: (NOT
attisdropped)
-> Index Scan using
pg_constraint_oid_index on pg_constraint c (cost=0.29..0.54 rows=1
width=72) (actual time=0.002..0.002 rows=0 loops=68009)
Index Cond: (oid = d.objid)
Filter: (contype =
'c'::"char")
Rows Removed by Filter: 1
-> Hash (cost=3.43..3.43 rows=43
width=68) (actual time=0.019..0.019 rows=45 loops=1)
Buckets: 1024 Batches: 1 Memory
Usage: 5kB
-> Seq Scan on pg_namespace nr
(cost=0.00..3.43 rows=43 width=68) (actual time=0.002..0.009 rows=45
loops=1)
-> Hash (cost=3.43..3.43 rows=43 width=68)
(actual time=0.033..0.033 rows=45 loops=1)
Buckets: 1024 Batches: 1 Memory Usage:
5kB
-> Seq Scan on pg_namespace nc
(cost=0.00..3.43 rows=43 width=68) (actual time=0.006..0.023 rows=45
loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=1347.24..2301846.66
rows=22888482 width=320) (actual time=25.189..95625.715 rows=39060 loops=1)
-> Nested Loop (cost=1347.24..2072961.84 rows=22888482
width=324) (actual time=25.189..95614.756 rows=39060 loops=1)
Join Filter: CASE WHEN (c_1.contype = 'f'::"char") THEN
((r_1.oid = c_1.confrelid) AND (a_1.attnum = ANY (c_1.confkey))) ELSE
((r_1.oid = c_1.conrelid) AND (a_1.attnum = ANY (c_1.conkey))) END
Rows Removed by Join Filter: 422496768
-> Hash Join (cost=1343.28..9256.07 rows=12935
width=202) (actual time=10.337..352.964 rows=115258 loops=1)
Hash Cond: (a_1.attrelid = r_1.oid)
-> Seq Scan on pg_attribute a_1
(cost=0.00..7139.14 rows=171814 width=70) (actual time=0.019..252.967
rows=190504 loops=1)
Filter: (NOT attisdropped)
-> Hash (cost=1324.30..1324.30 rows=1518
width=136) (actual time=9.539..9.539 rows=3664 loops=1)
Buckets: 1024 Batches: 1 Memory Usage:
602kB
-> Hash Join (cost=3.97..1324.30
rows=1518 width=136) (actual time=0.154..8.385 rows=3664 loops=1)
Hash Cond: (r_1.relnamespace =
nr_1.oid)
-> Seq Scan on pg_class r_1
(cost=0.00..1299.46 rows=1518 width=76) (actual time=0.119..7.352 rows=3664
loops=1)
Filter: (pg_has_role(relowner,
'USAGE'::text) AND (relkind = 'r'::"char"))
Rows Removed by Filter: 12818
-> Hash (cost=3.43..3.43 rows=43
width=68) (actual time=0.024..0.024 rows=45 loops=1)
Buckets: 1024 Batches: 1
Memory Usage: 5kB
-> Seq Scan on pg_namespace
nr_1 (cost=0.00..3.43 rows=43 width=68) (actual time=0.004..0.013 rows=45
loops=1)
-> Materialize (cost=3.97..3751.20 rows=3539
width=187) (actual time=0.000..0.195 rows=3666 loops=115258)
-> Hash Join (cost=3.97..3733.50 rows=3539
width=187) (actual time=0.040..7.604 rows=3666 loops=1)
Hash Cond: (c_1.connamespace = nc_1.oid)
-> Seq Scan on pg_constraint c_1
(cost=0.00..3680.88 rows=3539 width=127) (actual time=0.008..6.201 rows=3666
loops=1)
Filter: (contype = ANY
('{p,u,f}'::"char"[]))
Rows Removed by Filter: 12728
-> Hash (cost=3.43..3.43 rows=43
width=68) (actual time=0.021..0.021 rows=45 loops=1)
Buckets: 1024 Batches: 1 Memory
Usage: 5kB
-> Seq Scan on pg_namespace nc_1
(cost=0.00..3.43 rows=43 width=68) (actual time=0.002..0.009 rows=45
loops=1)
Total runtime: 96271.501 ms
(62 rows)

=== NEW PLAN ===

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=14100.54..21633.07 rows=930 width=320) (actual
time=506.665..718.118 rows=53529 loops=1)
-> Append (cost=14100.54..21612.15 rows=930 width=320) (actual
time=506.648..661.385 rows=53529 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=14100.54..14116.92
rows=819 width=320) (actual time=506.648..512.847 rows=14469 loops=1)
-> HashAggregate (cost=14100.54..14108.73 rows=819
width=324) (actual time=506.647..511.084 rows=14469 loops=1)
-> Hash Join (cost=1327.08..14088.26 rows=819
width=324) (actual time=8.260..477.803 rows=28938 loops=1)
Hash Cond: (c.connamespace = nc.oid)
-> Hash Join (cost=1323.11..14073.03 rows=819
width=264) (actual time=8.226..468.191 rows=28938 loops=1)
Hash Cond: (r.relnamespace = nr.oid)
-> Nested Loop (cost=1319.14..14057.80
rows=819 width=204) (actual time=8.206..456.325 rows=28938 loops=1)
-> Nested Loop
(cost=1318.86..13439.51 rows=1115 width=140) (actual time=8.198..312.402
rows=68009 loops=1)
Join Filter: (r.oid =
a.attrelid)
-> Hash Join
(cost=1318.43..8904.01 rows=4747 width=88) (actual time=8.184..86.227
rows=68009 loops=1)
Hash Cond: (d.refobjid =
r.oid)
-> Seq Scan on pg_depend
d (cost=0.00..7301.66 rows=63051 width=12) (actual time=0.636..49.435
rows=68020 loops=1)
Filter:
((refclassid = 1259::oid) AND (classid = 2606::oid))
Rows Removed by
Filter: 243541
-> Hash
(cost=1299.46..1299.46 rows=1518 width=76) (actual time=7.540..7.540
rows=3664 loops=1)
Buckets: 1024
Batches: 1 Memory Usage: 387kB
-> Seq Scan on
pg_class r (cost=0.00..1299.46 rows=1518 width=76) (actual
time=0.112..6.834 rows=3664 loops=1)
Filter:
(pg_has_role(relowner, 'USAGE'::text) AND (relkind = 'r'::"char"))
Rows Removed
by Filter: 12818
-> Index Scan using
pg_attribute_relid_attnum_index on pg_attribute a (cost=0.42..0.94 rows=1
width=70) (actual time=0.003..0.003 rows=1 loops=68009)
Index Cond: ((attrelid =
d.refobjid) AND (attnum = d.refobjsubid))
Filter: (NOT
attisdropped)
-> Index Scan using
pg_constraint_oid_index on pg_constraint c (cost=0.29..0.54 rows=1
width=72) (actual time=0.002..0.002 rows=0 loops=68009)
Index Cond: (oid = d.objid)
Filter: (contype =
'c'::"char")
Rows Removed by Filter: 1
-> Hash (cost=3.43..3.43 rows=43
width=68) (actual time=0.016..0.016 rows=45 loops=1)
Buckets: 1024 Batches: 1 Memory
Usage: 5kB
-> Seq Scan on pg_namespace nr
(cost=0.00..3.43 rows=43 width=68) (actual time=0.001..0.006 rows=45
loops=1)
-> Hash (cost=3.43..3.43 rows=43 width=68)
(actual time=0.029..0.029 rows=45 loops=1)
Buckets: 1024 Batches: 1 Memory Usage:
5kB
-> Seq Scan on pg_namespace nc
(cost=0.00..3.43 rows=43 width=68) (actual time=0.011..0.020 rows=45
loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=1326.79..7495.23 rows=111
width=320) (actual time=8.141..143.971 rows=39060 loops=1)
-> Hash Join (cost=1326.79..7494.12 rows=111 width=324)
(actual time=8.141..138.438 rows=39060 loops=1)
Hash Cond: (c_1.connamespace = nc_1.oid)
-> Hash Join (cost=1322.82..7488.63 rows=111
width=264) (actual time=8.096..127.856 rows=39060 loops=1)
Hash Cond: (r_1.relnamespace = nr_1.oid)
-> Nested Loop (cost=1318.86..7483.13 rows=111
width=204) (actual time=8.065..116.450 rows=39060 loops=1)
Join Filter: (r_1.oid = a_1.attrelid)
-> Hash Join (cost=1318.43..5019.66
rows=266 width=203) (actual time=8.022..17.054 rows=3666 loops=1)
Hash Cond: (CASE WHEN (c_1.contype =
'f'::"char") THEN c_1.confrelid ELSE c_1.conrelid END = r_1.oid)
-> Seq Scan on pg_constraint c_1
(cost=0.00..3680.88 rows=3539 width=127) (actual time=0.009..5.704 rows=3666
loops=1)
Filter: (contype = ANY
('{p,u,f}'::"char"[]))
Rows Removed by Filter: 12728
-> Hash (cost=1299.46..1299.46
rows=1518 width=76) (actual time=7.999..7.999 rows=3664 loops=1)
Buckets: 1024 Batches: 1
Memory Usage: 387kB
-> Seq Scan on pg_class r_1
(cost=0.00..1299.46 rows=1518 width=76) (actual time=0.114..7.091 rows=3664
loops=1)
Filter:
(pg_has_role(relowner, 'USAGE'::text) AND (relkind = 'r'::"char"))
Rows Removed by Filter:
12818
-> Index Scan using
pg_attribute_relid_attnam_index on pg_attribute a_1 (cost=0.42..9.22 rows=3
width=70) (actual time=0.007..0.024 rows=11 loops=3666)
Index Cond: (attrelid = CASE WHEN
(c_1.contype = 'f'::"char") THEN c_1.confrelid ELSE c_1.conrelid END)
Filter: ((NOT attisdropped) AND
(attnum = ANY (CASE WHEN (c_1.contype = 'f'::"char") THEN c_1.confkey ELSE
c_1.conkey END)))
Rows Removed by Filter: 21
-> Hash (cost=3.43..3.43 rows=43 width=68)
(actual time=0.018..0.018 rows=45 loops=1)
Buckets: 1024 Batches: 1 Memory Usage:
5kB
-> Seq Scan on pg_namespace nr_1
(cost=0.00..3.43 rows=43 width=68) (actual time=0.002..0.010 rows=45
loops=1)
-> Hash (cost=3.43..3.43 rows=43 width=68) (actual
time=0.027..0.027 rows=45 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 5kB
-> Seq Scan on pg_namespace nc_1
(cost=0.00..3.43 rows=43 width=68) (actual time=0.006..0.016 rows=45
loops=1)
Total runtime: 720.907 ms
(62 rows)