Re: "Recheck conditions" on indexes

Ivan Voras <ivoras(at)freebsd(dot)org> writes:
> I'm just wondering: in the execution plan such as this one, is the
> "Recheck Cond" phase what it apparently looks like: an additional check
> on the data returned by indexes, and why is it necessary?

Bitmap indexscans are potentially lossy. If the bitmap recording all the
tuple locations reported by the index gets too big, we compress entries by
remembering only that a particular page has to be visited, not the precise
tuples on that page. Once this happens, the indexed condition has to be
rechecked at each tuple on the page, once we finally get to the point of
visiting it. The recheck condition isn't used on pages that didn't become
lossy.

Recheck conditions are also used for cases where the index isn't able to
test the query WHERE condition exactly, such as anchored LIKE conditions.
That case doesn't apply to your example, though.

> Also, why is it using the Bitmap Index Scan in both cases?

We don't support ORed index conditions in plain index scans, much less use
of more than one index. The only mechanism that can implement that is a
BitmapOr.

regards, tom lane