Thread: Which is better Index

Dear all,

 I want to clear my doubts regarding creating several single or a multi-column indexes.
My table schema is :-
CREATE TABLE svo2(  svo_id bigint NOT NULL DEFAULT nextval('svo_svo_id_seq'::regclass),  doc_id integer,  sentence_id integer,  clause_id integer,  negation integer,  subject charactervarying(3000),  verb character varying(3000),  "object" character varying(3000),  preposition character varying(3000),  subject_type character varying(3000),  object_type charactervarying(3000),  subject_attribute character varying(3000),  object_attribute character varying(3000),  verb_attribute character varying(3000),  subject_concept character varying(100), object_concept character varying(100),  subject_sense character varying(100),  object_sense character varying(100),  subject_chain character varying(5000),  object_chain character varying(5000),  sub_type_id integer,  obj_type_id integer,  CONSTRAINT pk_svo_id PRIMARY KEY (svo_id))WITH (  OIDS=FALSE);


Fore.g

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.



Thanks & best regards,
Adarsh Sharma

Thats very difficult to tell as you have not shared the details of system, like what is the other table,
how the joined table are related and so on.
Basically we need to understand how the data is organized within table and across schema or system.

To begin with, maybe below links could provide some insights:

http://www.postgresql.org/docs/current/static/indexes-multicolumn.html
http://www.postgresql.org/docs/current/static/indexes-bitmap-scans.html


--
Regards,
Chetan Suttraway
EnterpriseDB, The Enterprise PostgreSQL company.

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