Subject: Re: Wrapping a where clause to preserve rows with nulls

From: "David G. Johnston" <[email-masked]>
Date: Wed, 18 Dec 2024 21:14:43 -0700

On Wednesday, December 18, 2024, Adrian Garcia Badaracco <
[email-masked]> wrote:

>
> Is there any way to include the rows where the predicate evaluates to null
> while still using an index?
>

That seems quite unlikely.  Your definition of equality is incompatible
with the system’s standard definition while requiring that the standard
equals operator be used in the query.  Null values and non-null values are
not considered equal, or, put another way, always considered distinct from
each other.  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.

David J.

---

From: Tom Lane <[email-masked]>
Date: Wed, 18 Dec 2024 23:38:08 -0500

> ... A btree index, which handles =, can’t be told to behave

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

---

From: Adrian Garcia Badaracco <[email-masked]>
Date: Wed, 18 Dec 2024 22:41:58 -0600

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/f92442ea8e8944c78f8e40d6648d049ff8e335ec/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).

---

From: Adrian Garcia Badaracco <[email-masked]>
Date: Thu, 19 Dec 2024 00:47:50 -0600

Well, there is a wrinkle: if the predicate returns `false` but one of the
columns is null then the whole thing ends up `true` when I'd want it to be
`false`. Say col_a = [1] and col_b = [null]:

WHERE (col_a < 1 AND col_b > 1) OR col_a IS NULL OR col_b IS NULL -> WHERE
(false AND null) OR false OR true -> WHERE false OR false OR true -> true.

That's still a pretty good solution for now.

On Wed, Dec 18, 2024 at 10:41 PM Adrian Garcia Badaracco <
[email-masked]> wrote:

> 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: