From benjamin@araisoft.com Tue Jun  13 16:04:25 2006
X-Original-To: pgsql-general-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id B11529FA13E;
	Tue, 13 Jun 2006 13:04:25 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 93919-08; Tue, 13 Jun 2006 13:04:16 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from server10.araisoft.com (server10.araisoft.com [72.9.228.195])
	by postgresql.org (Postfix) with ESMTP id CAE279F9316;
	Tue, 13 Jun 2006 13:04:15 -0300 (ADT)
Received: from uni (adsl-69-235-160-36.dsl.irvnca.pacbell.net [69.235.160.36])
	by server10.araisoft.com (Postfix) with ESMTP
	id 262E23D62034; Tue, 13 Jun 2006 09:04:14 -0700 (PDT)
Reply-To: <benjamin@araisoft.com>
From: "Benjamin Arai" <benjamin@araisoft.com>
To: <pgsql-general@postgresql.org>,
	<pgsql-performance@postgresql.org>
Subject: Question about clustering multiple columns
Date: Tue, 13 Jun 2006 09:04:15 -0700
Organization: Araisoft
Message-ID: <002e01c68f03$05f00e10$6501a8c0@uni>
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_NextPart_000_002F_01C68EC8.59913610"
X-Mailer: Microsoft Office Outlook 11
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
thread-index: AcaPAa1hG8/KtusrTQGYSpnCQ9zr5w==
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/636
X-Sequence-Number: 97008

This is a multi-part message in MIME format.

------=_NextPart_000_002F_01C68EC8.59913610
Content-Type: multipart/alternative;
	boundary="----=_NextPart_001_0030_01C68EC8.59913610"


------=_NextPart_001_0030_01C68EC8.59913610
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi,
 
I have a database where there are three columns (name,date,data).  The
queries are almost always something like SELECT date,data FROM table WHERE
name=blah AND date > 1/1/2005 AND date < 1/1/2006;.  I currently have three
B-tree indexes, one for each of the columns.  Is clustering on date index
going to be what I want, or do I need a index that contains both name and
date?
 
Benjamin Arai
barai@cs.ucr.edu
http://www.benjaminarai.com <http://www.benjaminarai.com/> 
 
 

------=_NextPart_001_0030_01C68EC8.59913610
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<META content=3D"MSHTML 6.00.2900.2873" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D873365015-13062006><FONT face=3DArial=20
size=3D2>Hi,</FONT></SPAN></DIV>
<DIV><SPAN class=3D873365015-13062006><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D873365015-13062006><FONT face=3DArial size=3D2>I have =
a database=20
where there are three columns (name,date,data).&nbsp; The queries are =
almost=20
always something like SELECT date,data FROM table WHERE name=3Dblah AND =
date &gt;=20
1/1/2005 AND date &lt; 1/1/2006;.&nbsp; I currently have&nbsp;three =
B-tree=20
indexes, one&nbsp;for each of the columns.&nbsp; Is clustering on date =
index=20
going to be what I want, or do I need a index that contains both name =
and=20
date?</FONT></SPAN></DIV>
<DIV><SPAN class=3D873365015-13062006><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D873365015-13062006>
<DIV align=3Dleft><FONT face=3DArial><STRONG>
<DIV dir=3Dltr align=3Dleft><FONT face=3DArial><STRONG>Benjamin=20
Arai</STRONG></FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2><A=20
href=3D"mailto:barai@cs.ucr.edu">barai@cs.ucr.edu</A></FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2><A=20
href=3D"http://www.benjaminarai.com/">http://www.benjaminarai.com</A></FO=
NT></DIV>
<DIV align=3Dleft><FONT face=3DArial=20
size=3D2></FONT>&nbsp;</DIV></STRONG></FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV><FONT =
face=3DArial=20
size=3D2></FONT></SPAN></DIV></BODY></HTML>

------=_NextPart_001_0030_01C68EC8.59913610--

------=_NextPart_000_002F_01C68EC8.59913610
Content-Type: text/x-vcard;
	name="barai@cs.ucr.edu.vcf"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
	filename="barai@cs.ucr.edu.vcf"

BEGIN:VCARD
VERSION:2.1
N:Arai;Benjamin
FN:barai@cs.ucr.edu
ORG:University of California, Riverside
TITLE:PhD Student
TEL;WORK;VOICE:(951) 827-2838
TEL;HOME;VOICE:(951) 682-6816
TEL;CELL;VOICE:(626) 378-2992
ADR;WORK;ENCODING=3DQUOTED-PRINTABLE:;;Department of Computer Science =
and Engineering=3D0D=3D0AEngineering BU2, Room =3D
351=3D0D=3D0AUniversity of California;Riverside;CA;92521;United States =
of Americ=3D
a
LABEL;WORK;ENCODING=3DQUOTED-PRINTABLE:Department of Computer Science =
and Engineering=3D0D=3D0AEngineering BU2, Room 35=3D
1=3D0D=3D0AUniversity of California=3D0D=3D0ARiverside, CA =
92521=3D0D=3D0AUnited States =3D
of America
URL;WORK:http://www.benjaminarai.com
EMAIL;PREF;INTERNET:barai@cs.ucr.edu
REV:20060520T165426Z
END:VCARD

------=_NextPart_000_002F_01C68EC8.59913610--




From bruno@wolff.to Fri Jun  16 15:28:08 2006
X-Original-To: pgsql-general-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 3F5ED9FA42B
	for <pgsql-general-postgresql.org@postgresql.org>;
	Fri, 16 Jun 2006 12:28:08 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 51193-01 for
	<pgsql-general-postgresql.org@postgresql.org>;
	Fri, 16 Jun 2006 12:27:59 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from wolff.to (wolff.to [66.93.197.194])
	by postgresql.org (Postfix) with SMTP id 778929FA5C3
	for <pgsql-general@postgresql.org>;
	Fri, 16 Jun 2006 12:27:59 -0300 (ADT)
Received: (qmail 12719 invoked by uid 500); 16 Jun 2006 15:31:32 -0000
Date: Fri, 16 Jun 2006 10:31:32 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Benjamin Arai <benjamin@araisoft.com>
Cc: pgsql-general@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: Question about clustering multiple columns
Message-ID: <20060616153132.GA9056@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Benjamin Arai <benjamin@araisoft.com>, pgsql-general@postgresql.org,
	pgsql-performance@postgresql.org
References: <002e01c68f03$05f00e10$6501a8c0@uni>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <002e01c68f03$05f00e10$6501a8c0@uni>
User-Agent: Mutt/1.4.2.1i
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/676
X-Sequence-Number: 97048

On Tue, Jun 13, 2006 at 09:04:15 -0700,
  Benjamin Arai <benjamin@araisoft.com> wrote:
> Hi,
>  
> I have a database where there are three columns (name,date,data).  The
> queries are almost always something like SELECT date,data FROM table WHERE
> name=blah AND date > 1/1/2005 AND date < 1/1/2006;.  I currently have three
> B-tree indexes, one for each of the columns.  Is clustering on date index
> going to be what I want, or do I need a index that contains both name and
> date?

I would expect that clustering on the name would be better for the above
query.
You probably want an index on name and date combined.



From benjamin@araisoft.com Fri Jun  16 19:00:16 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 7DF279FA4C1
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 16 Jun 2006 16:00:16 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 71688-10 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 16 Jun 2006 16:00:10 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from server10.araisoft.com (server10.araisoft.com [72.9.228.195])
	by postgresql.org (Postfix) with ESMTP id 6637A9FA10A
	for <pgsql-performance@postgresql.org>;
	Fri, 16 Jun 2006 16:00:10 -0300 (ADT)
Received: from akami (puravida.cs.ucr.edu [138.23.204.204])
	by server10.araisoft.com (Postfix) with ESMTP
	id B86303D6200C; Fri, 16 Jun 2006 11:59:58 -0700 (PDT)
From: "Benjamin Arai" <benjamin@araisoft.com>
To: "'Bruno Wolff III'" <bruno@wolff.to>
Cc: <pgsql-performance@postgresql.org>
References: <002e01c68f03$05f00e10$6501a8c0@uni>
	<20060616153132.GA9056@wolff.to>
	<002301c69170$5d715170$1853f450$@com>
	<20060616185556.GA12618@wolff.to>
In-Reply-To: <20060616185556.GA12618@wolff.to>
Subject: Re: Question about clustering multiple columns
Date: Fri, 16 Jun 2006 11:55:38 -0700
Message-ID: <003201c69176$7694f5c0$63bee140$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcaRdgagmFUnhF7qTVGuXpGetV0RLAAAEBZQ
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/433
X-Sequence-Number: 19790

Thanks! This exactly what I was looking for.  

Benjamin Arai
Benjamin@araisoft.com
http://www.benjaminarai.com

-----Original Message-----
From: Bruno Wolff III [mailto:bruno@wolff.to] 
Sent: Friday, June 16, 2006 11:56 AM
To: Benjamin Arai
Cc: pgsql-performance@postgresql.org
Subject: Re: Question about clustering multiple columns

On Fri, Jun 16, 2006 at 11:11:59 -0700,
  Benjamin Arai <benjamin@araisoft.com> wrote:
> Hi,
> 
> Thanks for the reply. I have one more question.  Does it matter in 
> which order that I make the index?

Please keep replies copied to the lists so that other people can learn from
and crontibute to the discussion.

In this case I am just going to copy back to the performance list, since it
is generally better for perfomance questions than the general list.

> For example, should I create an index cusip,date or date,cusip, does 
> it matter which order.  My goal is to cluster the entries by cusip, 
> then for each cusip order the data by date (maybe the order by data 
> occurs automatically).  Hm, in that case maybe I only need to cluster 
> by cusip, but then how do I ensure that each cusip had its data ordered by
date?

I think that you want to order by cusip (assuming that corresponds to "name"
in you sample query below) first. You won't end up having to go through
values in the index that will be filtered out if you do it that way.

The documentation for the cluster command says that it clusters on indexes,
not columns. So if the index is on (cusip, date), then the records will be
ordered by cusip, date immediately after the cluster. (New records added
after the cluster are not guarenteed to be ordered by the index.)

> 
> Benjamin
> 
> -----Original Message-----
> From: Bruno Wolff III [mailto:bruno@wolff.to]
> Sent: Friday, June 16, 2006 8:32 AM
> To: Benjamin Arai
> Cc: pgsql-general@postgresql.org; pgsql-performance@postgresql.org
> Subject: Re: Question about clustering multiple columns
> 
> On Tue, Jun 13, 2006 at 09:04:15 -0700,
>   Benjamin Arai <benjamin@araisoft.com> wrote:
> > Hi,
> >  
> > I have a database where there are three columns (name,date,data).  
> > The queries are almost always something like SELECT date,data FROM 
> > table WHERE name=blah AND date > 1/1/2005 AND date < 1/1/2006;.  I 
> > currently have three B-tree indexes, one for each of the columns.  
> > Is clustering on date index going to be what I want, or do I need a 
> > index that contains both name and date?
> 
> I would expect that clustering on the name would be better for the 
> above query.
> You probably want an index on name and date combined.
> 
> 
> 

!DSPAM:4492fdfd193631139819016!




From bruno@wolff.to Fri Jun  16 18:52:28 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 18E229FA4C1
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 16 Jun 2006 15:52:28 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 71432-08 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 16 Jun 2006 15:52:21 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from wolff.to (wolff.to [66.93.197.194])
	by postgresql.org (Postfix) with SMTP id 91C539FA10A
	for <pgsql-performance@postgresql.org>;
	Fri, 16 Jun 2006 15:52:21 -0300 (ADT)
Received: (qmail 13775 invoked by uid 500); 16 Jun 2006 18:55:56 -0000
Date: Fri, 16 Jun 2006 13:55:56 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Benjamin Arai <benjamin@araisoft.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Question about clustering multiple columns
Message-ID: <20060616185556.GA12618@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Benjamin Arai <benjamin@araisoft.com>,
	pgsql-performance@postgresql.org
References: <002e01c68f03$05f00e10$6501a8c0@uni>
	<20060616153132.GA9056@wolff.to>
	<002301c69170$5d715170$1853f450$@com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <002301c69170$5d715170$1853f450$@com>
User-Agent: Mutt/1.4.2.1i
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/358
X-Sequence-Number: 19715

On Fri, Jun 16, 2006 at 11:11:59 -0700,
  Benjamin Arai <benjamin@araisoft.com> wrote:
> Hi,
> 
> Thanks for the reply. I have one more question.  Does it matter in which
> order that I make the index?

Please keep replies copied to the lists so that other people can learn from
and crontibute to the discussion.

In this case I am just going to copy back to the performance list, since it
is generally better for perfomance questions than the general list.

> For example, should I create an index cusip,date or date,cusip, does it
> matter which order.  My goal is to cluster the entries by cusip, then for
> each cusip order the data by date (maybe the order by data occurs
> automatically).  Hm, in that case maybe I only need to cluster by cusip, but
> then how do I ensure that each cusip had its data ordered by date?

I think that you want to order by cusip (assuming that corresponds to "name"
in you sample query below) first. You won't end up having to go through values
in the index that will be filtered out if you do it that way.

The documentation for the cluster command says that it clusters on indexes,
not columns. So if the index is on (cusip, date), then the records will be
ordered by cusip, date immediately after the cluster. (New records added 
after the cluster are not guarenteed to be ordered by the index.)

> 
> Benjamin
> 
> -----Original Message-----
> From: Bruno Wolff III [mailto:bruno@wolff.to] 
> Sent: Friday, June 16, 2006 8:32 AM
> To: Benjamin Arai
> Cc: pgsql-general@postgresql.org; pgsql-performance@postgresql.org
> Subject: Re: Question about clustering multiple columns
> 
> On Tue, Jun 13, 2006 at 09:04:15 -0700,
>   Benjamin Arai <benjamin@araisoft.com> wrote:
> > Hi,
> >  
> > I have a database where there are three columns (name,date,data).  The 
> > queries are almost always something like SELECT date,data FROM table 
> > WHERE name=blah AND date > 1/1/2005 AND date < 1/1/2006;.  I currently 
> > have three B-tree indexes, one for each of the columns.  Is clustering 
> > on date index going to be what I want, or do I need a index that 
> > contains both name and date?
> 
> I would expect that clustering on the name would be better for the above
> query.
> You probably want an index on name and date combined.
> 
> !DSPAM:4492ce0d180368658827628!
>