Bitmap index scans use of filters on available columns - Mailing list pgsql-hackers

create table f as select (random()*100)::int as x, md5(random()::text)
as y from generate_series(1,1000000);

create index on f (x, y);
analyze verbose f; --dont vacuum

explain select * from f where x=5 and y like '%abc%';
                                 QUERY PLAN
------------------------------------------------------------------------------Bitmap Heap Scan on f
(cost=382.67..9314.72rows=1 width=37)  Recheck Cond: (x = 5)  Filter: (y ~~ '%abc%'::text)  ->  Bitmap Index Scan on
f_x_y_idx (cost=0.00..382.67 rows=10433 width=0)        Index Cond: (x = 5)
 

Is there a fundamental reason the filter on y is not being applied to
the index scan, rather than the heap scan?  Should this be a to-do
item?  This could avoid a lot of heap block reads, and also might
prevent the bitmap from overflowing work_mem and turning lossy.

Cheers,

Jeff