Обсуждение: BitMap Heap Scan & BitMap Index Scan
I would like to know, What is BitMap Heap Scan & BitMap Index Scan? When I use EXPLAIN for query, which has LEFT JOIN with 4 different table then some time query planner uses Bitmap Heap Scan and some time Bitmap Index Scan?
View this message in context: BitMap Heap Scan & BitMap Index Scan
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
View this message in context: BitMap Heap Scan & BitMap Index Scan
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
http://www.postgresql.org/docs/9.3/static/using-explain.html On Sun, Nov 10, 2013 at 4:32 PM, monalee_dba <monalee@sungraceinfotech.co.in> wrote: > I would like to know, What is BitMap Heap Scan & BitMap Index Scan? When I > use EXPLAIN for query, which has LEFT JOIN with 4 different table then some > time query planner uses Bitmap Heap Scan and some time Bitmap Index Scan? The way to go here would be to have a look at the documentation first: http://www.postgresql.org/docs/9.3/static/using-explain.html Then, AFAIK, Bitmap Heap Scan (upper level) is always coupled with Bitmap Index Scan (lower level) so there are always in at least 2 nodes, at least because you could have multiple Bitmap Index Scan nodes. The lower node Bitmap Index Scan creates a bitmap of the pages of the relation to track pages that might contain tuples satisfying the index condition (1 bit per page, so a relation with 1 million pages would have roughly 119kB). Then the bitmap is passed to the upper node called "Bitmap Index Scan", that reads the pages in a more sequential fashion. Regards, -- Michael
On 10/11/13 08:32, monalee_dba wrote: > I would like to know, What is BitMap Heap Scan & BitMap Index Scan? When > I use EXPLAIN for query, which has LEFT JOIN with 4 different table then > some time query planner uses Bitmap Heap Scan and some time Bitmap Index > Scan? Check out this great presentation: http://momjian.us/main/writings/pgsql/optimizer.pdf The way I understand it is this (Correct me if I am wrong). The bitmap index scan uses an index to build a bitmap where each bit corresponds to a data buffer (8k). Since a buffer can contain multiple tuples and not all of them must match the condition another run over the heap pages is needed to find the matching tuples. This is the bitmap heap scan. It iterates over the table data buffers found in the bitmap index scan and selects only those tuples that match the filter (hence the recheck thing you see in explain) and visibility conditions. Torsten