Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id D8C392E002F
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 25 Feb 2008 12:50:54 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 96469-04 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 25 Feb 2008 12:50:45 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from el-out-1112.google.com (el-out-1112.google.com
 [209.85.162.181])
	by postgresql.org (Postfix) with ESMTP id 29F8F2E003A
	for <pgsql-performance@postgresql.org>;
	Mon, 25 Feb 2008 12:50:46 -0400 (AST)
Received: by el-out-1112.google.com with SMTP id r27so1503237ele.5
	for <pgsql-performance@postgresql.org>;
	Mon, 25 Feb 2008 08:50:45 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	bh=TuyZx7oUaF9/c2FeqGdfqobb/Wjg8yGZN360q/oky6I=;
	b=W2TlheEbv1aqJUpVKXc4e0Vt2Nx3Lh5JpAjz6kstMGqlNfqP37g5deIj2OfkgiioBKdlx/ypo5lGe0JAtsSxHjAuxLvONiNY2AZRhlY+TN86u3K0AwkX3ydLlU74pmOG9+krSsqQ9Qn1dBm7hKgmAEmCTFo01zH+8XcZAX0QtWA=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=iMJcW4jjN4md6bJ+40501UDbWgIT0LWilkGG0R+GvaZdneZ37Wf3yoLisAyM+ZuEUrmsNeu85csth+SmFGK/XqukxzxpvuKjG9H3z6vCCuuvCRKyPTDiiVfNJieYEeN56/wHNKrLNnPF7R5hA19ncoizMMZyS7gWxYFNuGax63Y=
Received: by 10.114.175.16 with SMTP id x16mr3863928wae.116.1203958244883;
	Mon, 25 Feb 2008 08:50:44 -0800 (PST)
Received: by 10.114.176.19 with HTTP; Mon, 25 Feb 2008 08:50:44 -0800 (PST)
Message-ID: <c2350ba40802250850m2ae1867agbbdc942c3c482d5b@mail.gmail.com>
Date: Mon, 25 Feb 2008 11:50:44 -0500
From: "Kynn Jones" <kynnjo@gmail.com>
To: Matthew <matthew@flymine.org>
Subject: Re: Q on views and performance
Cc: pgsql-performance@postgresql.org
In-Reply-To: <Pine.LNX.4.64.0802251326010.20402@aragorn.flymine.org>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_14423_10807493.1203958244876"
References: <c2350ba40802221249h37913d0cyaa6387a93c55fe69@mail.gmail.com>
	<Pine.LNX.4.64.0802251326010.20402@aragorn.flymine.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/323
X-Sequence-Number: 28865

------=_Part_14423_10807493.1203958244876
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Mon, Feb 25, 2008 at 8:45 AM, Matthew <matthew@flymine.org> wrote:

> On Fri, 22 Feb 2008, Kynn Jones wrote:
> > Hi.  I'm trying to optimize...
> >
> > (Q1)   SELECT a1.word, a2.word
> >         FROM T a1 JOIN T a2 USING ( zipk )
> >        WHERE a1.type = <int1>
> >          AND a2.type = <int2>;
>
> Okay, try this:
>
> Create an index on T(type, zipk), and then CLUSTER on that index...


This is just GREAT!!!  It fits the problem to a tee.

Many, many thanks!

Also, including zipk in the index is a really nice extra boost.  (If you
hadn't mentioned it I would have just settled for clustering only on
type...)

Thanks for that also!

Kynn

------=_Part_14423_10807493.1203958244876
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div class="gmail_quote">On Mon, Feb 25, 2008 at 8:45 AM, Matthew &lt;<a href="mailto:matthew@flymine.org">matthew@flymine.org</a>&gt; wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
On Fri, 22 Feb 2008, Kynn Jones wrote:<br>
&gt; Hi. &nbsp;I&#39;m trying to optimize...<br>
<div class="Ih2E3d">&gt;<br>
&gt; (Q1) &nbsp; SELECT a1.word, a2.word<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; FROM T a1 JOIN T a2 USING ( zipk )<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp;WHERE a1.type = &lt;int1&gt;<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND a2.type = &lt;int2&gt;;<br>
<br>
</div>Okay, try this:<br>
<br>
Create an index on T(type, zipk), and then CLUSTER on that index...</blockquote><div><br class="webkit-block-placeholder"></div><div>This is just GREAT!!! &nbsp;It fits the problem to a tee.</div><div><br class="webkit-block-placeholder">
</div><div>Many, many thanks!</div><div><br class="webkit-block-placeholder"></div><div>Also, including zipk in the index is a really nice extra boost. &nbsp;(If you hadn&#39;t mentioned it I would have just settled for clustering only on type...)</div>
<div><br class="webkit-block-placeholder"></div><div>Thanks for that also!</div><div><br class="webkit-block-placeholder"></div><div>Kynn</div><div><br></div></div>

------=_Part_14423_10807493.1203958244876--