From adarsh.sharma@orkash.com Tue Apr  5 10:27:10 2011
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--



From chetan.suttraway@enterprisedb.com Tue Apr  5 14:07:36 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id AC4681337B47
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  5 Apr 2011 11:07:36 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 31071-01
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  5 Apr 2011 14:07:29 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from exprod7og120.obsmtp.com (exprod7og120.obsmtp.com [64.18.2.18])
	by mail.postgresql.org (Postfix) with ESMTP id A44AB133655C
	for <pgsql-performance@postgresql.org>;
	Tue,  5 Apr 2011 11:07:28 -0300 (ADT)
Received: from mail-vx0-f175.google.com ([209.85.220.175]) (using TLSv1) by
	exprod7ob120.postini.com ([64.18.6.12]) with SMTP
	ID DSNKTZsiIOILcb9lNdVRcMNdtX3VwtBLXFRs@postini.com;
	Tue, 05 Apr 2011 07:07:29 PDT
Received: by mail-vx0-f175.google.com with SMTP id 7so291294vxd.6
	for <pgsql-performance@postgresql.org>;
	Tue, 05 Apr 2011 07:07:28 -0700 (PDT)
Received: by 10.52.95.226 with SMTP id dn2mr6258726vdb.217.1302012448177; Tue,
	05 Apr 2011 07:07:28 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.52.169.99 with HTTP; Tue, 5 Apr 2011 07:07:08 -0700 (PDT)
In-Reply-To: <4D9AEE6C.50006@orkash.com>
References: <4D9AEE6C.50006@orkash.com>
From: Chetan Suttraway <chetan.suttraway@enterprisedb.com>
Date: Tue, 5 Apr 2011 19:37:08 +0530
Message-ID: <BANLkTinbn2aoCMyf752-EO6zapfPgzimzQ@mail.gmail.com>
Subject: Re: Which is better Index
To: Adarsh Sharma <adarsh.sharma@orkash.com>
Cc: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=20cf307d04bc777c8704a02c65ff
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/31
X-Sequence-Number: 43093

--20cf307d04bc777c8704a02c65ff
Content-Type: text/plain; charset=ISO-8859-1

On Tue, Apr 5, 2011 at 3:56 PM, Adarsh Sharma <adarsh.sharma@orkash.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.

--20cf307d04bc777c8704a02c65ff
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<br><br><div class=3D"gmail_quote">On Tue, Apr 5, 2011 at 3:56 PM, Adarsh S=
harma <span dir=3D"ltr">&lt;<a href=3D"mailto:adarsh.sharma@orkash.com">ada=
rsh.sharma@orkash.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_q=
uote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1e=
x;">





<div bgcolor=3D"#ffffff" text=3D"#000000">
Dear all,<br>
<br>
=A0I want to clear my doubts regarding creating several single or a
multi-column indexes.<br>
My table schema is :-<br>
CREATE TABLE svo2(=A0 svo_id bigint NOT NULL DEFAULT
nextval(&#39;svo_svo_id_seq&#39;::regclass),=A0 doc_id integer,=A0 sentence=
_id
integer,=A0 clause_id integer,=A0 negation integer,=A0 subject
charactervarying(3000),=A0 verb character varying(3000),=A0 &quot;object&qu=
ot;
character varying(3000),=A0 preposition character varying(3000),=A0
subject_type character varying(3000),=A0 object_type
charactervarying(3000),=A0 subject_attribute character varying(3000),=A0
object_attribute character varying(3000),=A0 verb_attribute character
varying(3000),=A0 subject_concept character varying(100), object_concept
character varying(100),=A0 subject_sense character varying(100),=A0
object_sense character varying(100),=A0 subject_chain character
varying(5000),=A0 object_chain character varying(5000),=A0 sub_type_id
integer,=A0 obj_type_id integer,=A0 CONSTRAINT pk_svo_id PRIMARY KEY
(svo_id))WITH (=A0 OIDS=3DFALSE);<br>
<br>
<br>
<u><b>Fore.g</b></u> <br>
<br>
CREATE INDEX idx_svo2_id_dummy=A0 ON svo2=A0 USING btree (doc_id,
clause_id, sentence_id);<br>
<br>
or<br>
<br>
CREATE INDEX idx_svo2_id_dummy=A0 ON svo2=A0 USING btree (doc_id);<br>
CREATE INDEX idx_svo2_id_dummy1=A0 ON svo2=A0 USING btree (clause_id);<br>
CREATE INDEX idx_svo2_id_dummy2=A0 ON svo2=A0 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>
</div>

</blockquote></div><br>Thats very difficult to tell as you have not shared =
the details of system, like what is the other table, <br>how the joined tab=
le are related and so on.<br>Basically we need to understand how the data i=
s organized within table and across schema or system.<br>

<br>To begin with, maybe below links could provide some insights:<br><br><a=
 href=3D"http://www.postgresql.org/docs/current/static/indexes-multicolumn.=
html">http://www.postgresql.org/docs/current/static/indexes-multicolumn.htm=
l</a><br>

<a href=3D"http://www.postgresql.org/docs/current/static/indexes-bitmap-sca=
ns.html">http://www.postgresql.org/docs/current/static/indexes-bitmap-scans=
.html</a><br><br><br>-- <br>Regards,<br>Chetan Suttraway<br><font><font col=
or=3D"#888888"><span style=3D"border-collapse:separate;color:rgb(0, 0, 0);f=
ont-family:Helvetica;font-style:normal;font-variant:normal;font-weight:norm=
al;letter-spacing:normal;line-height:normal;text-indent:0px;text-transform:=
none;white-space:normal;word-spacing:0px;font-size:medium"><span style=3D"b=
order-collapse:separate;color:rgb(0, 0, 0);font-family:Helvetica;font-style=
:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-h=
eight:normal;text-indent:0px;text-transform:none;white-space:normal;word-sp=
acing:0px;font-size:medium"><span style=3D"border-collapse:separate;color:r=
gb(0, 0, 0);font-family:Helvetica;font-style:normal;font-variant:normal;fon=
t-weight:normal;letter-spacing:normal;line-height:normal;text-indent:0px;te=
xt-transform:none;white-space:normal;word-spacing:0px;font-size:medium"><sp=
an style=3D"border-collapse:separate;color:rgb(0, 0, 0);font-family:Helveti=
ca;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:=
normal;line-height:normal;text-indent:0px;text-transform:none;white-space:n=
ormal;word-spacing:0px;font-size:medium"><span style=3D"border-collapse:sep=
arate;color:rgb(0, 0, 0);font-family:Helvetica;font-style:normal;font-varia=
nt:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-=
indent:0px;text-transform:none;white-space:normal;word-spacing:0px;font-siz=
e:medium"><span style=3D"border-collapse:separate;color:rgb(0, 0, 0);font-f=
amily:Helvetica;font-style:normal;font-variant:normal;font-weight:normal;le=
tter-spacing:normal;line-height:normal;text-indent:0px;text-transform:none;=
white-space:normal;word-spacing:0px;font-size:medium"><a href=3D"http://www=
.enterprisedb.com/" target=3D"_blank">EnterpriseDB</a>, The=A0<a href=3D"ht=
tp://www.enterprisedb.com/" target=3D"_blank">Enterprise PostgreSQL</a>=A0c=
ompany.</span></span></span></span></span></span></font></font><br>

<br><br><br>

--20cf307d04bc777c8704a02c65ff--



From greg@2ndQuadrant.com Wed Apr  6 06:55:14 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 7D44E1337BA8
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  6 Apr 2011 03:55:14 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 65480-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed,  6 Apr 2011 06:55:07 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.9])
	by mail.postgresql.org (Postfix) with ESMTP id DDBFE13364DE
	for <pgsql-performance@postgresql.org>;
	Wed,  6 Apr 2011 03:55:06 -0300 (ADT)
Received: from [192.168.1.6] (mail.highperformancepostgresql.com
	[71.179.240.8])
	by mrelayeu.kundenserver.de (node=mrbap0) with ESMTP (Nemesis)
	id 0M3M80-1PpyJd2wrI-00rTyI; Wed, 06 Apr 2011 08:55:06 +0200
Message-ID: <4D9C0E61.7000401@2ndQuadrant.com>
Date: Wed, 06 Apr 2011 02:55:29 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20110307 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Which is better Index
References: <4D9AEE6C.50006@orkash.com>
In-Reply-To: <4D9AEE6C.50006@orkash.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Provags-ID: V02:K0:sJ0akddVVns9Z5jCQrJF6oDBXTUJiJOkEnsSlzSGjuN
	Zifcnilt4av4avUSHcOj1LzhACvmtZ1q5CaJzgcMJbREL3aD35
	9IXA63sH8Lpenmgo4IZwX0wnbFch67ZKwmy1r/jSEGyZNkf7Ni
	sVgb4iewAewA7imid5DiqG72AlgIM0pjjkI6dqpWPfskN7Q8jg
	6cDAT/4vLHcMYpAdNF4S/YPahlpdmim3jzS8K6tWlU=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201104/39
X-Sequence-Number: 43101

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