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