Re: Bitmap index scans use of filters on available columns

On Wed, Nov 4, 2015 at 7:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
>> On 4 November 2015 at 15:54, Tom Lane <tgl@sss.pgh.pa.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.

I had thought it must already be able to execute arbitrary
expressions, due to the ability to already support user-defined btree
ops (and ops of non-btree types in the case of other index types).
Does that only require a restricted execution environment?


> In the case at hand, the planner should have considered a plan of this
> shape as well.  Presumably it concluded it was more expensive than using
> the bitmap approach.  Jeff might try "set enable_bitmapscan = 0" and
> compare the estimated and actual costs.

It is a simplified test case, I can get it to do about anything I want
by tweaking the size, selectivity, columns selected, and cache warmth.

My understanding is that the normal (not index-only) index scan, which
is thought to be slower and actually is slower with a hot cache, also
applies the filter on the heap tuple, not the index tuple.  But since
it only matters much for large queries and large queries tend to
prefer bitmaps, I was less interested in that case.  And bitmaps can
feed into BitmapAnd, which is very helpful in avoiding index-creep
where every query gets a new index tailored for it.  I think we are
missing a trick here that could apply to lots of situations.

Cheers,

Jeff