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.72 rows=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.



Sent via pgsql-hackers mailing list (
To make changes to your subscription:

Reply via email to