Re: Bitmap index scans use of filters on available columns

On Wed, Nov 4, 2015 at 10:59 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 16:14, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> 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.
>
> btree applies no such filter in either case. "Filter" conditions are
> applied outside the index AM --- and yes, I will resist any proposal to
> relocate that responsibility.

I don't understand what you're arguing against here - as Simon I think
correctly points out, there seems to be a substantial performance
improvement possible here. It's not so much that we would apply the
Filter conditions outside the index AM as that we would have two kinds
of Index Quals that could be enforced by the index AM. Some quals
(such as x = 5) could be enforced by scanning only the relevant
portion of the index, while others (such as y like '%abc%') don't
abridge the portion of the index that needs to be scanned, but could
disqualify some index tuples before we go to the trouble of hitting
the heap.

The problem I see is this might involve testing quals against an index
tuple when the corresponding heap tuple isn't visible. This probably
isn't safe except for leakproof quals, and there might be some other
problems as well.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company