Re: Recheck condition...
| От | Gurjeet Singh |
|---|---|
| Тема | Re: Recheck condition... |
| Дата | |
| Msg-id | CABwTF4WiV_trS2QGXGziEpo6GhRmK4DNQV9JW-+mbdVdFgqHNw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Recheck condition... (Ioannis Anagnostopoulos <ioannis@anatec.com>) |
| Список | pgsql-novice |
On Fri, Jul 6, 2012 at 9:41 PM, Ioannis Anagnostopoulos <ioannis@anatec.com> wrote:
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>
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
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
В списке pgsql-novice по дате отправления: