Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 9DC562E00C1 for ; Sat, 23 Feb 2008 12:21:54 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 21747-05 for ; Sat, 23 Feb 2008 12:21:50 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.179]) by postgresql.org (Postfix) with ESMTP id 8C9172E00FA for ; Sat, 23 Feb 2008 12:21:50 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so997753waf.5 for ; Sat, 23 Feb 2008 08:21:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=V+o/n4WehaY6LOhIrComJFJYy/44MVvBcR4bxP75yUw=; b=Gk5rJZcn+dT5mnWSuNVD8W1XaFGgiMiPwAb+sHO3rJ7/fipJ9NVJIOtmjEHPIdMsc2BRt93sR1XXqLIqgACglavxycOqZ277NQxEGi/jI9Uvnrx2ewlCBW3n2KQi/rV7cyEUgeQwN8+fZ/nBEniy1IExOtKD0sjN6tebFW7w9W4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=pnkBvNHXsWvMZeTfVKQQ9lCZhPHNXRUAm/vP2aeVgJJaC1ZYXuo8szhuGaTRP1Vlxwszt0jOYppFNeMdKnOqj8Bk4hKMGFHqyaDFsQCbWwixM7T4FkuxV+o2xWwecg6K5wYyxl9f6Mi9mq7PRT4JiwddhpaoKA8UkWoDEZcC+Wg= Received: by 10.115.95.1 with SMTP id x1mr703742wal.94.1203783709820; Sat, 23 Feb 2008 08:21:49 -0800 (PST) Received: by 10.114.176.19 with HTTP; Sat, 23 Feb 2008 08:21:49 -0800 (PST) Message-ID: Date: Sat, 23 Feb 2008 11:21:49 -0500 From: "Kynn Jones" To: "Dean Gibson (DB Administrator)" Subject: Re: Q on views and performance Cc: pgsql-performance@postgresql.org In-Reply-To: <47C03709.5010205@ultimeth.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5966_11725063.1203783709813" References: <47BF7B80.1040309@ultimeth.com> <47C03709.5010205@ultimeth.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/297 X-Sequence-Number: 28839 ------=_Part_5966_11725063.1203783709813 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ("concept key" for short). So, if it were indeed a multilingual dictionary, records in T would look like word | zipk | language ---------+------+----------- house | 1234 | casa | 1234 | haus | 1234 | piano | 2345 | piano | 2345 | cat | 3456 | chat | 3456 | chat | 4567 | plausch | 4567 | ...where I used the notation to denote "the integer id assigned to language lang". Therefore typically there are about 100 records in T for any given zipk, one for each language. But the correspondence is not perfect, since, for example, some languages have, proverbially, more than one word for snow, and some (maybe from some tropical island in the South Pacific) have none. (This last case, BTW, is what accounts for the use of left joins, as will become clear in a minute.) The table S can be thought of a table consisting of a collection of words to be translated to some target language. In the first type of query (Q1), all the words in S are effectively declared to belong to the same source language, whereas in the second type of query (Q2) the source language for the words in S is left unspecified (in this case S may contain words from various languages, or words--like "piano" or "chat" in the example above--that belong simultaneously to different languages, and which may (e.g. piano) or may not (e.g. chat) have the same zipk [concept key] for each of these languages). So, regarding your question about (Q1**) and (Q2**): (Q1**) SELECT a1.word, sq.word FROM S JOIN T a1 USING ( word ) LEFT JOIN ( SELECT * FROM T a2 WHERE a2.type = ) sq USING ( zipk ) WHERE a1.type = ; (Q2**) SELECT a1.word, sq.word FROM S JOIN T a1 USING ( word ) LEFT JOIN ( SELECT * FROM T a2 WHERE a2.type = ) sq USING ( zipk ) ...the inner join with S is intended to pick out all the records in the source table (either T in Q1** or T in Q2**) corresponding to words in S, while the second (left) join, is there to find all the "translations" in the target language. I use a left join so that even those words in S for which no translations exist will show up in the query results. 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 = AND type2 = ; > 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 (or pair of languages, to continue the multilingual dictionary analogy), which freaks me out for some reason. (Actually, the number of such views would be many more than that, because in the actual application there is not just one T but several dozen, similar to what would happen to the schema in the multilingual dictionary analogy if we wanted to pre-segregate the words according to some categories, say a T for animals, a T for fruits, a T for verbs, a T for professions, etc.) (I need to do a bit more work before I can post the EXPLAIN results.) kynn ------=_Part_5966_11725063.1203783709813 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ("concept key" for short).  So, if it were indeed a multilingual dictionary, records in T would look like

  word   | zipk | language
---------+------+-----------
 house   | 1234 | <english>
 casa    | 1234 | <spanish>
 haus    | 1234 | <german>
 piano   | 2345 | <english>
 piano   | 2345 | <spanish>
 cat     | 3456 | <english>
 chat    | 3456 | <french>
 chat    | 4567 | <english>
 plausch | 4567 | <german>

...where I used the notation <lang> to denote "the integer id assigned to language lang".  Therefore typically there are about 100 records in T for any given zipk, one for each language.  But the correspondence is not perfect, since, for example, some languages have, proverbially, more than one word for snow, and some (maybe from some tropical island in the South Pacific) have none.  (This last case, BTW, is what accounts for the use of left joins, as will become clear in a minute.)

The table S can be thought of a table consisting of a collection of words to be translated to some target language.  In the first type of query (Q1), all the words in S are effectively declared to belong to the same source language, whereas in the second type of query (Q2) the source language for the words in S is left unspecified (in this case S may contain words from various languages, or words--like "piano" or "chat" in the example above--that belong simultaneously to different languages, and which may (e.g. piano) or may not (e.g. chat) have the same zipk [concept key] for each of these languages).

So, regarding your question about (Q1**) and (Q2**):

(Q1**)  SELECT a1.word, sq.word FROM
               S      JOIN T a1 USING ( word )
                 LEFT JOIN ( SELECT * FROM T a2
                             WHERE a2.type = <int2> ) sq USING ( zipk )
         WHERE a1.type = <int1>;

(Q2**)  SELECT a1.word, sq.word FROM
               S      JOIN T a1 USING ( word )
                 LEFT JOIN ( SELECT * FROM T a2
                             WHERE a2.type = <int2> ) sq USING ( zipk )

...the inner join with S is intended to pick out all the records in the source table (either T<int1> in Q1** or T in Q2**) corresponding to words in S, while the second (left) join, is there to find all the "translations" in the target language.  I use a left join so that even those words in S for which no translations exist will show up in the query results.

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 (or pair of languages, to continue the multilingual dictionary analogy), which freaks me out for some reason.  (Actually, the number of such views would be many more than that, because in the actual application there is not just one T but several dozen, similar to what would happen to the schema in the multilingual dictionary analogy if we wanted to pre-segregate the words according to some categories, say a T for animals, a T for fruits, a T for verbs, a T for professions, etc.)

(I need to do a bit more work before I can post the EXPLAIN results.)

kynn

------=_Part_5966_11725063.1203783709813--