Re: Bitmap index scans use of filters on available columns

On Wed, Nov 4, 2015 at 7:14 AM, 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.

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