10

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"

Improve this question

1 Answer 1

Reset to default
12

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.

Improve this answer
2
  • Thank you. So if I understand correctly a block is a bitmap that stores row references or block references. I guess these blocks are build first by the two bitmap index scans. BitmapAnd takes these blocks, perform the and operation and return one Block. Am I right?
    – Jesús López
    Commented Jan 21, 2016 at 6:35
  • 2
    A "block" (or a "page") is a 8kB chunk of disk space where your permanent data is stored. Before you can do anything with a block, you have to pull it into RAM and put in a "buffer". The bitmap itself is a transient data structure which only exists in RAM and so is not block-structured (or at least, not in the same sense as heap blocks are). It reads index blocks to get the data it needs to create the bitmap (in this case it doesn't actually read the blocks, because it found them already in the buffers), then uses the bitmap to decide which table ("Heap") blocks it needs to inspect.
    – jjanes
    Commented Jan 21, 2016 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.