Re: Index INCLUDE vs. Bitmap Index Scan

> 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.

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”.

The second one postpones this condition to the Bitmap Heap Scan.

QUERY PLAN
------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbl (cost=4.14..8.16 rows=1 width=4) (actual time=0.023..0.028 rows=8 loops=1)
Recheck Cond: (a = 1)
Filter: ((1.0 / (c)::numeric) > 0.1)
Heap Blocks: exact=2
Buffers: shared hit=3
-> Bitmap Index Scan on idx (cost=0.00..4.14 rows=1 width=0) (actual time=0.007..0.007 rows=8 loops=1)
Index Cond: (a = 1)
Buffers: shared hit=1
Planning Time: 0.053 ms
Execution Time: 0.044 ms

I’ve never noticed that behaviour before, but if it is there to prevent the exception-on-dead-tuple problem, the same could be applied to INCLUDE columns?

I realise that this will not cover all use cases I can imagine but it would be consistent for key and non-key columns.

-markus