Hi Ilia, I checked v3. The direct NOT IN form is folded to false, but some equivalent forms still scan the table:
create table t(a int); insert into t values (1), (42), (null); -- folded to false explain (costs off) select * from t where a not in (42, null); -- these still scan the table explain (costs off) select * from t where not (a in (42, null)); explain (costs off) select * from t where not (a = any (array[42, null])); explain (costs off) select * from t where not not (a not in (42, null)); explain (costs off) select * from t where (a not in (42, null)) = true; create function not_in_null(integer) returns boolean language sql immutable as $$ select $1 not in (42, null) $$; explain (costs off) select * from t where not_in_null(a); It looks like the expression produced after removing NOT or inlining the function does not get another chance to use the new folding. There are also CASE WHEN conditions and aggregate FILTER clauses, where false and null have the same effect: explain (costs off, verbose) select case when a not in (42, null) then 1 else 0 end from t; explain (costs off, verbose) select count(*) filter (where a not in (42, null)) from t; explain (costs off, verbose) select a, count(*) filter (where a not in (42, null)) over () from t; These plans still contain the array comparison. The CASE expression could become 0, and the filters could become false. This does not necessarily mean that the table scan can be removed. Could you also add regression tests, at least for the issues fixed in v3: multidimensional arrays, ON CONFLICT with a partial index, and folding under AND/OR? Currently, the patch only updates the expected output of two existing queries. Tests checking both results and plans would help prevent these issues from coming back. Best regards, Denis Smirnov
