InletDownload

PostgreSQL EXPLAIN

Bitmap Heap Scan in PostgreSQL EXPLAIN

A Bitmap Heap Scan reads the table pages that a Bitmap Index Scan marked, in physical order, each page once. It’s the planner’s middle ground between an index scan and a full table scan; it slows down when the bitmap goes lossy or the matches cover most of the table.

Updated 9 October 2026

What it does

A Bitmap Heap Scan takes the bitmap of row locations built by the Bitmap Index Scan beneath it (or by a BitmapAnd / BitmapOr of several) and reads those table pages in the order they sit on disk. Each page is read once, however many matching rows it holds. On each page it picks out the marked rows, checks they’re visible to your transaction, and applies any extra conditions.

Compared with an Index Scan, it gives up returning rows in index order in exchange for reading the table sequentially. Compared with a Seq Scan, it skips pages with no matches.

When the planner picks it

  • A condition matches more rows than suit an Index Scan but too few for a Seq Scan: typically between a fraction of a percent and a few percent of a table, depending on how the rows are spread.
  • OR conditions, or conditions on two indexed columns, combined through BitmapOr / BitmapAnd.
  • GIN and other indexes that only support bitmap scans (full-text search, jsonb @>, arrays).
  • The inner side of a Nested Loop, with the outer row’s value in Recheck Cond, when each lookup returns several rows.

Reading its numbers

Bitmap Heap Scan on orders  (cost=59.43..19809.61 rows=4196 width=34) (actual time=0.321..22.937 rows=4000.00 loops=1)
  Recheck Cond: ((customer_id >= 1000) AND (customer_id <= 1199))
  Rows Removed by Index Recheck: 371397
  Heap Blocks: exact=757 lossy=3122
  Buffers: shared hit=3886
  • Recheck Cond: the index condition, checked again against rows on the page. It’s always printed but only does work on lossy pages (below) or with index types that can return false matches.
  • Heap Blocks: exact=…: pages for which the bitmap remembered exactly which rows matched.
  • Heap Blocks: lossy=…: pages for which it only remembered “something on this page matches”, because the bitmap outgrew work_mem. On those pages every row is read and run through Recheck Cond. See lossy heap blocks.
  • Rows Removed by Index Recheck: rows on lossy pages that failed the recheck. Here 371,397 rows were read and thrown away to find 4,000. It’s an average per loop.
  • Filter / Rows Removed by Filter: extra conditions that weren’t part of the index search, checked on each row.
  • Buffers: table pages, plus the index pages counted on the Bitmap Index Scan below.

Parallel Bitmap Heap Scan

In a parallel plan, the leader builds the bitmap alone and the table pages are shared out between the processes. PostgreSQL 18 adds a line per worker:

->  Parallel Bitmap Heap Scan on orders  (cost=59.43..12390.47 rows=1748 width=34) (actual time=0.203..22.341 rows=1333.33 loops=3)
      Recheck Cond: ((customer_id >= 1000) AND (customer_id <= 1199))
      Rows Removed by Index Recheck: 123799
      Heap Blocks: exact=377 lossy=1527
      Buffers: shared hit=3926
      Worker 0:  Heap Blocks: exact=249 lossy=1030
      Worker 1:  Heap Blocks: exact=131 lossy=565

In our runs the top Heap Blocks line counted only the leader’s pages: leader and workers add up to 757 exact and 3,122 lossy, the same as the serial plan above. PostgreSQL 17 and 14 print only that top line, so most of the work is missing from the plan.

When it’s a problem

  1. Lossy blocks. If lossy= is large and Rows Removed by Index Recheck dwarfs the rows returned, the bitmap didn’t fit in work_mem. Raise it for the session or one query: SET LOCAL work_mem = '32MB' inside a transaction. In the example, the default 4 MB gave exact=3879 and no recheck; 64 kB gave 3,122 lossy pages and four times the run time. Trade-off: work_mem applies to every sort and hash in every session, so raise it where it’s needed, not server-wide without thought.
  2. Almost every page is read. Heap Blocks: exact=8333 on an 8,334-page table means the matches are spread everywhere, and a Seq Scan would read the same pages. If you need fewer rows, narrow the condition. If the table is often queried by this column, CLUSTER <table> USING <index> rewrites it in index order so matching rows share pages (it takes an ACCESS EXCLUSIVE lock while it runs, and the order decays as rows change).
  3. Large Rows Removed by Filter. The index finds candidates, the filter discards most. Add the filtered column to the index, or a partial index for a fixed condition.
  4. Wrong estimates. A bitmap scan expected to touch 50 pages that touches 50,000 is a statistics problem; run ANALYZE and look again.

Example

PostgreSQL 18.6, default settings (work_mem 4 MB):

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL,
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);
VACUUM ANALYZE orders;

(Our test table also had a foreign key to customers; it doesn’t change these plans.)

The orders of 200 customers, 20 each, scattered through the table:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id BETWEEN 1000 AND 1199;

Bitmap Heap Scan on orders  (cost=59.43..7154.02 rows=4196 width=34) (actual time=0.730..5.213 rows=4000.00 loops=1)
  Recheck Cond: ((customer_id >= 1000) AND (customer_id <= 1199))
  Heap Blocks: exact=3879
  Buffers: shared hit=3886
  ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..58.38 rows=4196 width=0) (actual time=0.331..0.331 rows=4000.00 loops=1)
        Index Cond: ((customer_id >= 1000) AND (customer_id <= 1199))
        Index Searches: 1
        Buffers: shared hit=7
Planning Time: 0.070 ms
Execution Time: 5.462 ms

4,000 rows on 3,879 different pages: almost one page per row, but each read once and in order.

The same query with a tiny work_mem, and parallel workers and plain index scans switched off so the plan stays comparable:

SET work_mem = '64kB';
SET enable_indexscan = off;
SET max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id BETWEEN 1000 AND 1199;

Bitmap Heap Scan on orders  (cost=59.43..19809.61 rows=4196 width=34) (actual time=0.321..22.937 rows=4000.00 loops=1)
  Recheck Cond: ((customer_id >= 1000) AND (customer_id <= 1199))
  Rows Removed by Index Recheck: 371397
  Heap Blocks: exact=757 lossy=3122
  Buffers: shared hit=3886
  ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..58.38 rows=4196 width=0) (actual time=0.256..0.256 rows=4000.00 loops=1)
        Index Cond: ((customer_id >= 1000) AND (customer_id <= 1199))
        Index Searches: 1
        Buffers: shared hit=7
Planning:
  Buffers: shared hit=115
Planning Time: 0.210 ms
Execution Time: 23.146 ms

The same pages were read (shared hit=3886), but on 3,122 of them every row had to be rechecked: 371,397 rows read and discarded. The run time went from 5 ms to 23 ms. Note the planner priced this in too: the total cost rose from 7,154 to 19,810.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts. With your own Anthropic API key, Ask Claude (⌘L) explains a plan like this one, sending the plan and schema but never rows.

Related

Sources