From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Bitmap index scans use of filters on available columns |
Date: | 2015-11-03 18:58:32 |
Message-ID: | CAMkU=1x1ObWnPJL9ki7Ky-E2fUKXXevm6A0EL7W_+4MF9fhGcg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2015-11-03 19:15:51 | Re: Minor clarifying changes to abbreviated key abort code comments |
Previous Message | Neil Tiffin | 2015-11-03 18:39:47 | Re: [patch] to build docs on Mac OS X El Capitan with MacPorts |