Re: Which is better Index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread 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@xxxxxxxxxxxxxxx   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux