What is a Bitmap Index and When Should You Use One?
Learn what a bitmap index is, why it suits low-cardinality columns, and how it speeds up analytical queries in interviews.
Expected Interview Answer
A bitmap index stores one bit array per distinct column value, where each bit position corresponds to a row and is set to 1 if that row holds that value, letting the engine answer filters and combine conditions with fast bitwise AND/OR/NOT operations instead of tree traversals.
Bitmap indexes work best on low-cardinality columns, such as gender, status, or region, where the number of distinct values is small relative to the row count, because each distinct value gets its own compressible bit vector. Queries with multiple WHERE conditions on such columns are answered by ANDing or ORing the relevant bitmaps together in memory, which is dramatically cheaper than walking several B-tree indexes and intersecting row-id lists. The trade-off is write cost: updating a single row can require touching the bitmap for its old and new values, and under fine-grained bitmap locking this makes bitmap indexes a poor fit for high-write OLTP tables, but an excellent fit for read-heavy analytical warehouses.
- Extremely fast AND/OR/NOT combination across multiple filters
- Highly compressible for low-cardinality columns
- Ideal for read-heavy analytical and data-warehouse workloads
- Enables efficient COUNT and existence checks without scanning rows
AI Mentor Explanation
Picture a scorer keeping a separate strip of paper for every possible dismissal type — bowled, caught, run-out — with one box per batter, ticking the box only for the dismissal that actually happened. To find all batters out "caught" this over, the scorer just reads one strip instead of rereading every scorecard entry. A bitmap index does exactly this: one bit vector per distinct value, so filtering means reading a single compact strip rather than scanning every row.
Step-by-Step Explanation
Step 1
Identify a low-cardinality column
Choose a column with relatively few distinct values, such as status or region, where a bitmap pays off.
Step 2
Build one bit vector per value
The engine creates a bit array the length of the table, one per distinct value, with a 1 marking rows that hold it.
Step 3
Combine bitmaps for multi-condition filters
A query with several WHERE clauses on bitmap-indexed columns ANDs or ORs the relevant vectors together.
Step 4
Translate result bits to rows
The engine converts the final set bits back into row identifiers to fetch the matching data.
What Interviewer Expects
- Understanding that bitmap indexes suit low-cardinality columns
- Ability to explain bitwise AND/OR combination across filters
- Awareness of the write-cost trade-off versus B-tree indexes
- Distinction between OLAP-friendly and OLTP-unfriendly use cases
Common Mistakes
- Suggesting a bitmap index for a high-cardinality column like email
- Not mentioning the write/locking overhead on frequently updated tables
- Confusing a bitmap index with a bitmap used for storage compression
- Forgetting that combining conditions is the key performance win
Best Answer (HR Friendly)
“A bitmap index keeps one small bit array per distinct value in a column, marking which rows have that value. It is great for columns with few possible values, like status flags, because combining several filters becomes a fast bitwise operation instead of scanning the table, but it is less suited to tables with heavy, frequent writes.”
Code Example
-- Low-cardinality column: order_status has few distinct values
CREATE BITMAP INDEX idx_orders_status
ON Orders (order_status);
-- Combining two bitmap-indexed filters is cheap:
-- the engine ANDs the status bitmap with the region bitmap
SELECT COUNT(*)
FROM Orders
WHERE order_status = 'SHIPPED'
AND region = 'EU';Follow-up Questions
- Why do bitmap indexes struggle on high-write OLTP tables?
- How does bitmap index compression like run-length encoding help?
- When would you prefer a B-tree index over a bitmap index?
- How does a bitmap index answer a COUNT query without touching rows?
MCQ Practice
1. A bitmap index is best suited for columns with:
Bitmap indexes create one bit vector per distinct value, so they stay compact and fast only when the value count is small.
2. What operation makes combining multiple bitmap-indexed filters fast?
Bitmaps for different conditions are combined with cheap bitwise AND/OR/NOT operations to find matching rows.
3. Why are bitmap indexes generally avoided on heavily-written OLTP tables?
A single row update can touch large bit vectors, and bitmap locking is typically coarser-grained than row locking, hurting write concurrency.
Flash Cards
What is a bitmap index? — An index storing one bit vector per distinct column value, marking which rows contain it.
When is a bitmap index a good fit? — For low-cardinality columns in read-heavy, analytical workloads.
Why avoid bitmap indexes on OLTP tables? — Frequent writes force expensive bit-vector updates and coarser locking.
How are multiple bitmap filters combined? — Via fast bitwise AND, OR, and NOT operations between the vectors.