From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Adrian Garcia Badaracco <adrian@adriangb.com>,
        "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: Wrapping a where clause to preserve rows with nulls
In-reply-to: <CAKFQuwa2jcBnsZxHmNn7_1MjcmOZx_fU93epBx9+orw3yMNgKw@mail.gmail.com>
References: <CAE8z92GN0-5J=4q6RSsPGk_OP-Y_EHVNgQimTWwcq+BE+HGqRQ@mail.gmail.com> <CAKFQuwa2jcBnsZxHmNn7_1MjcmOZx_fU93epBx9+orw3yMNgKw@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Wed, 18 Dec 2024 21:14:43 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <3062508.1734583088.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Wed, 18 Dec 2024 23:38:08 -0500
Message-ID: <3062509.1734583088@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/3062509.1734583088%40sss.pgh.pa.us>
Precedence: bulk

"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 =, can’t 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=# create table t (id int unique);
CREATE TABLE
regression=# explain select * from t where id = 5000 or id is null;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=8.42..18.98 rows=14 width=4)
   Recheck Cond: ((id IS NULL) OR (id = 5000))
   ->  BitmapOr  (cost=8.42..8.42 rows=14 width=0)
         ->  Bitmap Index Scan on t_id_key  (cost=0.00..4.25 rows=13 width=0)
               Index Cond: (id IS NULL)
         ->  Bitmap Index Scan on t_id_key  (cost=0.00..4.16 rows=1 width=0)
               Index Cond: (id = 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