[SQL] Help tuning query

[Kevin Traub]

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



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[SQL] Can Postgres cache a table in memory?

[Kevin Traub]

Hello all;

I'm trying to speed up a query which returns the majority of a table so and
index isn't helpful.
I've got more than enough RAM to hold my table so, can anyone tell me if
there is there a way to force Postgres to cache a table in RAM?

Any help would be appreciated.

Thanks;
-Kevin Traub



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])