Incredibly slow queries oninformation_schema.constraint_column_usage? - Mailing list pgsql-general
| From | Viktor Fougstedt |
|---|---|
| Subject | Incredibly slow queries oninformation_schema.constraint_column_usage? |
| Date | |
| Msg-id | 07365773-71E4-46A4-AA10-794094DB07D6@chalmers.se Whole thread Raw |
| Responses |
Re: Incredibly slow queries oninformation_schema.constraint_column_usage?
(Alexey Bashtanov <bashtanov@imap.cc>)
|
| List | pgsql-general |
Hello!
We have a program which auto-generates tables and constraints, run against PostgreSQL 9.5.
On startup, the program checks the current database schema to detect changes that are needed. For one of our database servers, some selects from information_schema are incredibly slow:
select count(*) from information_schema.constraint_column_usage;
count
-------
844
(1 row)
The above query takes 55 seconds to run.
explain select count(*) from information_schema.constraint_column_usage;
Aggregate (cost=6607288.37..6607288.38 rows=1 width=0)
-> Append (cost=66.87..6446680.69 rows=64243070 width=0)
[complete output at the end of this email]
Is the database instance broken somehow, or are these exceptional execution times to be expected? Can I do something differently?
Grateful for any hints.
Regards,
/Viktor
Complete explain plan:
Aggregate (cost=6607288.37..6607288.38 rows=1 width=0)
-> Append (cost=66.87..6446680.69 rows=64243070 width=0)
-> Subquery Scan on "*SELECT* 1" (cost=66.87..66.89 rows=1 width=0)
-> HashAggregate (cost=66.87..66.88 rows=1 width=324)
Group Key: nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
-> Nested Loop (cost=38.06..66.86 rows=1 width=324)
-> Nested Loop (cost=37.80..66.56 rows=1 width=264)
-> Nested Loop (cost=37.38..66.10 rows=1 width=200)
-> Nested Loop (cost=36.96..57.87 rows=1 width=136)
-> Hash Join (cost=36.54..40.97 rows=2 width=132)
Hash Cond: (nc.oid = c.connamespace)
-> Seq Scan on pg_namespace nc (cost=0.00..3.94 rows=94 width=68)
-> Hash (cost=36.51..36.51 rows=2 width=72)
-> Seq Scan on pg_constraint c (cost=0.00..36.51 rows=2 width=72)
Filter: (contype = 'c'::"char")
-> Index Scan using pg_depend_depender_index on pg_depend d (cost=0.42..8.44 rows=1 width=12)
Index Cond: ((classid = '2606'::oid) AND (objid = c.oid))
Filter: (refclassid = '1259'::oid)
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.42..8.21 rows=1 width=70)
Index Cond: ((attrelid = d.refobjid) AND (attnum = d.refobjsubid))
Filter: (NOT attisdropped)
-> Index Scan using pg_class_oid_index on pg_class r (cost=0.41..0.46 rows=1 width=76)
Index Cond: (oid = a.attrelid)
Filter: (pg_has_role(relowner, 'USAGE'::text) AND (relkind = 'r'::"char"))
-> Index Scan using pg_namespace_oid_index on pg_namespace nr (cost=0.27..0.29 rows=1 width=68)
Index Cond: (oid = r.relnamespace)
-> Subquery Scan on "*SELECT* 2" (cost=2512.13..6446613.80 rows=64243069 width=0)
-> Nested Loop (cost=2512.13..5804183.11 rows=64243069 width=0)
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
-> Hash Join (cost=2507.01..22250.59 rows=153142 width=6)
Hash Cond: (a_1.attrelid = r_1.oid)
-> Seq Scan on pg_attribute a_1 (cost=0.00..16389.75 rows=485975 width=6)
Filter: (NOT attisdropped)
-> Hash (cost=2316.95..2316.95 rows=15205 width=4)
-> Hash Join (cost=5.12..2316.95 rows=15205 width=4)
Hash Cond: (r_1.relnamespace = nr_1.oid)
-> Seq Scan on pg_class r_1 (cost=0.00..2102.76 rows=15205 width=8)
Filter: (pg_has_role(relowner, 'USAGE'::text) AND (relkind = 'r'::"char"))
-> Hash (cost=3.94..3.94 rows=94 width=4)
-> Seq Scan on pg_namespace nr_1 (cost=0.00..3.94 rows=94 width=4)
-> Materialize (cost=5.12..58.41 rows=839 width=55)
-> Hash Join (cost=5.12..54.21 rows=839 width=55)
Hash Cond: (c_1.connamespace = nc_1.oid)
-> Seq Scan on pg_constraint c_1 (cost=0.00..37.56 rows=839 width=59)
Filter: (contype = ANY ('{p,u,f}'::"char"[]))
-> Hash (cost=3.94..3.94 rows=94 width=4)
-> Seq Scan on pg_namespace nc_1 (cost=0.00..3.94 rows=94 width=4)
(47 rows)
pgsql-general by date: