Re: Very slow INFORMATION_SCHEMA
От | Mario Weilguni |
---|---|
Тема | Re: Very slow INFORMATION_SCHEMA |
Дата | |
Msg-id | 481EEFD4.3060203@sime.com обсуждение исходный текст |
Ответ на | Re: Very slow INFORMATION_SCHEMA (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Very slow INFORMATION_SCHEMA
|
Список | 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
В списке pgsql-performance по дате отправления:
Предыдущее
От: Shane AmblerДата:
Сообщение: Re: Fastest way / best practice to calculate "next birthdays"