Re: Index INCLUDE vs. Bitmap Index Scan

> On 27.02.2019, at 00:22, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Markus Winand <markus.winand@winand.at> writes:
>> I think Bitmap Index Scan should take advantage of B-tree INCLUDE columns, which it doesn’t at the moment (tested on
masteras 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
couldbe 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