Re: Which is better Index

On Tue, Apr 5, 2011 at 3:56 PM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com>wrote:

> 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 <http://www.enterprisedb.com/>, The Enterprise
PostgreSQL<http://www.enterprisedb.com/>
company.