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

[PERFORM] Q on views and performance

Hi. I'm trying to optimize the performance of a database whose main purpose is to support two (rather similar) kinds of queries. The first kind, which is expected to be the most common (I estimate it will account for about 90% of all the queries performed on this DB), has the following general st