Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 01DDD1337BDD
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  5 Apr 2011 07:27:10 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 35225-01-6
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  5 Apr 2011 10:27:00 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp110.iad.emailsrvr.com (smtp110.iad.emailsrvr.com
	[207.97.245.110])
	by mail.postgresql.org (Postfix) with ESMTP id 978861337BD4
	for <pgsql-performance@postgresql.org>;
	Tue,  5 Apr 2011 07:26:53 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by smtp41.relay.iad1a.emailsrvr.com (SMTP Server) with ESMTP id
	5E64B291594; Tue,  5 Apr 2011 06:26:53 -0400 (EDT)
X-Virus-Scanned: OK
Received: by smtp41.relay.iad1a.emailsrvr.com (Authenticated sender:
	adarsh.sharma-AT-orkash.com) with ESMTPSA id 9BAF329157B
	for <pgsql-performance@postgresql.org>;
	Tue,  5 Apr 2011 06:26:52 -0400 (EDT)
Message-ID: <4D9AEE6C.50006@orkash.com>
Date: Tue, 05 Apr 2011 15:56:52 +0530
From: Adarsh Sharma <adarsh.sharma@orkash.com>
User-Agent: Thunderbird 2.0.0.22 (X11/20090625)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Which is better Index
Content-Type: multipart/alternative;
	boundary="------------020000080706030103000703"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201104/26
X-Sequence-Number: 43088

This is a multi-part message in MIME format.
--------------020000080706030103000703
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------020000080706030103000703
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
Dear all,<br>
<br>
&nbsp;I want to clear my doubts regarding creating several single or a
multi-column indexes.<br>
My table schema is :-<br>
CREATE TABLE svo2(&nbsp; svo_id bigint NOT NULL DEFAULT
nextval('svo_svo_id_seq'::regclass),&nbsp; doc_id integer,&nbsp; sentence_id
integer,&nbsp; clause_id integer,&nbsp; negation integer,&nbsp; subject
charactervarying(3000),&nbsp; verb character varying(3000),&nbsp; "object"
character varying(3000),&nbsp; preposition character varying(3000),&nbsp;
subject_type character varying(3000),&nbsp; object_type
charactervarying(3000),&nbsp; subject_attribute character varying(3000),&nbsp;
object_attribute character varying(3000),&nbsp; verb_attribute character
varying(3000),&nbsp; subject_concept character varying(100), object_concept
character varying(100),&nbsp; subject_sense character varying(100),&nbsp;
object_sense character varying(100),&nbsp; subject_chain character
varying(5000),&nbsp; object_chain character varying(5000),&nbsp; sub_type_id
integer,&nbsp; obj_type_id integer,&nbsp; CONSTRAINT pk_svo_id PRIMARY KEY
(svo_id))WITH (&nbsp; OIDS=FALSE);<br>
<br>
<br>
<u><b>Fore.g</b></u> <br>
<br>
CREATE INDEX idx_svo2_id_dummy&nbsp; ON svo2&nbsp; USING btree (doc_id,
clause_id, sentence_id);<br>
<br>
or<br>
<br>
CREATE INDEX idx_svo2_id_dummy&nbsp; ON svo2&nbsp; USING btree (doc_id);<br>
CREATE INDEX idx_svo2_id_dummy1&nbsp; ON svo2&nbsp; USING btree (clause_id);<br>
CREATE INDEX idx_svo2_id_dummy2&nbsp; ON svo2&nbsp; USING btree (sentence_id);<br>
<br>
Which is better if a query uses all three columns in join where clause.<br>
<br>
<br>
<br>
Thanks &amp; best regards,<br>
Adarsh Sharma<br>
<br>
</body>
</html>

--------------020000080706030103000703--