Message-ID: <4D9C0E61.7000401@2ndQuadrant.com>
Date: Wed, 06 Apr 2011 02:55:29 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20110307 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Which is better Index
References: <4D9AEE6C.50006@orkash.com>
In-Reply-To: <4D9AEE6C.50006@orkash.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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