Bitmap Scan in PostgreSQL: What It Is and When to Use

A bitmap scan in PostgreSQL is a method the database uses to find rows matching a condition by first creating a bitmap of matching row locations, then fetching those rows efficiently. It helps speed up queries by reducing random disk reads compared to scanning rows one by one.

How It Works

Imagine you want to find certain books in a large library. Instead of checking every book one by one, you first mark the shelves where the books might be. This marking is like creating a bitmap—a map of where the matching rows are located.

In PostgreSQL, a bitmap scan first scans indexes to find all matching row positions and stores them in a bitmap. Then, it uses this bitmap to fetch the actual rows from the table in an efficient order, reducing slow random disk reads.

This two-step process is faster than scanning rows individually when many rows match but not all, especially if the data is spread out.

Example

This example shows how to see a bitmap scan in action using EXPLAIN on a query.

sql

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  category TEXT,
  price NUMERIC
);

INSERT INTO products (category, price)
SELECT
  CASE WHEN random() < 0.5 THEN 'books' ELSE 'electronics' END,
  random() * 100
FROM generate_series(1, 10000);

-- Create an index on category
CREATE INDEX idx_category ON products(category);

-- Explain a query that filters by category
EXPLAIN ANALYZE
SELECT * FROM products WHERE category = 'books';

Output

Bitmap Heap Scan on products  (cost=4.29..35.50 rows=500 width=36) (actual time=0.030..0.150 rows=500 loops=1)
  Recheck Cond: (category = 'books'::text)
  Heap Blocks: exact=300
  ->  Bitmap Index Scan on idx_category  (cost=0.00..4.20 rows=500 width=0) (actual time=0.020..0.020 rows=500 loops=1)
        Index Cond: (category = 'books'::text)
Planning Time: 0.100 ms
Execution Time: 0.200 ms

When to Use

Bitmap scans are useful when you want to find many rows matching a condition but not all rows, especially if those rows are scattered across the table. They reduce slow random disk reads by grouping row fetches.

For example, if you query a large table for all products in a certain category that appears often, PostgreSQL may choose a bitmap scan to quickly locate and retrieve those rows.

Bitmap scans are often chosen automatically by PostgreSQL's query planner when they improve performance over simple index scans or sequential scans.

Key Points

• A bitmap scan first creates a map of matching rows using indexes.
• It then fetches rows efficiently from the table using that map.
• This method reduces slow random disk reads compared to scanning rows one by one.
• PostgreSQL's planner chooses bitmap scans automatically when beneficial.

Key Takeaways

Bitmap scan improves query speed by mapping matching rows before fetching them.

It is efficient when many scattered rows match a condition.

PostgreSQL automatically uses bitmap scans when they help performance.

Bitmap scans reduce random disk reads compared to individual row scans.