Re: Which is better Index

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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books