Re: Index INCLUDE vs. Bitmap Index Scan

> On 27.02.2019, at 02:00, Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> On Tue, Feb 26, 2019 at 09:07:01PM +0100, Markus Winand wrote:
>> CREATE INDEX idx ON tbl (a, b, c);
>> Bitmap Heap Scan on tbl  (cost=4.14..8.16 rows=1 width=7616) (actual time=0.021..0.021 rows=1 loops=1)
>>   Recheck Cond: ((a = 1) AND (c = 1))
>>   ->  Bitmap Index Scan on idx  (cost=0.00..4.14 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=1)
>>         Index Cond: ((a = 1) AND (c = 1))
>>
>> (As a side node: I also dislike it how Bitmap Index Scan mixes search conditions and filters in “Index Cond”)
>
> I don't think it's mixing them;  it's using index scan on leading *and*
> nonleading column.  That's possible even if frequently not efficient.

The distinction leading / non-leading is very important for performance. Other database products use different names in
theexecution plan so that it is immediately visible (without knowing the index definition). 

- Oracle: access vs. filter predicates
- SQL Server: “seek predicates” vs. “predicates”
- Db2: START/STOP vs. SARG
- MySQL/MariaDB show how many leading columns of the index are used — the rest is just “filtering"

PostgreSQL: no difference visible in the execution plan.

CREATE INDEX idx ON tbl (a,b,c);

EXPLAIN (analyze, buffers)
SELECT *
 FROM tbl
WHERE a = 1
  AND c = 1;

                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl  (cost=4.14..8.16 rows=1 width=7616) (actual time=0.017..0.018 rows=1 loops=1)
   Recheck Cond: ((a = 1) AND (c = 1))
   Heap Blocks: exact=1
   Buffers: shared hit=1 read=1
   ->  Bitmap Index Scan on idx  (cost=0.00..4.14 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=1)
         Index Cond: ((a = 1) AND (c = 1))
         Buffers: shared read=1
 Planning Time: 0.149 ms
 Execution Time: 0.035 ms


DROP INDEX idx;
CREATE INDEX idx ON tbl (a, c, b); -- NOTE: column “c” is second


                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl  (cost=4.14..8.16 rows=1 width=7616) (actual time=0.013..0.013 rows=1 loops=1)
   Recheck Cond: ((a = 1) AND (c = 1))
   Heap Blocks: exact=1
   Buffers: shared hit=1 read=1
   ->  Bitmap Index Scan on idx  (cost=0.00..4.14 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)
         Index Cond: ((a = 1) AND (c = 1))
         Buffers: shared read=1
 Planning Time: 0.262 ms
 Execution Time: 0.036 ms
(9 rows)

-markus