Re: [PERFORM] Q on views and performance

On Mon, Feb 25, 2008 at 11:56 AM, Matthew <[EMAIL PROTECTED]> wrote: > On Mon, 25 Feb 2008, Kynn Jones wrote: > > This is just GREAT!!! It fits the problem to a tee. > > It makes the queries quick then? It is good that you ask. Clearly you know the story: a brilliant-sounding optimization that

Re: [PERFORM] Q on views and performance

On Mon, 25 Feb 2008, Kynn Jones wrote: This is just GREAT!!! It fits the problem to a tee. It makes the queries quick then? Matthew -- The only secure computer is one that's unplugged, locked in a safe, and buried 20 feet under the ground in a secret location...and i'm not even too sure abou

Re: [PERFORM] Q on views and performance

On Mon, Feb 25, 2008 at 8:45 AM, Matthew <[EMAIL PROTECTED]> wrote: > On Fri, 22 Feb 2008, Kynn Jones wrote: > > Hi. I'm trying to optimize... > > > > (Q1) SELECT a1.word, a2.word > > FROM T a1 JOIN T a2 USING ( zipk ) > >WHERE a1.type = > > AND a2.type = ; > > Okay, t

Re: [PERFORM] Q on views and performance

So, this email is directed much more towards Postgres Powers That Be. I came across this problem a while ago, and I haven't checked whether it has been improved. On Mon, 25 Feb 2008, I wrote: Hi. I'm trying to optimize... (Q1) SELECT a1.word, a2.word FROM T a1 JOIN T a2 USING ( zip

Re: [PERFORM] Q on views and performance

On Fri, 22 Feb 2008, Kynn Jones wrote: Hi. I'm trying to optimize... (Q1) SELECT a1.word, a2.word FROM T a1 JOIN T a2 USING ( zipk ) WHERE a1.type = AND a2.type = ; Okay, try this: Create an index on T(type, zipk), and then CLUSTER on that index. That will effecti

Re: [PERFORM] Q on views and performance

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

Re: [PERFORM] Q on views and 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

Re: [PERFORM] Q on views and performance

Hi, Dean. The system I'm working with is very similar "in spirit" to a large multilingual dictionary covering 100 languages. Using this analogy, the "type" column would correspond to the language, and the zipk column would correspond to some language-independent key associated with a concept ("co

Re: [PERFORM] Q on views and performance

On 2008-02-23 07:08, Dean Gibson (DB Administrator) wrote: ... SELECT word1, word1 FROM S JOIN txt ON word = word1 WHERE type1 = AND type2 = ; ... Oops that should be: SELECT word1, word2 FROM S JOIN txt ON word = word1 WHERE type1 = AND type2 = ; -- Mail to my list address MUST be

Re: [PERFORM] Q on views and performance

On 2008-02-23 05:59, Kynn Jones wrote: On Fri, Feb 22, 2008 at 8:48 PM, Dean Gibson (DB Administrator) <[EMAIL PROTECTED] > wrote: ... Since you have experience working with views, let me ask you this. The converse strategy to the one I described originally wou

Re: [PERFORM] Q on views and performance

Hi Kynn, Lets take these up as cases : Case A: keep one large table T and keep V1 V100 Case B: keep one large table T and store the the same data also in T1...T100 Case C: keep T1...T100 and store one V which is a UNION of T1 ... T100 1. The way I look at it, in case B although fetching dat

Re: [PERFORM] Q on views and performance

On Fri, Feb 22, 2008 at 8:48 PM, Dean Gibson (DB Administrator) < [EMAIL PROTECTED]> wrote: > On 2008-02-22 12:49, Kynn Jones wrote: > > Of course, I expect that using views V and V... would > > result in a loss in performance relative to a version that used bona > > fide tables T and T. My quest

Re: [PERFORM] Q on views and performance

On Fri, Feb 22, 2008 at 8:48 PM, Dean Gibson (DB Administrator) < [EMAIL PROTECTED]> wrote: > On 2008-02-22 12:49, Kynn Jones wrote: > > Of course, I expect that using views V and V... would > > result in a loss in performance relative to a version that used bona > > fide tables T and T. My quest

Re: [PERFORM] Q on views and performance

On 2008-02-22 12:49, Kynn Jones wrote: Of course, I expect that using views V and V... would result in a loss in performance relative to a version that used bona fide tables T and T. My question is, how can I minimize this performance loss? That used to be my thoughts too, but I have found o