"Recheck conditions" on indexes

From: Ivan Voras <ivoras(at)freebsd(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: "Recheck conditions" on indexes
Date: 2013-10-25 07:59:50
Message-ID: l4d8dk$5h8$1@ger.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Albe Laurenz 2013-10-25 09:06:25 Re: "Recheck conditions" on indexes
Previous Message Achilleas Mantzios 2013-10-25 07:11:54 Re: (collation) Building postgresql on FreeBSD, the pros and cons of icu