Bitmap index scan behavior in PostgreSQL - Step-by-Step Execution

Concept Flow - Bitmap index scan behavior

Start Query

↓

Use Index to Find Matching Rows

↓

Build Bitmap of Row Locations

↓

Combine Bitmaps if Multiple Indexes

↓

Scan Heap Using Bitmap

↓

Return Matching Rows

↓

End

The query planner uses indexes to find matching rows, builds a bitmap of their locations, then scans the table efficiently using this bitmap to return results.

Execution Sample

PostgreSQL

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM employees WHERE department_id = 5;

This query uses a bitmap index scan to find all employees in department 5 efficiently.

Execution Table

Step	Action	Details	Result
1	Start Query	PostgreSQL receives SELECT with WHERE department_id=5	Query begins
2	Index Scan	Use index on department_id to find matching row pointers	Found row pointers for matching rows
3	Build Bitmap	Create bitmap of matching row locations	Bitmap created with row locations
4	Heap Scan	Scan table rows using bitmap to fetch actual data	Rows fetched from heap
5	Return Rows	Return matching rows to client	Result set sent
6	End	Query execution complete	All matching rows returned

💡 All matching rows found and returned, query completes

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Bitmap	empty	row pointers collected	bitmap built	used to scan heap	cleared after scan
Result Set	empty	empty	empty	rows fetched	rows returned

Key Moments - 2 Insights

Why does PostgreSQL build a bitmap instead of fetching rows immediately after index scan?

Because the bitmap collects all matching row locations first, allowing a single efficient heap scan instead of many random accesses. See execution_table rows 3 and 4.

What happens if multiple indexes are used in the bitmap index scan?

PostgreSQL combines bitmaps from each index to find the intersection or union of matching rows before scanning the heap. This is part of the bitmap build step (row 3).

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the bitmap actually used to fetch rows from the table?

A Step 4 - Heap Scan

B Step 3 - Build Bitmap

C Step 2 - Index Scan

D Step 5 - Return Rows

💡 Hint

Check the 'Action' and 'Details' columns in execution_table row 4.

According to variable_tracker, what is the state of the Result Set after Step 3?

A Contains all matching rows

B Still empty, rows not fetched yet

C Partially filled with some rows

D Cleared after heap scan

💡 Hint

Look at the 'Result Set' row under 'After Step 3' in variable_tracker.

If the bitmap was not built and rows were fetched immediately after index scan, what would likely happen?

A Query would be faster due to immediate fetch

B Heap scan would be more efficient

C Many random disk accesses would slow down query

D No change in performance

💡 Hint

Refer to key_moments explanation about why bitmap is built before heap scan.

Concept Snapshot

Bitmap Index Scan in PostgreSQL:
- Uses index to find matching row locations
- Builds a bitmap of these locations
- Scans table heap once using bitmap
- Efficient for many matching rows
- Combines multiple bitmaps if needed

Full Transcript

In PostgreSQL, a bitmap index scan helps find rows matching a condition efficiently. First, the index is scanned to find row locations matching the query. These locations are stored in a bitmap, which is a compact map of rows to fetch. Then, PostgreSQL scans the table heap once using this bitmap to retrieve the actual rows. This reduces random disk access and speeds up queries with many matches. If multiple indexes are involved, their bitmaps are combined before scanning the heap. The process ends by returning all matching rows to the client.