Skip site navigation (1) Skip section navigation (2)

Re: Q on views and performance

From: "Dean Gibson (DB Administrator)" <postgresql(at)ultimeth(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Q on views and performance
Date: 2008-02-23 16:49:13
Message-ID: 47C04E89.3030300@ultimeth.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

Responses

pgsql-performance by date

Next:From: Dean Gibson (DB Administrator)Date: 2008-02-23 16:55:42
Subject: Re: Q on views and performance
Previous:From: Kynn JonesDate: 2008-02-23 16:21:49
Subject: Re: Q on views and performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group