When working with PostgreSQL, especially on large tables, you may come across something called a Bitmap Heap Scan in your EXPLAIN ANALYZE output. At first, it may look confusing compared to simpler operations like Index Scan or Sequential Scan. However, Bitmap Heap Scan is actually one of PostgreSQL’s smart optimization techniques designed to handle a very common performance problem.
To understand why Bitmap Heap Scan exists, we first need to look at how PostgreSQL normally reads data. If a query returns only a few rows, PostgreSQL uses an Index Scan. This is fast because it directly jumps to the required rows using an index. On the other hand, if a query needs a large portion of the table, PostgreSQL prefers a Sequential Scan, which reads the entire table in order. This is efficient because sequential reads are faster, especially when data is cached or stored on fast disks.
The problem happens in the middle case. Imagine a query that returns a few thousand rows from a table with millions of rows. These matching rows are usually spread across different parts of the table. If PostgreSQL uses a normal Index Scan, it has to jump randomly across many data pages. This random access can slow things down, especially when data is not fully in memory. At the same time, doing a full Sequential Scan would mean reading the entire table, which is also inefficient because most of the data is not needed.
This is exactly where Bitmap Heap Scan becomes useful.
How does it work?
A Bitmap Heap Scan works in two main steps. First, PostgreSQL uses one or more indexes to identify which parts of the table might contain matching rows. Instead of fetching rows immediately, it builds a bitmap, which is a simple in-memory structure that marks which table pages contain relevant data. Each page is marked only once, even if multiple matching rows exist on that page.
In the second step, PostgreSQL reads the table pages in order, similar to a sequential scan. It checks the bitmap to see which pages are relevant and only processes those pages. This approach avoids unnecessary reads and reduces random disk access. As a result, it combines the benefits of both index scanning and sequential scanning.
One of the biggest advantages of Bitmap Heap Scan is that it can use multiple indexes together. For example, if a query has conditions on multiple columns, PostgreSQL can build separate bitmaps from each index and then combine them using logical operations like AND or OR. This allows PostgreSQL to filter data more efficiently without needing a single combined index.
Bitmap Heap Scan is usually chosen when the query returns a moderate number of rows. If the result set is very small, a simple Index Scan is faster because it can stop early. If the result set is very large, a Sequential Scan is more efficient. Bitmap Heap Scan fits in between these two cases, where neither approach is ideal.
Sometimes, you may notice a “Recheck Cond” in the plan. This happens when the bitmap is not precise enough at the row level, and PostgreSQL needs to verify the condition again while reading the actual rows. This does not mean the plan is bad; it is just an additional safety step to ensure correct results.
The reason Bitmap Heap Scan exists is to reduce the cost of random disk access while avoiding unnecessary full table scans. It provides a balanced approach that improves performance for medium-sized result sets. This makes it especially useful in real-world applications where queries often filter data using multiple conditions.
In practice, Bitmap Heap Scan is commonly seen in systems like ERP applications, reporting tools, and analytics queries, where data is large and queries are complex. Instead of being a confusing or inefficient operation, it is actually a sign that PostgreSQL is making a smart decision based on the data and query conditions.
Overall, Bitmap Heap Scan is an important part of PostgreSQL’s query optimization strategy. It helps bridge the gap between index scans and sequential scans, providing better performance in scenarios where both approaches have limitations. Understanding it can help you read execution plans more confidently and optimize your queries more effectively.