Bitmap index scan behavior in PostgreSQL

Introduction

Bitmap index scan helps find rows quickly by using a map of matching row locations instead of scanning each row one by one.

When you want to speed up queries that match many rows but not all rows.

When combining multiple index conditions to find rows efficiently.

When the database needs to reduce random disk reads by grouping row fetches.

When a query uses multiple indexes on the same table to filter results.

When the planner decides a bitmap scan is cheaper than a sequential or regular index scan.

Syntax

PostgreSQL

Bitmap Index Scan on index_name  -- scans the index to find matching row locations
Bitmap Heap Scan on table_name    -- fetches the actual rows using the bitmap

Bitmap index scan first creates a bitmap of matching row positions from the index.

Then, bitmap heap scan uses this bitmap to fetch rows efficiently, reducing random disk access.

Examples

Shows query plan that may include a Bitmap Index Scan if many rows match department_id = 5.

PostgreSQL

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

May use Bitmap Index Scan on department_id index and Bitmap Index Scan on salary index, then combine results.

PostgreSQL

EXPLAIN SELECT * FROM employees WHERE department_id = 5 AND salary > 50000;

Sample Program

This query filters system catalog tables with conditions that may trigger a bitmap index scan on relkind and relpages indexes.

PostgreSQL

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pg_catalog.pg_class WHERE relkind = 'r' AND relpages > 1000;

Important Notes

Bitmap index scans are chosen by PostgreSQL planner when it expects many rows to match but not all.

They reduce random disk reads by grouping row fetches, improving performance on large tables.

You can see bitmap index scans in query plans using EXPLAIN or EXPLAIN ANALYZE.

Summary

Bitmap index scan finds matching rows by creating a bitmap of row locations from indexes.

Bitmap heap scan uses this bitmap to fetch rows efficiently from the table.

This method speeds up queries that match many but not all rows, reducing disk access.