From: Dmitry Tkach <dmitry@openratings.com>
To: Kevin Traub <ktraub@verizon.net>
Subject: Re: Help tuning query
Date: Thu, 26 Sep 2002 15:15:04 -0400
Message-ID: <3D935CB8.10500@openratings.com> (raw)
References: <amvm78$19rg$1@news.hub.org>
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
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dmitry@openratings.com, ktraub@verizon.net
Subject: Re: Help tuning query
In-Reply-To: <3D935CB8.10500@openratings.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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