Re: Bitmap index scans use of filters on available columns

On 4 November 2015 at 16:14, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > On 4 November 2015 at 15:54, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > We generate this plan
> > Index Scan using f_x_y_idx on f (cost=0.42..26075.71 rows=209 width=37)
> > Index Cond: (x = 5)
> > Filter: (y ~~ '%abc%'::text)
>
> > So it should be possible to do the Filter condition on the
> BitmapIndexScan.
>
> You're missing my point: that is possible in an indexscan, but *not* in a
> bitmap indexscan, because the index AM APIs are totally different in the
> two cases. In a bitmap scan, nothing more than a TID bitmap is ever
> returned out to anyplace that could execute arbitrary expressions.
>

Still misunderstanding each other... sorry about that

If a btree can Filter y like that on an IndexScan, then it can also apply
that Filter on y when it is looking through rows before it adds them to the
bitmap.

I completely understand that it cannot return the value (of y) via the
bitmap.

We should be able to get a plan like this

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)
-> Bitmap Index Scan on f_x_y_idx (cost=0.00..382.67 rows=10433
width=0)
Index Cond: (x = 5)
>> Filter: (y ~~ '%abc%'::text)

if the bitmap stays non-lossy.

I see that plannodes.h says
"In a BitmapIndexScan plan node, the targetlist and qual fields are not
used and are always NIL. "
but it doesn't say why.

Why can't the BitmapIndexScan execute arbitrary expressions? (or What did
you mean by that phrase?)

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services