Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 6C8C42E00CD
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sat, 23 Feb 2008 11:08:39 -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 00367-08 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sat, 23 Feb 2008 11:08:26 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mail.mailpen.net (mail.mailpen.net [209.90.228.14])
	by postgresql.org (Postfix) with ESMTP id F0B002E011A
	for <pgsql-performance@postgresql.org>;
	Sat, 23 Feb 2008 11:08:34 -0400 (AST)
Received: from [172.24.2.1] (pool-71-113-24-134.sttlwa.dsl-w.verizon.net
	[71.113.24.134])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.mailpen.net (Postfix) with ESMTP id C53232FE802
	for <pgsql-performance@postgresql.org>;
	Sat, 23 Feb 2008 07:08:32 -0800 (PST)
Message-ID: <47C03709.5010205@ultimeth.com>
Date: Sat, 23 Feb 2008 07:08:57 -0800
From: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Organization: UltiMeth Systems
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Q on views and performance
References: <c2350ba40802221249h37913d0cyaa6387a93c55fe69@mail.gmail.com>	
	<47BF7B80.1040309@ultimeth.com>
	<c2350ba40802230559l280eab73k8e79a8bda7a4994a@mail.gmail.com>
In-Reply-To: <c2350ba40802230559l280eab73k8e79a8bda7a4994a@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------070603060207030302040900"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/295
X-Sequence-Number: 28837

This is a multi-part message in MIME format.
--------------070603060207030302040900
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 2008-02-23 05:59, Kynn Jones wrote:
> On Fri, Feb 22, 2008 at 8:48 PM, Dean Gibson (DB Administrator) 
> <postgresql@ultimeth.com <mailto:postgresql@ultimeth.com>> wrote:
>
>     ...
>
>
> Since you have experience working with views, let me ask you this. 
>  The converse strategy to the one I described originally would be to 
> create the individual tables T1, T2, T3, ..., T100, but instead of 
> keeping around the original (and now redundant) table T, replace it 
> with a view V made up of the union of T1, T2, T3, ..., T100.  The 
> problem with this alternative is that one cannot index V, or define a 
> primary key constraint for it, because it's a view.  This means that a 
> search in V, even for a primary key value, would be *have to be* very 
> inefficient (i.e. I don't see how even the very clever PostgreSQL 
> implementers could get around this one!), because the engine would 
> have to search *all* the underlying tables, T1 through T100, even if 
> it found the desired record in T1, since it has no way of knowing that 
> the value is unique all across V.
>
> Is there a way around this?
>
> kynn
>
Oh, I wouldn't create separate tables and do a UNION of them, I'd think 
that would be inefficient.

I didn't look in detail at your previous eMail, but I will now:

1. You haven't told us the distribution of "zipk", or what the tables 
are indexed on, or what type of performance you are expecting.  Your 
initial examples don't help much unless you actually have performance 
numbers or EXPLAIN output for them, since adding the third JOIN 
significantly changes the picture, as does changing one of the JOINs to 
a LEFT JOIN.

2. In your actual (Q1** and Q2**) examples, why is one JOIN an INNER 
JOIN and the other one a LEFT JOIN?  Given your description of Q1 at the 
top of your message, that doesn't make sense to me.

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>;

If either of those (either with or without the "LEFT") are not 
equivalent to your problem, how about just:

SELECT a1.word AS word1, a2.word AS word2
  FROM S JOIN T a1 USING( word)
    [LEFT] JOIN T a2 USING( zipk )  -- Use "LEFT" if appropriate
  WHERE a1.type = <int1> AND a2.type = <int2>;

Show us (using EXPLAIN) what the query planner thinks of each of these.

-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.


--------------070603060207030302040900
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
On 2008-02-23 05:59, Kynn Jones wrote:
<blockquote
 cite="mid:c2350ba40802230559l280eab73k8e79a8bda7a4994a@mail.gmail.com"
 type="cite">
  <div class="gmail_quote">On Fri, Feb 22, 2008 at 8:48 PM, Dean Gibson
(DB Administrator) &lt;<a moz-do-not-send="true"
 href="mailto:postgresql@ultimeth.com">postgresql@ultimeth.com</a>&gt;
wrote:<br>
  <blockquote class="gmail_quote"
 style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">...</blockquote>
  <div><br class="webkit-block-placeholder">
  </div>
  <div>Since you have experience working with views, let me ask you
this.  The converse strategy to the one I described originally would be
to create the individual tables T1, T2, T3, ..., T100, but instead of
keeping around the original (and now redundant) table T, replace it
with a view V made up of the union of T1, T2, T3, ..., T100.  The
problem with this alternative is that one cannot index V, or define a
primary key constraint for it, because it's a view.  This means that a
search in V, even for a primary key value, would be *have to be* very
inefficient (i.e. I don't see how even the very clever PostgreSQL
implementers could get around this one!), because the engine would have
to search *all* the underlying tables, T1 through T100, even if it
found the desired record in T1, since it has no way of knowing that the
value is unique all across V.</div>
  <div><br class="webkit-block-placeholder">
  </div>
  <div>Is there a way around this?</div>
  <div><br class="webkit-block-placeholder">
  </div>
  <div>kynn</div>
  <div><br>
  </div>
  </div>
</blockquote>
Oh, I wouldn't create separate tables and do a UNION of them, I'd think
that would be inefficient.<br>
<br>
I didn't look in detail at your previous eMail, but I will now:<br>
<br>
1. You haven't told us the distribution of "zipk", or what the tables
are indexed on, or what type of performance you are expecting.  Your
initial examples don't help much unless you actually have performance
numbers or EXPLAIN output for them, since adding the third JOIN
significantly changes the picture, as does changing one of the JOINs to
a LEFT JOIN.<br>
<br>
2. In your actual (Q1** and Q2**) examples, why is one JOIN an INNER
JOIN and the other one a LEFT JOIN?  Given your description of Q1 at
the top of your message, that doesn't make sense to me.<br>
<br>
3. Why not write:<br>
<br>
<tt>CREATE VIEW txt AS<br>
  SELECT a1.word AS word1, a1.type AS type1, a2.word AS word2, a2.type
AS type2<br>
    FROM T a1 [LEFT] JOIN T a2 USING( zipk );  -- Use "LEFT" if
appropriate<br>
SELECT word1, word1<br>
  FROM S JOIN txt ON word = word1</tt><tt><br>
  WHERE type1 = &lt;int1&gt; AND type2 = &lt;int2&gt;;</tt><br>
<br>
If either of those (either with or without the "LEFT") are not
equivalent to your problem, how about just:<br>
<br>
SELECT <tt>a1.word AS word1, a2.word AS word2<br>
  FROM S JOIN T a1 USING( word)<br>
    [LEFT] JOIN T a2 USING( zipk )</tt><tt>  -- Use "LEFT" if
appropriate</tt><br>
<tt>  WHERE a1.type = &lt;int1&gt; AND a2.type = &lt;int2&gt;;<br>
</tt><br>
Show us (using EXPLAIN) what the query planner thinks of each of these.<br>
<br>
<pre class="moz-signature" cols="72">-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.</pre>
</body>
</html>

--------------070603060207030302040900--