A slow query
От | Alban Hertroys |
---|---|
Тема | A slow query |
Дата | |
Msg-id | 448EBA3D.8020801@magproductions.nl обсуждение исходный текст |
Ответы |
Re: A slow query
(Alban Hertroys <alban@magproductions.nl>)
Re: A slow query - Help please? (Alban Hertroys <alban@magproductions.nl>) |
Список | pgsql-general |
Hi all, We're using some 3rd party product that uses inheritence, and the following query is rather slow on PostgreSQL 7.4.7 (debian stable). Any suggestions how to speed it up? explain analyze SELECT otype,owner,rnumber,dir,number,dnumber,pos,snumber FROM mm_posrel posrel ORDER BY number DESC LIMIT 25; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=7996.04..7996.10 rows=25 width=60) (actual time=2329.505..2329.767 rows=25 loops=1) -> Sort (cost=7996.04..8157.42 rows=64553 width=60) (actual time=2329.495..2329.585 rows=25 loops=1) Sort Key: posrel.number -> Result (cost=0.00..1510.51 rows=64553 width=60) (actual time=0.045..1644.541 rows=75597 loops=1) -> Append (cost=0.00..1510.51 rows=64553 width=60) (actual time=0.034..977.543 rows=75597 loops=1) -> Seq Scan on mm_posrel posrel (cost=0.00..1510.51 rows=64551 width=39) (actual time=0.027..436.501 rows=75597 loops=1) -> Seq Scan on mm_menu_item posrel (cost=0.00..0.00 rows=1 width=60) (actual time=0.005..0.005 rows=0 loops=1) -> Seq Scan on mm_cms_operation posrel (cost=0.00..0.00 rows=1 width=60) (actual time=0.005..0.005 rows=0 loops=1) Total runtime: 2332.136 ms (9 rows) The tables look like (I added the pkeys after the indexes on number, it didn't change the problem): Table "public.mm_posrel" Column | Type | Modifiers ---------+---------+----------- number | integer | not null otype | integer | not null owner | text | not null snumber | integer | not null dnumber | integer | not null rnumber | integer | not null dir | integer | pos | integer | Indexes: "mm_posrel_pkey" primary key, btree (number) "mm_posrel_dnumber_idx" btree (dnumber) "mm_posrel_number_idx" btree (number) "mm_posrel_rnumber_idx" btree (rnumber) "mm_posrel_snumber_idx" btree (snumber) Inherits: mm_insrel Table "public.mm_menu_item" Column | Type | Modifiers ---------+---------+----------- number | integer | not null otype | integer | not null owner | text | not null snumber | integer | not null dnumber | integer | not null rnumber | integer | not null dir | integer | pos | integer | name | text | not null Indexes: "mm_menu_item_pkey" primary key, btree (number) "mm_menu_item_dnumber_idx" btree (dnumber) "mm_menu_item_number_idx" btree (number) "mm_menu_item_rnumber_idx" btree (rnumber) "mm_menu_item_snumber_idx" btree (snumber) Inherits: mm_posrel Table "public.mm_cms_operation" Column | Type | Modifiers ---------+---------+----------- number | integer | not null otype | integer | not null owner | text | not null snumber | integer | not null dnumber | integer | not null rnumber | integer | not null dir | integer | pos | integer | m_type | text | not null getvars | text | Indexes: "mm_cms_operation_pkey" primary key, btree (number) "mm_cms_operation_dnumber_idx" btree (dnumber) "mm_cms_operation_number_idx" btree (number) "mm_cms_operation_rnumber_idx" btree (rnumber) "mm_cms_operation_snumber_idx" btree (snumber) Inherits: mm_posrel Table "public.mm_insrel" Column | Type | Modifiers ---------+---------+----------- number | integer | not null otype | integer | not null owner | text | not null snumber | integer | not null dnumber | integer | not null rnumber | integer | not null dir | integer | Indexes: "mm_insrel_dnumber_idx" btree (dnumber) "mm_insrel_number_idx" btree (number) "mm_insrel_rnumber_idx" btree (rnumber) "mm_insrel_snumber_idx" btree (snumber) Inherits: mm_object Table "public.mm_object" Column | Type | Modifiers --------+---------+----------- number | integer | not null otype | integer | not null owner | text | not null Indexes: "mm_object_pkey" primary key, btree (number) -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
В списке pgsql-general по дате отправления: