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.