Re: Q on views and performance

["Dean Gibson (DB Administrator)" <postgresql@xxxxxxxxxxxx>]

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.