Re: Index INCLUDE vs. Bitmap Index Scan

> On 27.02.2019, at 02:00, Justin Pryzby <pryzby(at)telsasoft(dot)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 the execution 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