Re: Very slow INFORMATION_SCHEMA - Mailing list pgsql-performance

Tom Lane schrieb:
> Ernesto <equistango@gmail.com> writes:
>
>> I'm wondering why would this query take about 90 seconds to return 74 rows?
>>
>
> EXPLAIN ANALYZE might tell you something.
>
> Is this really the query you're running?  Because these two columns
> don't exist:
>
>
>> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME,
>> INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME
>>
>
> Leaving those out, I get sub-second runtimes for 70-odd foreign key
> constraints, on much slower hardware than I think you are using.
>
I can confirm this for a quite larger result set (4020 rows) for a DB
with 410 tables and a lot of foreign key constraints.

                                    QUERY PLAN


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
 Sort  (cost=1062.00..1062.00 rows=1 width=192) (actual
time=30341.257..30343.195 rows=4020 loops=1)
   Sort Key: table_constraints.table_name,
((ss.x).n)::information_schema.cardinal_number
   ->  Nested Loop  (cost=889.33..1061.99 rows=1 width=192) (actual
time=308.004..30316.302 rows=4020 loops=1)
         ->  Nested Loop  (cost=889.33..1057.70 rows=1 width=132)
(actual time=307.984..30271.700 rows=4020 loops=1)
               Join Filter: ((table_constraints.constraint_name)::text =
((ss.conname)::information_schema.sql_identifier)::text)
               ->  Subquery Scan table_constraints  (cost=887.99..926.75
rows=1 width=128) (actual time=278.247..293.392 rows=554 loops=1)
                     Filter: (((constraint_schema)::text =
'public'::text) AND ((constraint_type)::text = 'FOREIGN KEY'::text))
                     ->  Unique  (cost=887.99..912.21 rows=969
width=259) (actual time=276.915..288.848 rows=4842 loops=1)
                           ->  Sort  (cost=887.99..890.41 rows=969
width=259) (actual time=276.911..279.536 rows=4842 loops=1)
                                 Sort Key: constraint_catalog,
constraint_schema, constraint_name, table_catalog, table_schema,
table_name, constraint_type, is_deferr
able, initially_deferred
                                 ->  Append  (cost=118.46..839.92
rows=969 width=259) (actual time=1.971..48.601 rows=4842 loops=1)
                                       ->  Subquery Scan "*SELECT* 1"
(cost=118.46..238.72 rows=224 width=259) (actual time=1.970..14.931
rows=1722 loops=1)
                                             ->  Hash Join
(cost=118.46..236.48 rows=224 width=259) (actual time=1.968..12.556
rows=1722 loops=1)
                                                   Hash Cond:
(c.connamespace = nc.oid)
                                                   ->  Hash Join
(cost=116.97..227.42 rows=224 width=199) (actual time=1.902..8.206
rows=1722 loops=1)
                                                         Hash Cond:
(r.relnamespace = nr.oid)
                                                         ->  Hash Join
(cost=115.50..222.49 rows=329 width=139) (actual time=1.839..5.760
rows=1722 loops=1)
                                                               Hash
Cond: (c.conrelid = r.oid)
                                                               ->  Seq
Scan on pg_constraint c  (cost=0.00..97.23 rows=1723 width=75) (actual
time=0.004..1.195 rows=1
723 loops=1)
                                                               ->  Hash
(cost=110.28..110.28 rows=418 width=72) (actual time=1.823..1.823
rows=458 loops=1)
                                                                     ->
Seq Scan on pg_class r  (cost=0.00..110.28 rows=418 width=72) (actual
time=0.012..1.437 rows=
458 loops=1)

Filter: ((relkind = 'r'::"char") AND (pg_has_role(relowner,
'USAGE'::text) OR has_table_pri
vilege(oid, 'INSERT'::text) OR has_table_privilege(oid, 'UPDATE'::text)
OR has_table_privilege(oid, 'DELETE'::text) OR has_table_privilege(oid,
'REFERENCES'::text) OR
 has_table_privilege(oid, 'TRIGGER'::text)))
                                                         ->  Hash
(cost=1.27..1.27 rows=15 width=68) (actual time=0.051..0.051 rows=15
loops=1)
                                                               ->  Seq
Scan on pg_namespace nr  (cost=0.00..1.27 rows=15 width=68) (actual
time=0.010..0.032 rows=15 l
oops=1)

Filter: (NOT pg_is_other_temp_schema(oid))
                                                   ->  Hash
(cost=1.22..1.22 rows=22 width=68) (actual time=0.049..0.049 rows=22
loops=1)
                                                         ->  Seq Scan on
pg_namespace nc  (cost=0.00..1.22 rows=22 width=68) (actual
time=0.008..0.022 rows=22 loops=1
)
                                       ->  Subquery Scan "*SELECT* 2"
(cost=125.45..601.21 rows=745 width=138) (actual time=2.621..29.380
rows=3120 loops=1)
                                             ->  Hash Join
(cost=125.45..593.76 rows=745 width=138) (actual time=2.618..24.938
rows=3120 loops=1)
                                                   Hash Cond:
(a.attrelid = r.oid)
                                                   ->  Seq Scan on
pg_attribute a  (cost=0.00..419.55 rows=5551 width=6) (actual
time=0.009..8.111 rows=3399 loops=1)
                                                         Filter:
(attnotnull AND (attnum > 0) AND (NOT attisdropped))
                                                   ->  Hash
(cost=121.78..121.78 rows=294 width=136) (actual time=2.578..2.578
rows=458 loops=1)
                                                         ->  Hash Join
(cost=1.46..121.78 rows=294 width=136) (actual time=0.073..2.085
rows=458 loops=1)
                                                               Hash
Cond: (r.relnamespace = nr.oid)
                                                               ->  Seq
Scan on pg_class r  (cost=0.00..115.76 rows=431 width=72) (actual
time=0.011..1.358 rows=458 lo
ops=1)

Filter: ((relkind = 'r'::"char") AND (pg_has_role(relowner,
'USAGE'::text) OR has_table_privilege
(oid, 'SELECT'::text) OR has_table_privilege(oid, 'INSERT'::text) OR
has_table_privilege(oid, 'UPDATE'::text) OR has_table_privilege(oid,
'DELETE'::text) OR has_table
_privilege(oid, 'REFERENCES'::text) OR has_table_privilege(oid,
'TRIGGER'::text)))
                                                               ->  Hash
(cost=1.27..1.27 rows=15 width=68) (actual time=0.051..0.051 rows=15
loops=1)

                                                                     ->
Seq Scan on pg_namespace nr  (cost=0.00..1.27 rows=15 width=68) (actual
time=0.010..0.033 row
s=15 loops=1)

Filter: (NOT pg_is_other_temp_schema(oid))
               ->  Nested Loop  (cost=1.34..130.80 rows=6 width=321)
(actual time=0.040..52.244 rows=1845 loops=554)
                     ->  Nested Loop  (cost=1.34..128.42 rows=8
width=261) (actual time=0.017..16.949 rows=1251 loops=554)
                           Join Filter: (pg_has_role(r.relowner,
'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT'::text) OR
has_table_privilege(c.oid, 'INSERT'::
text) OR has_table_privilege(c.oid, 'UPDATE'::text) OR
has_table_privilege(c.oid, 'REFERENCES'::text))
                           ->  Hash Join  (cost=1.34..109.27 rows=46
width=193) (actual time=0.009..5.149 rows=1251 loops=554)
                                 Hash Cond: (c.connamespace = nc.oid)
                                 ->  Seq Scan on pg_constraint c
(cost=0.00..103.69 rows=1008 width=133) (actual time=0.007..2.765
rows=1302 loops=554)
                                       Filter: (contype = ANY
('{p,u,f}'::"char"[]))
                                 ->  Hash  (cost=1.33..1.33 rows=1
width=68) (actual time=0.022..0.022 rows=1 loops=1)
                                       ->  Seq Scan on pg_namespace nc
(cost=0.00..1.33 rows=1 width=68) (actual time=0.016..0.019 rows=1 loops=1)
                                             Filter: ('public'::text =
((nspname)::information_schema.sql_identifier)::text)
                           ->  Index Scan using pg_class_oid_index on
pg_class r  (cost=0.00..0.39 rows=1 width=76) (actual time=0.005..0.006
rows=1 loops=693054)
                                 Index Cond: (r.oid = c.conrelid)
                                 Filter: (relkind = 'r'::"char")
                     ->  Index Scan using pg_namespace_oid_index on
pg_namespace nr  (cost=0.00..0.28 rows=1 width=68) (actual
time=0.004..0.005 rows=1 loops=693054)
                           Index Cond: (nr.oid = r.relnamespace)
                           Filter: (NOT pg_is_other_temp_schema(oid))
         ->  Index Scan using pg_attribute_relid_attnum_index on
pg_attribute a  (cost=0.00..4.27 rows=1 width=70) (actual
time=0.006..0.007 rows=1 loops=4020)
               Index Cond: ((ss.roid = a.attrelid) AND (a.attnum =
(ss.x).x))
               Filter: (NOT attisdropped)
 Total runtime: 30346.174 ms
(60 rows)
X-AntiVirus: checked by AntiVir MailGuard (Version: 8.0.0.18; AVE: 8.1.0.37; VDF: 7.0.3.243)

This is Postgresql 8.2.4, on a Dual-Core XEON 3.6GHz. With nested_loops
off, I get a very fast response (330ms).

Regards,
    Mario Weilguni