Re: Index INCLUDE vs. Bitmap Index Scan

Markus Winand <markus(dot)winand(at)winand(dot)at> writes:
>> 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:
>>> (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).

Other database products don't have the wide range of index types that
we do. The concepts you propose using are pretty much meaningless
for non-btree indexes. EXPLAIN doesn't really know which of the index
conditions will usefully cut down the index search space for the
particular type, so it just lists everything that has the right form
to be passed to the index AM.

Note that passing a condition to the AM, rather than executing it as
a filter, is generally a win when possible even if it fails to cut
the portion of the index searched at all. That's because it can save
visits to the heap (tying back to the original point in this thread,
that we test index conditions, then heap liveness check, then filter
conditions). So the planner is aggressive about pushing things into
that category when it can.

It might help to point out that to be an index condition, a WHERE
clause has to meet tighter conditions than just whether it mentions
an index column. It generally has to be of the form "index_column
indexable_operator pseudo_constant" (though some index types support
some other cases like "index_column IS NULL" as index conditions too).
Clauses mentioning INCLUDE columns fail this test a priori, because
there are no indexable operators associated with an INCLUDE column.

regards, tom lane