Re: "Recheck conditions" on indexes

Ivan Voras wrote:
> 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? I would have
> though that indexes are accurate enough?
>
> cms=> explain analyze select * from users where
> other_ids->'OIB'='70328909364' or code='0023017009';
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------
> ----------------------------
> Bitmap Heap Scan on users (cost=8.52..39.21 rows=10 width=330) (actual
> time=0.042..0.044 rows=2 loops=1)
> Recheck Cond: (((other_ids -> 'OIB'::text) = '70328909364'::text) OR
> ((code)::text = '0023017009'::text))
> -> BitmapOr (cost=8.52..8.52 rows=10 width=0) (actual
> time=0.035..0.035 rows=0 loops=1)
> -> Bitmap Index Scan on users_other_ids_oib (cost=0.00..4.26
> rows=9 width=0) (actual time=0.023..0.023 rows=1 loops=1)
> Index Cond: ((other_ids -> 'OIB'::text) =
> '70328909364'::text)
> -> Bitmap Index Scan on users_code (cost=0.00..4.26 rows=1
> width=0) (actual time=0.012..0.012 rows=1 loops=1)
> Index Cond: ((code)::text = '0023017009'::text)
> Total runtime: 0.082 ms
> (8 rows)
>
> Both indexes are plain btrees, the first one is on the expression on the
> hstore field (other_ids->'OIB') and the second one on a plain text
> field. Also, why is it using the Bitmap Index Scan in both cases? A
> plain query for code='foo' uses a plain index scan.
>
> This is PostgreSQL 9.1.

Just because there is an entry in the index does not imply that the
corresponding table entry is visible for this transaction.
To ascertain that, the table row itself has to be checked.

PostgreSQL 9.2 introduced "index only scan" which avoids that
additional step if it is safe to do so.

Yours,
Laurenz Albe