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.
Cheers,
Jeff
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
