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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Dean Gibson (DB Administrator) | 2008-02-23 16:55:42 | Re: Q on views and performance |
Previous Message | Kynn Jones | 2008-02-23 16:21:49 | Re: Q on views and performance |