MIME-Version: 1.0
From: Adrian Garcia Badaracco <adrian@adriangb.com>
Date: Wed, 18 Dec 2024 21:47:15 -0600
Message-ID: <CAE8z92GN0-5J=4q6RSsPGk_OP-Y_EHVNgQimTWwcq+BE+HGqRQ@mail.gmail.com>
Subject: Wrapping a where clause to preserve rows with nulls
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000007e78eb0629976344"
Archived-At: <https://www.postgresql.org/message-id/CAE8z92GN0-5J%3D4q6RSsPGk_OP-Y_EHVNgQimTWwcq%2BBE%2BHGqRQ%40mail.gmail.com>
Precedence: bulk

--0000000000007e78eb0629976344
Content-Type: text/plain; charset="UTF-8"

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 return
`null`,  but I actually want to keep rows that return `null` (and rows that
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 = 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 = 5000;

But as soon as I tack on an `IS NOT FALSE` the index is not used:

SELECT *
FROM test_index
WHERE (value = 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 null
while still using an index?

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

<div dir=3D"ltr">I have a query where I have to run a where clause generate=
d by another system (i.e., I can&#39;t modify that where=C2=A0clause. The w=
here 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`f=
alse`).<div><br></div><div>I thought it would be as simple as wrapping in `=
(...) is not false` but that seems to prevent index usage.</div><div><br></=
div><div>For example, let&#39;s say that given the table:</div><div><br></d=
iv><div>CREATE TABLE test_index (value INTEGER);<br>CREATE INDEX idx_value =
ON test_index(value);</div><div><br></div><div>And the predicate `value =3D=
 5000`, if I run the original query that excludes rows where the predicate =
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><div>But as=
 soon as I tack on an `IS NOT FALSE` the index is not used:</div><div><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 surprising =
to me. I was hoping this might be able to use the index.</div><div><br></di=
v><div>Is there any way to include the rows where the predicate evaluates t=
o null while still using an index?</div><div><br></div></div>

--0000000000007e78eb0629976344--