Content-Type: multipart/alternative;
 boundary="------------H0hF0qNU0oVZB23VorrmUYVp"
Message-ID: <13ebf00f-2991-43c5-5d11-a8216cb3217a@thax.hardliners.org>
Date: Sun, 25 Aug 2024 19:04:32 +0200
MIME-Version: 1.0
Subject: Re: Non-trivial condition is only propagated to one side of JOIN
Content-Language: en-US, de-DE
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
References: <8520950a-272c-9cc6-de29-2993b4227bf3@thax.hardliners.org>
 <CAKFQuwZ3jMCfy=cMS5BrzVR7M-ZN2N_2Ocgh+6Rj89f6=an-ZA@mail.gmail.com>
From: Tobias Hoffmann <ldev-list@thax.hardliners.org>
In-Reply-To: <CAKFQuwZ3jMCfy=cMS5BrzVR7M-ZN2N_2Ocgh+6Rj89f6=an-ZA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/13ebf00f-2991-43c5-5d11-a8216cb3217a%40thax.hardliners.org>
Precedence: bulk

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

On 25/08/2024 17:35, David G. Johnston wrote:
> On Sunday, August 25, 2024, Tobias Hoffmann 
> <ldev-list@thax.hardliners.org> wrote:
>
>
>     3) Problematic example:
>
>     # EXPLAIN SELECT * FROM tbl1 JOIN tbl2 ON tbl2.site_id =
>     tbl1.site_id WHERE tbl1.site_id = 1 OR tbl1.site_id IS NULL; 
>
>
> The “is null” predicate in this query is doing nothing as your next 
> comment alludes to; you will produce no rows out of the join with a 
> null site_id due to the use of the equals operator in the join.


Well, that's why I said: "keep in mind that this example is as 
simplified as possible"...
Even though `tbl1.site_id = 1` is – in this case – completely equivalent 
to `tbl1.site_id = 1 OR tbl1.site_id IS NULL` – the first one is 
completely pushed down, but the second is not.

A more complete example might look more like this:

CREATE VIEW "subview1" AS
   SELECT tbl1.site_id, ... JOIN ... ON tbl1.site_id = tbl2.site_id 
WHERE ...;

CREATE VIEW "view1" AS
   SELECT site_id, ... FROM subview1  -- maybe even: WHERE site_id IS 
NOT NULL
   UNION ALL
   SELECT null, ...;

SELECT * FROM view1 WHERE (site_id = 1 OR site_id IS NULL);

The reason, why the outer query would have a more complicated condition 
might have nothing to do with the subquery containing the JOIN.
(This is also not a `UNION ALL` special case: `site_id IS NULL` could 
also be generated by a LEFT JOIN, e.g.)

But not pushing down the condition has the grave impact, that those - 
otherwise working VIEWs (i.e. subview1) become "unfixably" broken, for 
certain WHERE-conditions on the outside.

Another reason why I said `site_id INTEGER NOT NULL` and `IS NOT 
DISTINCT FROM`, is that there might be some mathematical reason I'm not 
yet aware of where the propagation would not be sound, but which would 
not apply for arbitrary site_id [nullable, ...].
My primary goal is to find at least *some* way to get the condition 
pushed further in to avoid the full table scan, and to not have to 
completely rewrite all the VIEWs into a single big query, where I could 
inject the site_id parameter (e.g. "$1") in multiple places as needed...

   Tobias


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

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 25/08/2024 17:35, David G. Johnston
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAKFQuwZ3jMCfy=cMS5BrzVR7M-ZN2N_2Ocgh+6Rj89f6=an-ZA@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      On Sunday, August 25, 2024, Tobias Hoffmann &lt;<a
        href="mailto:ldev-list@thax.hardliners.org"
        moz-do-not-send="true" class="moz-txt-link-freetext">ldev-list@thax.hardliners.org</a>&gt;
      wrote:<br>
      <blockquote class="gmail_quote" style="margin:0 0 0
        .8ex;border-left:1px #ccc solid;padding-left:1ex"><br>
        3) Problematic example:<br>
        <br>
        # EXPLAIN SELECT * FROM tbl1 JOIN tbl2 ON tbl2.site_id =
        tbl1.site_id WHERE tbl1.site_id = 1 OR tbl1.site_id IS NULL; </blockquote>
      <div><br>
      </div>
      <div>The “is null” predicate in this query is doing nothing as
        your next comment alludes to; you will produce no rows out of
        the join with a null site_id due to the use of the equals
        operator in the join.</div>
    </blockquote>
    <p><br>
    </p>
    <p>Well, that's why I said: "keep in mind that this example is as
      simplified as possible"...<br>
      Even though `tbl1.site_id = 1` is – in this case – completely
      equivalent to `tbl1.site_id = 1 OR tbl1.site_id IS NULL` – the
      first one is completely pushed down, but the second is not.</p>
    <p>A more complete example might look more like this:</p>
    <p>CREATE VIEW "subview1" AS<br>
        SELECT tbl1.site_id, ... JOIN ... ON tbl1.site_id = tbl2.site_id
      WHERE ...;  <br>
    </p>
    <p>CREATE VIEW "view1" AS<br>
        SELECT site_id, ... FROM subview1  -- maybe even: WHERE site_id
      IS NOT NULL<br>
        UNION ALL<br>
        SELECT null, ...;<br>
      <br>
      SELECT * FROM view1 WHERE (site_id = 1 OR site_id IS NULL);</p>
    <p>The reason, why the outer query would have a more complicated
      condition might have nothing to do with the subquery containing
      the JOIN.<br>
      (This is also not a `UNION ALL` special case: `site_id IS NULL`
      could also be generated by a LEFT JOIN, e.g.)<br>
    </p>
    <p>But not pushing down the condition has the grave impact, that
      those - otherwise working VIEWs (i.e. subview1) become "unfixably"
      broken, for certain WHERE-conditions on the outside.</p>
    <p>Another reason why I said `site_id INTEGER NOT NULL` and `IS NOT
      DISTINCT FROM`, is that there might be some mathematical reason
      I'm not yet aware of where the propagation would not be sound, but
      which would not apply for arbitrary site_id [nullable, ...].<br>
      My primary goal is to find at least *some* way to get the
      condition pushed further in to avoid the full table scan, and to
      not have to completely rewrite all the VIEWs into a single big
      query, where I could inject the site_id parameter (e.g. "$1") in
      multiple places as needed...<br>
    </p>
    <p>  Tobias<br>
    </p>
    <br>
  </body>
</html>

--------------H0hF0qNU0oVZB23VorrmUYVp--