From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Which is better Index
Date: Wed, 06 Apr 2011 02:55:29 -0400
Message-ID: <4D9C0E61.7000401@2ndQuadrant.com> (raw)
In-Reply-To: <4D9AEE6C.50006@orkash.com>
References: <4D9AEE6C.50006@orkash.com>
On 04/05/2011 06:26 AM, Adarsh Sharma wrote:
> CREATE INDEX idx_svo2_id_dummy ON svo2 USING btree (doc_id,
> clause_id, sentence_id);
>
> or
>
> CREATE INDEX idx_svo2_id_dummy ON svo2 USING btree (doc_id);
> CREATE INDEX idx_svo2_id_dummy1 ON svo2 USING btree (clause_id);
> CREATE INDEX idx_svo2_id_dummy2 ON svo2 USING btree (sentence_id);
>
> Which is better if a query uses all three columns in join where clause.
Impossible to say. It's possible neither approach is best. If
clause_id and sentence_id are not very selective, the optimal setup here
could easily be an index on only doc_id. Just index that, let the query
executor throw out non-matching rows. Indexes are expensive to
maintain, and are not free to use in queries either.
What you could do here is create all four of these indexes, try to
simulate your workload, and see which actually get used. Throw out the
ones that the optimizer doesn't use anyway. The odds are against you
predicting what's going to happen accurately here. You might as well
accept that, set things up to measure what happens instead, and use that
as feedback on the design.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
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-performance@postgresql.org
Cc: greg@2ndQuadrant.com
Subject: Re: Which is better Index
In-Reply-To: <4D9C0E61.7000401@2ndQuadrant.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