Re: Help tuning query - Mailing list pgsql-sql

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
> 
> 
>