Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 1C5B12E0129
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sat, 23 Feb 2008 12:55:22 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 83779-09 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sat, 23 Feb 2008 12:55:19 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mail.mailpen.net (mail.mailpen.net [209.90.228.14])
	by postgresql.org (Postfix) with ESMTP id 1913D2E002E
	for <pgsql-performance@postgresql.org>;
	Sat, 23 Feb 2008 12:55:19 -0400 (AST)
Received: from [172.24.2.1] (pool-71-113-24-134.sttlwa.dsl-w.verizon.net
	[71.113.24.134])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.mailpen.net (Postfix) with ESMTP id D4E2C2FE802
	for <pgsql-performance@postgresql.org>;
	Sat, 23 Feb 2008 08:55:17 -0800 (PST)
Message-ID: <47C0500E.90907@ultimeth.com>
Date: Sat, 23 Feb 2008 08:55:42 -0800
From: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Organization: UltiMeth Systems
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Q on views and performance
References: <c2350ba40802221249h37913d0cyaa6387a93c55fe69@mail.gmail.com>	
	<47BF7B80.1040309@ultimeth.com>	
	<c2350ba40802230559l280eab73k8e79a8bda7a4994a@mail.gmail.com>	
	<47C03709.5010205@ultimeth.com>
	<c2350ba40802230821t2446a151j3279b8ea901c31b3@mail.gmail.com>
	<47C04E89.3030300@ultimeth.com>
In-Reply-To: <47C04E89.3030300@ultimeth.com>
Content-Type: multipart/alternative;
	boundary="------------010703090009090700050505"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/299
X-Sequence-Number: 28841

This is a multi-part message in MIME format.
--------------010703090009090700050505
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 2008-02-23 08:49, Dean Gibson (DB Administrator) wrote:
> Why 10,000 views???  What's wrong with the ONE view above?  You DON'T 
> want to be defining VIEWs based on actual tables VALUES;  leave that 
> to the SELECT.  For that matter, what's wrong with the final SELECT I 
> listed (below)?
>
> SELECT a1.word AS word1, a2.word AS word2
>   FROM S JOIN T a1 USING( word )
>     LEFT JOIN T a2 USING( zipk )
>   WHERE a1.type = <int1> AND a2.type = <int2>;
>
> -- Dean
Amendment:  I forgot, that if it's a LEFT JOIN you have to write it as:

SELECT a1.word AS word1, a2.word AS word2
  FROM S JOIN T a1 USING( word )
    LEFT JOIN T a2 USING( zipk )
  WHERE a1.type = <int1> AND (a2.type = <int2> OR a2.type IS NULL);

-- Dean

-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.


--------------010703090009090700050505
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
On 2008-02-23 08:49, Dean Gibson (DB Administrator) wrote:
<blockquote cite="mid:47C04E89.3030300@ultimeth.com" type="cite">
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
Why 10,000 views???  What's wrong with the ONE view above?  You DON'T
want to be defining VIEWs based on actual tables VALUES;  leave that to
the SELECT.  For that matter, what's wrong with the final SELECT I
listed (below)?<br>
  <br>
SELECT <tt>a1.word AS word1, a2.word AS word2<br>
  FROM S JOIN T a1 USING( word )<br>
    LEFT JOIN T a2 USING( zipk )</tt><br>
  <tt>  WHERE a1.type = &lt;int1&gt; AND a2.type = &lt;int2&gt;;</tt><br>
  <br>
-- Dean<br>
</blockquote>
Amendment:  I forgot, that if it's a LEFT JOIN you have to write it as:<br>
<br>
<tt>SELECT a1.word AS word1, a2.word AS word2<br>
  FROM S JOIN T a1 USING( word )<br>
    LEFT JOIN T a2 USING( zipk )<br>
</tt><tt>  WHERE a1.type = &lt;int1&gt; AND (a2.type = &lt;int2&gt; OR
a2.type IS NULL);</tt><br>
<br>
-- Dean<br>
<pre class="moz-signature" cols="72">-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.</pre>
</body>
</html>

--------------010703090009090700050505--