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)
