Re: [SQL] 7.3.1 index use / performance - Mailing list pgsql-performance

From Tomasz Myrta
Subject Re: [SQL] 7.3.1 index use / performance
Date
Msg-id 3E1AC14C.7010104@klaster.net
Whole thread Raw
In response to 7.3.1 index use / performance  (Achilleus Mantzios <achill@matrix.gatewaynet.com>)
Responses Re: [SQL] 7.3.1 index use / performance  (Achilleus Mantzios <achill@matrix.gatewaynet.com>)
List pgsql-performance
Achilleus Mantzios wrote:
<cut>

 > it has indexes:
 > Indexes: noonf_date btree (report_date),
 >          noonf_logno btree (log_no),
 >          noonf_rotation btree (rotation text_ops),
 >          noonf_vcode btree (v_code),
 >          noonf_voyageno btree (voyage_no)
 >
<cut>

 >
 >
-------------------------------------------------------------------------------------------------------------------
 >  Index Scan using noonf_date on noon  (cost=0.00..4.46 rows=1 width=39)
 > (actual time=0.27..52.89 rows=259 loops=1)
 >    Index Cond: ((report_date >= '2002-01-07'::date) AND (report_date <=
 > '2003-01-07'::date))
 >    Filter: ((v_code = '4500'::character varying) AND (rotation = 'NOON
 > '::character varying))
 >  Total runtime: 53.98 msec
 > (4 rows)
<cut>

Maybe it is not an answer to your question, but why don't you help
Postgres by yourself?
For this kind of queries it's better to drop index on report_date - your
report period is one year and answer to this condition is 10% records (I
suppose)
It would be better to change 2 indexes on v_code and rotation into one
index based on both fields.
What kind of queries do you have? How many records returns each "where"
condition? Use indexes on fields, on which condition result in smallest
amount of rows.

Regards,
Tomasz Myrta


pgsql-performance by date:

Previous
From: Achilleus Mantzios
Date:
Subject: 7.3.1 index use / performance
Next
From: Achilleus Mantzios
Date:
Subject: Re: [SQL] 7.3.1 index use / performance