Re: Recheck condition...

On Fri, Jul 6, 2012 at 9:41 PM, Ioannis Anagnostopoulos
<ioannis(at)anatec(dot)com>wrote:

> Hello,
>
> I think that my query is as much tuned as possible can. However I am
> baffled by the "recheck condition" that always follow my index scan. Is
> this something normal or can I get rid of it somehow?
>
> "HashAggregate (cost=2141.07..2141.16 rows=4 width=38)"
> " -> Result (cost=0.00..2141.03 rows=4 width=38)"
> " -> Append (cost=0.00..2140.99 rows=4 width=38)"
> " -> Seq Scan on message_copies (cost=0.00..0.00 rows=1
> width=68)"
> " Filter: ((date_part('day'::text, msg_date_rec) = ANY
> ('{6}'::double precision[])) AND (date_trunc('day'::text, msg_date_rec) =
> ANY ('{"2012-01-06 00:00:00"}'::timestamp without time zone[])) AND
> (((((pos_georef1)::text || (pos_georef2)::text) || (pos_georef3)::text) ||
> (pos_georef4)::text) = ANY ('{MKPM2648,MKPM2649,MKPM2650}'::text[])) AND
> (src_id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[])))"
> " -> Bitmap Heap Scan on message_copies_wk0 message_copies
> (cost=139.41..2140.99 rows=3 width=28)"
> " *Recheck Cond*: ((date_trunc('day'::text,
> msg_date_rec) = ANY ('{"2012-01-06 00:00:00"}'::timestamp without time
> zone[])) AND (src_id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[])) AND
> (((((pos_georef1)::text || (pos_georef2)::text) || (pos_georef3)::text) ||
> (pos_georef4)::text) = ANY ('{MKPM2648,MKPM2649,MKPM2650}'::text[])))"
> " Filter: (date_part('day'::text, msg_date_rec) = ANY
> ('{6}'::double precision[]))"
> " -> Bitmap Index Scan on
> idx_message_copies_wk0_date_src_pos (cost=0.00..139.41 rows=507 width=0)"
> " Index Cond: ((date_trunc('day'::text,
> msg_date_rec) = ANY ('{"2012-01-06 00:00:00"}'::timestamp without time
> zone[])) AND (src_id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[])) AND
> (((((pos_georef1)::text || (pos_georef2)::text) || (pos_georef3)::text) ||
> (pos_georef4)::text) = ANY ('{MKPM2648,MKPM2649,MKPM2650}'::text[])))"
>

It is expected between the "Bitmap Index Scan" and "Bitmap heap Scan"
operations, to account for lossy bitmaps built during bitmap builds.

Here's a 6 years old explanation from Heikki (part of a private
conversation at EnterpriseDB):

<quote>
The way bitmap index scans work is that the index is first scanned for
matching tuples. All matches are recorded in a fancy bitmap where each bit
represents one heap tuple. After scanning the index for all matches and
constructing the bitmap, the corresponding heap tuples are fetched. If
there's AND or OR clauses in the query, those are evaluated by doing AND or
OR of the bitmaps before fetching the heap tuples.

If the number of matches is high enough that the bitmap wouldn't fit in
memory, we don't record each matching tuple but just one bit / heap page
where there is matching tuples. That's what becoming lossy means. When
fetching the records from the heap, any lossy pages in the bitmap need to
be fully scanned and index quals re-evaluated to see which tuples really
match.

The above description is quite simplified, you might want to take a look at
src/backend/nodes/tidbitmap.c for a better description :-).
<quote>

Best regards,
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company