Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E339E2E003F for ; Sat, 23 Feb 2008 12:48:52 -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 83776-05 for ; Sat, 23 Feb 2008 12:48:50 -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 6463C2E002E for ; Sat, 23 Feb 2008 12:48:50 -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 42F422FE802 for ; Sat, 23 Feb 2008 08:48:49 -0800 (PST) Message-ID: <47C04E89.3030300@ultimeth.com> Date: Sat, 23 Feb 2008 08:49:13 -0800 From: "Dean Gibson (DB Administrator)" 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: <47BF7B80.1040309@ultimeth.com> <47C03709.5010205@ultimeth.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------040909020005000208030009" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/298 X-Sequence-Number: 28840 This is a multi-part message in MIME format. --------------040909020005000208030009 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 2008-02-23 08:21, Kynn Jones wrote: > ... > > 3. Why not write: > > CREATE VIEW txt AS > SELECT a1.word AS word1, a1.type AS type1, a2.word AS word2, > a2.type AS type2 > FROM T a1 [LEFT] JOIN T a2 USING( zipk ); -- Use "LEFT" if > appropriate > SELECT word1, word1 > FROM S JOIN txt ON word = word1 > WHERE type1 = AND type2 = ; > > > This is would indeed produce the same results as Q1, but this approach > would require defining about 10,000 views, one for each possible pair > of int1 and int2 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 = AND a2.type = ; -- Dean -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce. --------------040909020005000208030009 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 2008-02-23 08:21, Kynn Jones wrote:

...

3. Why not write:

CREATE VIEW txt AS SELECT a1.word AS word1, a1.type AS type1, a2.word AS word2, a2.type AS type2 FROM T a1 [LEFT] JOIN T a2 USING( zipk ); -- Use "LEFT" if appropriate SELECT word1, word1 FROM S JOIN txt ON word = word1 WHERE type1 = <int1> AND type2 = <int2>;

This is would indeed produce the same results as Q1, but this approach would require defining about 10,000 views, one for each possible pair of int1 and int2

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

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

--------------040909020005000208030009--