From: Tomasz Myrta <jasiek@klaster.net>
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [SQL] 7.3.1 index use / performance
Date: Tue, 07 Jan 2003 13:00:12 +0100
Message-ID: <3E1AC14C.7010104@klaster.net> (raw)
In-Reply-To: <Pine.LNX.4.44.0301071338480.7770-100000@matrix.gatewaynet.com>
References: <Pine.LNX.4.44.0301071338480.7770-100000@matrix.gatewaynet.com>
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
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: jasiek@klaster.net, achill@matrix.gatewaynet.com, pgsql-performance@postgresql.org
Subject: Re: [SQL] 7.3.1 index use / performance
In-Reply-To: <3E1AC14C.7010104@klaster.net>
* 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