MIME-Version: 1.0
References: <CAE8z92GN0-5J=4q6RSsPGk_OP-Y_EHVNgQimTWwcq+BE+HGqRQ@mail.gmail.com>
In-Reply-To: <CAE8z92GN0-5J=4q6RSsPGk_OP-Y_EHVNgQimTWwcq+BE+HGqRQ@mail.gmail.com>
From: Adrian Garcia Badaracco <adrian@adriangb.com>
Date: Wed, 18 Dec 2024 22:12:36 -0600
Message-ID: <CAE8z92FZa5dmnm3k2OQOVsazbuUrdSh8HFrgkU3XM79cxv66tw@mail.gmail.com>
Subject: Re: Wrapping a where clause to preserve rows with nulls
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000208b7f062997be29"
Archived-At: <https://www.postgresql.org/message-id/CAE8z92FZa5dmnm3k2OQOVsazbuUrdSh8HFrgkU3XM79cxv66tw%40mail.gmail.com>
Precedence: bulk

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

I'll note that the clause is arbitrary in the sense that I don't generate
it and cannot edit it but it's basically a bunch of boolean comparisons
chained i.e. `col_a >=3D 1 and col_b <=3D5 and col_c ...` so I can in gener=
al
add an index on say col_a and it does get used.

On Wed, Dec 18, 2024 at 9:47=E2=80=AFPM Adrian Garcia Badaracco <adrian@adr=
iangb.com>
wrote:

> I have a query where I have to run a where clause generated by another
> system (i.e., I can't modify that where clause. The where clause may retu=
rn
> `null`,  but I actually want to keep rows that return `null` (and rows th=
at
> return `true` but not rows that return `false`).
>
> I thought it would be as simple as wrapping in `(...) is not false` but
> that seems to prevent index usage.
>
> For example, let's say that given the table:
>
> CREATE TABLE test_index (value INTEGER);
> CREATE INDEX idx_value ON test_index(value);
>
> And the predicate `value =3D 5000`, if I run the original query that
> excludes rows where the predicate is null it uses the index:
>
> SELECT *
> FROM test_index
> WHERE value =3D 5000;
>
> But as soon as I tack on an `IS NOT FALSE` the index is not used:
>
> SELECT *
> FROM test_index
> WHERE (value =3D 5000) IS NOT FALSE;
>
> This was surprising to me. I was hoping this might be able to use the
> index.
>
> Is there any way to include the rows where the predicate evaluates to nul=
l
> while still using an index?
>
>

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

<div dir=3D"ltr">I&#39;ll note that the clause is arbitrary in the sense th=
at I don&#39;t generate it and cannot edit it but it&#39;s basically a bunc=
h of boolean comparisons chained i.e. `col_a &gt;=3D 1 and col_b &lt;=3D5 a=
nd col_c ...` so I can in general add an index on say col_a and it does get=
 used.</div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=
=3D"ltr" class=3D"gmail_attr">On Wed, Dec 18, 2024 at 9:47=E2=80=AFPM Adria=
n Garcia Badaracco &lt;<a href=3D"mailto:adrian@adriangb.com">adrian@adrian=
gb.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"m=
argin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left=
:1ex"><div dir=3D"ltr">I have a query where I have to run a where clause ge=
nerated by another system (i.e., I can&#39;t modify that where=C2=A0clause.=
 The where clause may return `null`,=C2=A0 but I actually want to keep rows=
 that return `null` (and rows that return `true` but not rows that return=
=C2=A0`false`).<div><br></div><div>I thought it would be as simple as wrapp=
ing in `(...) is not false` but that seems to prevent index usage.</div><di=
v><br></div><div>For example, let&#39;s say that given the table:</div><div=
><br></div><div>CREATE TABLE test_index (value INTEGER);<br>CREATE INDEX id=
x_value ON test_index(value);</div><div><br></div><div>And the predicate `v=
alue =3D 5000`, if I run the original query that excludes rows where the pr=
edicate is null it uses the index:</div><div><br></div><div>SELECT *</div><=
div>FROM test_index</div><div>WHERE value =3D 5000;</div><div><br></div><di=
v>But as soon as I tack on an `IS NOT FALSE` the index is not used:</div><d=
iv><br></div><div><div>SELECT *</div><div>FROM test_index</div><div>WHERE (=
value =3D 5000) IS NOT FALSE;</div></div><div><br></div><div>This was surpr=
ising to me. I was hoping this might be able to use the index.</div><div><b=
r></div><div>Is there any way to include the rows where the predicate evalu=
ates to null while still using an index?</div><div><br></div></div>
</blockquote></div>

--000000000000208b7f062997be29--