Bitmap index scans use of filters on available columns

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

Responses

Browse pgsql-hackers by date

  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