Re: Fold NOT IN / <> ALL expressions containing NULL to FALSE

Hi Ilia, folks.

I tested v6 on master (3c5d9d9), and make check passes. All five cases
from Denis's and my last mails now give the same plans and estimates as
master, and the partition key / dump case still works. I also ran
about 3200 random queries against both master and v6 (WHERE, JOIN ON,
HAVING, CASE, FILTER, nested IS [NOT] TRUE/FALSE, NOT/AND/OR, NULL
arrays) and got the same results.

I found one new regression. It is caused by the new IS NOT TRUE
handling: the qual now differs from a partial index predicate written
the same way.

create table pi (a int, b int, c int);
insert into pi select i, i % 100, i % 1000
from generate_series(1, 100000) i;
create index pi_c on pi (c) where (b not in (42, null)) is not true;
analyze pi;
explain (costs off)
select * from pi where (b not in (42, null)) is not true and c = 5;

master: Bitmap Heap Scan on pi
Recheck Cond: ((c = 5) AND ((b <> ALL (...)) IS NOT TRUE))
-> Bitmap Index Scan on pi_c
Index Cond: (c = 5)
v6: Seq Scan on pi
Filter: (c = 5)

This is not a costing choice. With enable_seqscan = off, v6 still
picks the seq scan ("Disabled: true"), so the index can't be used at
all. With EXPLAIN (ANALYZE, BUFFERS) on a warm cache, the query reads
102 buffers on master and 541 on v6, and the gap grows with the
table. The results are the same. It also happens when the
IS NOT TRUE is an arm of an OR, because the whole OR becomes TRUE and
is dropped:

create index pi_c2 on pi (a)
where b = 1 or (b not in (42, null)) is not true;
explain (costs off) select * from pi
where (b = 1 or (b not in (42, null)) is not true) and a = 5;

master: Index Scan using pi_c2 on pi
v6: Seq Scan on pi

simplify_qual_null_saops() turns the IS NOT TRUE into TRUE and removes
it from the qual. get_relation_info() simplifies the index predicate
with plain eval_const_expressions(), so the predicate keeps the IS NOT
TRUE, and predicate_implied_by() can't prove it from the remaining
quals. Folding a NOT IN arm out of an OR doesn't have this problem.
"b = 1 or b not in (42, null)" as both predicate and qual still uses
the index on v6, because the remaining arm implies the predicate.

Regards,
--
Rustam Allakov