A slow query

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(at)magproductions(dot)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 //