pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp tuning query
2+ messages / 2 participants
[nested] [flat]
* Help tuning query
@ 2002-09-26 19:12 Kevin Traub <ktraub@verizon.net>
2002-09-26 19:15 ` Re: Help tuning query Dmitry Tkach <dmitry@openratings.com>
0 siblings, 1 reply; 2+ messages in thread
From: Kevin Traub @ 2002-09-26 19:12 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Help tuning query
2002-09-26 19:12 Help tuning query Kevin Traub <ktraub@verizon.net>
@ 2002-09-26 19:15 ` Dmitry Tkach <dmitry@openratings.com>
0 siblings, 0 replies; 2+ messages in thread
From: Dmitry Tkach @ 2002-09-26 19:15 UTC (permalink / raw)
To: Kevin Traub <ktraub@verizon.net>
First of all, try replacing the username/foldername indexes on operator_messages with a single combined
index on, say (username,foldername)...
It is still not clear to me why it decides not to use one of those indexes you have (it would be less efficient than a
combined index, but still better than a seq. scan) - let's see if having a combined index helps...
If it doesn't, we'll need to look deeper into what exactly it is that makes it choose seqscan over an index...
I hope, it helps...
Dima
Kevin Traub wrote:
> 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
>
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-09-26 19:15 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-26 19:12 Help tuning query Kevin Traub <ktraub@verizon.net>
2002-09-26 19:15 ` Dmitry Tkach <dmitry@openratings.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox