Subject: Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause
To: pgsql-performance@postgresql.org
References: <97dbd6f9-a289-94d1-325f-997df87c672a@mail.de>
 <fc49b4e6-8912-2a8d-4f58-7d1ed455cb49@ed.ac.uk>
 <0e86e399-61dc-8e78-415e-c83db9592a80@mail.de>
 <CAMkU=1xkOSS6V=K4w=pcuUmV5Yz81=va6BpZeMyknGS4Sx9woA@mail.gmail.com>
 <a0cde040-44f8-ac0f-c87a-932d2901702e@mail.de>
 <CAMkU=1xDzevE2sUY8GBcrA50_ivvdw5tQHk9OzT0-4MkcbfYKw@mail.gmail.com>
From: "Sven R. Kunze" <srkunze@mail.de>
Message-ID: <cb1e4e2d-101d-cf14-9498-91afc004d8c8@mail.de>
Date: Fri, 30 Sep 2016 13:22:44 +0200
MIME-Version: 1.0
In-Reply-To: 
 <CAMkU=1xDzevE2sUY8GBcrA50_ivvdw5tQHk9OzT0-4MkcbfYKw@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------D084C884BFFF0E9CE270B244"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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

On 29.09.2016 22:26, Jeff Janes wrote:
> Well, I don't recall seeing this issue on this list before (or a few 
> other forums I read) while I see several other issues over and over 
> again.  So that is why I think it is a niche issue.  Perhaps I've have 
> seen it before and just forgotten, or have not recognized it as being 
> the same issue each time.

Understood.

>
>     This multitude of solution also shows that applications developers
>     might be overwhelmed by choosing the most appropriate AND most
>     long-lasting one. Because what I take from the discussion is that
>     a UNION might be appropriate right now but that could change in
>     the future even for the very same use-case at hand.
>
>
> I'm not sure what would cause it to change.  Do you mean if you 
> suddenly start selecting a much larger portion of the table?  I don't 
> know that the union would be particularly bad in that case, either.

Not suddenly but gradually. Data can change and we don't know for sure 
how people will use our systems in the future. Hence, another plan would 
be more optimal or even a seq scan on big_table would be faster.

In the case at hand, I doubt it but you never know.

> I'm not saying it wouldn't be nice to fix it.  I just don't think it 
> is particularly likely to happen soon.  I could be wrong (especially 
> if you can write the code to make it happen).

I have been thinking about this. It would be an interesting exercise as 
I haven't written much of C in the last decade but sometimes one needs 
to get out of the comfort zone to get things going.


Sven

--------------D084C884BFFF0E9CE270B244
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 29.09.2016 22:26, Jeff Janes wrote:<br>
    </div>
    <blockquote
cite="mid:CAMkU=1xDzevE2sUY8GBcrA50_ivvdw5tQHk9OzT0-4MkcbfYKw@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div class="gmail_extra">
          <div class="gmail_quote">Well, I don't recall seeing this
            issue on this list before (or a few other forums I read)
            while I see several other issues over and over again.  So
            that is why I think it is a niche issue.  Perhaps I've have
            seen it before and just forgotten, or have not recognized it
            as being the same issue each time.</div>
        </div>
      </div>
    </blockquote>
    <br>
    Understood.<br>
    <br>
    <blockquote
cite="mid:CAMkU=1xDzevE2sUY8GBcrA50_ivvdw5tQHk9OzT0-4MkcbfYKw@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div class="gmail_extra">
          <div class="gmail_quote">
            <div> </div>
            <blockquote class="gmail_quote" style="margin:0 0 0
              .8ex;border-left:1px #ccc solid;padding-left:1ex">
              <div bgcolor="#FFFFFF" text="#000000"><br>
                This multitude of solution also shows that applications
                developers might be overwhelmed by choosing the most
                appropriate AND most long-lasting one. Because what I
                take from the discussion is that a UNION might be
                appropriate right now but that could change in the
                future even for the very same use-case at hand.<br>
              </div>
            </blockquote>
            <div><br>
            </div>
            <div>I'm not sure what would cause it to change.  Do you
              mean if you suddenly start selecting a much larger portion
              of the table?  I don't know that the union would be
              particularly bad in that case, either.</div>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    Not suddenly but gradually. Data can change and we don't know for
    sure how people will use our systems in the future. Hence, another
    plan would be more optimal or even a seq scan on big_table would be
    faster.<br>
    <br>
    In the case at hand, I doubt it but you never know.<br>
    <br>
    <blockquote
cite="mid:CAMkU=1xDzevE2sUY8GBcrA50_ivvdw5tQHk9OzT0-4MkcbfYKw@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div class="gmail_extra">
          <div class="gmail_quote">
            <div>I'm not saying it wouldn't be nice to fix it.  I just
              don't think it is particularly likely to happen soon.  I
              could be wrong (especially if you can write the code to
              make it happen).</div>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    I have been thinking about this. It would be an interesting exercise
    as I haven't written much of C in the last decade but sometimes one
    needs to get out of the comfort zone to get things going.<br>
    <br>
    <br>
    Sven<br>
  </body>
</html>

--------------D084C884BFFF0E9CE270B244--