Heap Block in Bitmap Heap Scan – PostgreSQL Explanation

explainpostgresqlpostgresql-9.4

I have this query:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM test
WHERE
    timestamp_range @> '2015-01-22 23:00:00'::timestamp
    AND data_int_array @> '{49, 61}'::integer[];

Which outputs:

Bitmap Heap Scan on test  (cost=16.74..20.75 rows=1 width=113) (actual time=0.364..0.367 rows=2 loops=1)
  Recheck Cond: ((timestamp_range @> '2015-01-22 23:00:00'::timestamp without time zone) AND (data_int_array @> '{49,61}'::integer[]))
  Heap Blocks: exact=1
  Buffers: shared hit=8
  ->  BitmapAnd  (cost=16.74..16.74 rows=1 width=0) (actual time=0.351..0.351 rows=0 loops=1)
        Buffers: shared hit=7
        ->  Bitmap Index Scan on ix_test_interval  (cost=0.00..4.40 rows=17 width=0) (actual time=0.130..0.130 rows=12 loops=1)
              Index Cond: (timestamp_range @> '2015-01-22 23:00:00'::timestamp without time zone)
              Buffers: shared hit=2
        ->  Bitmap Index Scan on ix_test_data_int_array_data_json  (cost=0.00..12.08 rows=11 width=0) (actual time=0.211..0.211 rows=6 loops=1)
              Index Cond: (data_int_array @> '{49,61}'::integer[])
              Buffers: shared hit=5
Planning time: 0.396 ms
Execution time: 0.484 ms

I have read the documentation "Using Explain", but I didn't find any reference to Heap Block.

Please, could you tell me what Heap Bock means and how it relates to Buffers in a Bitmap Heap Scan?

I'm running: "PostgreSQL 9.4.5, compiled by Visual C++ build 1800, 64-bit"

Best Answer

Bitmaps can either store a bitmap of rows, or if that becomes too large to fit in work_mem it can "go lossy" by storing a bitmap of blocks. It can do this selectively, so some blocks can be converted lossy while others not.

If it goes lossy, then the Heap Scan must recheck every row in every lossy block which it visits, because it no longer has information about which particular rows in that block met the search criteria.

"Heap Blocks: exact=1" means that it only visited one block, and that it was not lossy. (So both of yours rows returned were in the same block)

This agrees with the Buffers data. The Bitmap Index Scans and BitmapAnd in total hit 7 blocks. The Heap Scan hit one more, bringing it up to 8.

Related Question