I'm trying to speed up a particular query, so I tried out a very
specific index meant to target this one query alone. (I'm not at all
convinced that's a good idea, but I'm curious to see just how fast I
can make this one query.) The index is like this:
create index idx_foo on my_tbl (start_on) where end_on is null and
bar_id is null and population_kind = 2;
The query needs to find rows that are before a certain start_on date
where all of the `where` conditions listed in the index are satisfied.
The query planner insists on using the index to do a bitmap scan:
```
db=# explain select start_on from my_tbl where end_on is null and
bar_id is null and population_kind = 2 and start_on < '2024-11-07';
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on my_tbl (cost=1116.28..329938.13 rows=317919 width=4)
Recheck Cond: ((start_on < '2024-11-07'::date) AND (end_on IS NULL)
AND (bar_id IS NULL) AND (population_kind = 2))
-> Bitmap Index Scan on idx_foo (cost=0.00..1036.81 rows=317919 width=0)
Index Cond: (start_on < '2024-11-07'::date)
JIT:
Functions: 2
Options: Inlining false, Optimization false, Expressions true, Deforming true
(7 rows)
```
Why wouldn't it do an index (or, really, an index only) scan in this
case, given that the index contains exactly the data that the query
needs?
I'm running PG 16.4.
- Jon
