Thread: "Recheck conditions" on indexes

Hi,

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.

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

On 25/10/2013 11:06, Albe Laurenz wrote:

> 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.

Understood.

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

It doesn't help in this case - the plan for the same query on a copy of
the database on 9.3 is exactly the same.

Ivan Voras <ivoras@freebsd.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