BUG #19007: Planner fails to choose partial index with spurious 'not null'

The following bug has been logged on the website:

Bug reference:      19007
Logged by:          Bryan Fox
Email address:      bryfox@gmail.com
PostgreSQL version: 17.5
Operating system:   Linux; macOS
Description:

I'm not sure if this is a bug, but the behavior was unexpected to me and
changed since v16. Documentation doesn't mention this as far as I can see.

This example has a partial index over one column where another column is not
null. The latter column is in fact 'not null' in the schema.

Prior to v17, this index would be used; in v17, the planner will choose a
sequential scan instead.

Of course, this setup is a little silly and easy to remedy. In reality, we
had a more complicated index and the column was nullable; later, someone
made a column 'not null'; later, we upgraded to v17. `last_idx_scan` did
make this easier to spot, though.


-- setup
create table example (id int, value float not null, flag bool not null);

insert into example
    select generate_series(1, 100_000) id, random() value, true flag;

create index new_idx on example using btree (value) where flag is not null;

-- query
explain analyze
select * from example where value < 0.1 and flag is not null;


v17 plan:
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Seq Scan on example  (cost=0.00..1887.00 rows=33333 width=13) (actual
time=0.010..5.816 rows=9951 loops=1)
   Filter: (value < '0.1'::double precision)
   Rows Removed by Filter: 90049


v16 plan:
                                                       QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on example  (cost=625.34..1676.92 rows=33167 width=13)
(actual time=1.023..2.710 rows=9979 loops=1)
   Recheck Cond: ((value < '0.1'::double precision) AND (flag IS NOT NULL))
   Heap Blocks: exact=637
   ->  Bitmap Index Scan on new_idx  (cost=0.00..617.04 rows=33167 width=0)
(actual time=0.936..0.937 rows=9979 loops=1)
         Index Cond: (value < '0.1'::double precision)