Help tuning query

From: "Kevin Traub" <ktraub(at)verizon(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Help tuning query
Date: 2002-09-26 19:12:38
Message-ID: amvm78$19rg$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

All;

Can anyone please help with the tuning of this query?
With 77000 rows in the operator_messages database the query is taking almost
15 seconds to return. Preference woul dbe under 5 seconds if possible.
System load on a dual processor P3 with 1.5GB of memory remains under .4
during the query.
The query and explain are noted below as well as description of the tables;
Note both ANALYZE and VACUUM have been run numerous times.
any help would be appreciated. -Kev

virgin=# explain SELECT opr.msg_id, opr.status, opc.std_time
virgin-# FROM op_msg_folder opc, operator_messages opr
virgin-# WHERE opr.username = 'khp'
virgin-# AND opr.foldername = 'inbox'
virgin-# and opr.msg_id = opc.msg_id;
NOTICE: QUERY PLAN:

Merge Join (cost=25037.29..27675.47 rows=47958 width=54)
-> Index Scan using opmf_i on op_msg_folder opc (cost=0.00..1797.37
rows=48579 width=32)
-> Sort (cost=25037.29..25037.29 rows=47958 width=22)
-> Seq Scan on operator_messages opr (cost=0.00..20722.26
rows=47958 width=22)

virgin=# \d operator_messages
Table "operator_messages"
Column | Type | Modifiers
------------+--------------+-----------
msg_id | numeric |
username | text |
foldername | text |
status | character(1) |
Indexes: op_msgs_i,
opr_msgs_foldername_i,
opr_msgs_username_i

virgin=# \d op_msgs_i
Index "op_msgs_i"
Column | Type
--------+---------
msg_id | numeric
btree

virgin=# \d opr_msgs_foldername_i
Index "opr_msgs_foldername_i"
Column | Type
------------+------
foldername | text
btree

virgin=# \d opr_msgs_username_i
Index "opr_msgs_username_i"
Column | Type
----------+------
username | text
btree

virgin=# \d op_msg_folder
Table "op_msg_folder"
Column | Type | Modifiers
----------------+--------------+-----------
msg_id | numeric |
status | character(1) |
std_time | text |
julian_time | text |
smi | character(3) |
description | text |
type | text |
flight | text |
tail | text |
dep_station | text |
dest_station | text |
op_description | text |
Unique keys: opmf_i

virgin=# \d opmf_i;
Index "opmf_i"
Column | Type
--------+---------
msg_id | numeric
unique btree

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Dmitry Tkach 2002-09-26 19:15:04 Re: Help tuning query
Previous Message Robert Treat 2002-09-26 19:09:32 Re: Preventing DELETEs