MIME-Version: 1.0
References: <CAE8z92GN0-5J=4q6RSsPGk_OP-Y_EHVNgQimTWwcq+BE+HGqRQ@mail.gmail.com>
 <CAKFQuwa2jcBnsZxHmNn7_1MjcmOZx_fU93epBx9+orw3yMNgKw@mail.gmail.com> <3062509.1734583088@sss.pgh.pa.us>
In-Reply-To: <3062509.1734583088@sss.pgh.pa.us>
From: Adrian Garcia Badaracco <adrian@adriangb.com>
Date: Wed, 18 Dec 2024 22:41:58 -0600
Message-ID: <CAE8z92FTVnCfbS54F01st0QxeLMsgt1mcafnQAW94h-=6-sZ4g@mail.gmail.com>
Subject: Re: Wrapping a where clause to preserve rows with nulls
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, 
	"pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000026ccf206299827b0"
Archived-At: <https://www.postgresql.org/message-id/CAE8z92FTVnCfbS54F01st0QxeLMsgt1mcafnQAW94h-%3D6-sZ4g%40mail.gmail.com>
Precedence: bulk

--00000000000026ccf206299827b0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Thank you for the great idea Tom. While yes I can't modify the original
WHERE clause I do think I'll be able to introspect it or get the system
generating it to tell me which columns it references and then add an OR x
is NULL OR y is NULL ...

For context, just in case it's interesting, I store Parquet statistics in a
Postgres table and run the output of this thing on them:
https://github.com/apache/datafusion/blob/f92442ea8e8944c78f8e40d6648d049ff=
8e335ec/datafusion/physical-optimizer/src/pruning.rs#L146-L456
Hence why I can't really control the WHERE clause (at least not without
re-implementing a bunch of finicky error prone code).

On Wed, Dec 18, 2024 at 10:38=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote=
:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Wednesday, December 18, 2024, Adrian Garcia Badaracco <
> > adrian@adriangb.com> wrote:
> >> Is there any way to include the rows where the predicate evaluates to
> null
> >> while still using an index?
>
> > ... A btree index, which handles =3D, can=E2=80=99t be told to behave
> > differently and so cannot fulfill your desire to produce rows where the
> > stored value is null; it can only produce those equal to 5000.
>
> Not in a single scan, no.  But multiple scans are possible:
>
> regression=3D# create table t (id int unique);
> CREATE TABLE
> regression=3D# explain select * from t where id =3D 5000 or id is null;
>                                   QUERY PLAN
>
>
> -------------------------------------------------------------------------=
-----
>  Bitmap Heap Scan on t  (cost=3D8.42..18.98 rows=3D14 width=3D4)
>    Recheck Cond: ((id IS NULL) OR (id =3D 5000))
>    ->  BitmapOr  (cost=3D8.42..8.42 rows=3D14 width=3D0)
>          ->  Bitmap Index Scan on t_id_key  (cost=3D0.00..4.25 rows=3D13
> width=3D0)
>                Index Cond: (id IS NULL)
>          ->  Bitmap Index Scan on t_id_key  (cost=3D0.00..4.16 rows=3D1
> width=3D0)
>                Index Cond: (id =3D 5000)
> (7 rows)
>
> The OP was quite unclear about what semantics he wants for
> multiple-variable WHERE clauses, but maybe something like this
> would work:
>
> WHERE (original-clause) OR x IS NULL OR y IS NULL OR ...
>
> where each variable mentioned in original-clause is allowed
> to also be NULL.  Or perhaps what is wanted is
>
> WHERE (original-clause) OR (x IS NULL AND y IS NULL AND ...)
>
> ??
>
>                         regards, tom lane
>

--00000000000026ccf206299827b0
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Thank you for the great idea Tom. While yes I can&#39;t mo=
dify the original WHERE clause I do think I&#39;ll be able to introspect it=
 or get the system generating it to tell me which columns it references and=
 then add an OR x is NULL OR y is NULL ...<div><br></div><div>For context, =
just in case it&#39;s interesting, I store Parquet statistics in a Postgres=
 table and run the output of this thing on them:=C2=A0<a href=3D"https://gi=
thub.com/apache/datafusion/blob/f92442ea8e8944c78f8e40d6648d049ff8e335ec/da=
tafusion/physical-optimizer/src/pruning.rs#L146-L456">https://github.com/ap=
ache/datafusion/blob/f92442ea8e8944c78f8e40d6648d049ff8e335ec/datafusion/ph=
ysical-optimizer/src/pruning.rs#L146-L456</a></div><div>Hence why I can&#39=
;t really control the WHERE clause (at least not without re-implementing a =
bunch of finicky=C2=A0error prone code).</div></div><br><div class=3D"gmail=
_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed,=
 Dec 18, 2024 at 10:38=E2=80=AFPM Tom Lane &lt;<a href=3D"mailto:tgl@sss.pg=
h.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex">&quot;David G. Johnston&quot; &lt;<a href=3D"mail=
to:david.g.johnston@gmail.com" target=3D"_blank">david.g.johnston@gmail.com=
</a>&gt; writes:<br>
&gt; On Wednesday, December 18, 2024, Adrian Garcia Badaracco &lt;<br>
&gt; <a href=3D"mailto:adrian@adriangb.com" target=3D"_blank">adrian@adrian=
gb.com</a>&gt; wrote:<br>
&gt;&gt; Is there any way to include the rows where the predicate evaluates=
 to null<br>
&gt;&gt; while still using an index?<br>
<br>
&gt; ... A btree index, which handles =3D, can=E2=80=99t be told to behave<=
br>
&gt; differently and so cannot fulfill your desire to produce rows where th=
e<br>
&gt; stored value is null; it can only produce those equal to 5000.<br>
<br>
Not in a single scan, no.=C2=A0 But multiple scans are possible:<br>
<br>
regression=3D# create table t (id int unique);<br>
CREATE TABLE<br>
regression=3D# explain select * from t where id =3D 5000 or id is null;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 <br>
---------------------------------------------------------------------------=
---<br>
=C2=A0Bitmap Heap Scan on t=C2=A0 (cost=3D8.42..18.98 rows=3D14 width=3D4)<=
br>
=C2=A0 =C2=A0Recheck Cond: ((id IS NULL) OR (id =3D 5000))<br>
=C2=A0 =C2=A0-&gt;=C2=A0 BitmapOr=C2=A0 (cost=3D8.42..8.42 rows=3D14 width=
=3D0)<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt;=C2=A0 Bitmap Index Scan on t_id_key=
=C2=A0 (cost=3D0.00..4.25 rows=3D13 width=3D0)<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (id IS N=
ULL)<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt;=C2=A0 Bitmap Index Scan on t_id_key=
=C2=A0 (cost=3D0.00..4.16 rows=3D1 width=3D0)<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (id =3D =
5000)<br>
(7 rows)<br>
<br>
The OP was quite unclear about what semantics he wants for<br>
multiple-variable WHERE clauses, but maybe something like this<br>
would work:<br>
<br>
WHERE (original-clause) OR x IS NULL OR y IS NULL OR ...<br>
<br>
where each variable mentioned in original-clause is allowed<br>
to also be NULL.=C2=A0 Or perhaps what is wanted is<br>
<br>
WHERE (original-clause) OR (x IS NULL AND y IS NULL AND ...)<br>
<br>
??<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--00000000000026ccf206299827b0--