Re: Index INCLUDE vs. Bitmap Index Scan

On 2/27/19 6:36 AM, Markus Winand wrote:
>
>
>> On 27.02.2019, at 00:22, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>> Markus Winand <markus(dot)winand(at)winand(dot)at> writes:
>>> I think Bitmap Index Scan should take advantage of B-tree INCLUDE columns, which it doesn’t at the moment (tested on master as of yesterday).
>>
>> Regular index scans don't do what you're imagining either (i.e., check
>> filter conditions in advance of visiting the heap). There's a roadblock
>> to implementing such behavior, which is that we might end up applying
>> filter expressions to dead rows. That could make users unhappy.
>> For example, given a filter condition like "1.0/c > 0.1", people
>> would complain if it still got zero-divide failures even after they'd
>> deleted all rows with c=0 from their table.
>
> Ok, but I don’t see how this case different for key columns vs. INCLUDE columns.
>

Yeah, I'm a bit puzzled by this difference too - why would it be safe
for keys and not the other included columns?

> When I test this with the (a, b, c) index (no INCLUDE), different
> plans are produced for "c=1" (my original example) vs. "1.0/c > 0.1”.

I do recall a discussion about executing expressions on index tuples
during IOS (before/without inspecting the heap tuple). I'm too lazy to
search for the thread now, but I recall it was somehow related to
leak-proof-ness. And AFAICS none of the "div" procs are marked as
leak-proof, so perhaps that's one of the reasons?

Of course, this does not explain why equality conditions and such (which
are generally leak-proof) couldn't be moved to the bitmap index scan.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services