A slow query - Mailing list 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 //