Bitmap Indexing vs Range Indexing

Bitmap indexing crushes low-cardinality analytical filters with bitwise math; range indexing owns ordered scans and high-cardinality lookups. Picking the wrong one bloats your index or kills your query plan.

How They Actually Work

Range indexing — almost always a B-tree — stores keys in sorted order, so the engine can binary-search to a value and then walk the leaf nodes for everything between two bounds. That ordering is the whole point: equality, BETWEEN, ORDER BY, and prefix matches all fall out of it for free. Bitmap indexing stores one bitstring per distinct column value, where each bit position maps to a row. Filtering becomes a bitwise AND/OR across those bitstrings, which CPUs do absurdly fast and which compresses to nothing when values repeat. The catch is structural, not cosmetic: bitmaps assume few distinct values and stable rows. B-trees assume nothing and degrade gracefully. One is a Swiss Army knife, the other is a scalpel that's useless for anything but its one cut.

Cardinality Is the Whole Argument

Cardinality decides this fight before any benchmark runs. A bitmap on a boolean is two tidy bitstrings — gorgeous. A bitmap on a UUID column is millions of nearly-empty bitstrings, each one bit set, and you've reinvented a worse, fatter row scan. The rule of thumb the textbooks hedge on, I won't: under a few hundred distinct values, bitmaps are spectacular; above a few thousand, they're a liability. Range indexes don't care. A B-tree on a primary key with ten million unique values is exactly as happy as one on a gender column, because sorted order doesn't depend on how many neighbors share your value. If you can't state your column's cardinality off the top of your head, you have no business reaching for a bitmap.

Writes Will Wreck Your Bitmap

Here's where bitmap fans go quiet. Updating a single row's value can require locking and rewriting entire bitmap segments, because the row-to-bit mapping is shared across many concurrent transactions. Oracle famously warns you off bitmaps on OLTP tables for exactly this — one DML statement can serialize a pile of others. B-trees take a row insert and touch one leaf, maybe split a node, and move on; they were built for live, concurrent, write-heavy traffic. So the honest framing is: bitmaps are a read-optimized, batch-load structure that belongs in a warehouse where data arrives nightly and queries run all day. Drop one into a transactional system with steady writes and you'll trade fast SELECTs for lock contention nobody warned the product team about.

Where Bitmap Earns Its Keep

Don't mistake my pick for contempt — bitmaps win decisively in their lane, and it's a real lane. Star-schema analytics is the canonical case: a fact table filtered by half a dozen low-cardinality dimension flags, where the optimizer ANDs and ORs bitmaps together in a single pass and answers a query a B-tree would need multiple index scans and a merge to satisfy. Combining filters is bitmap's superpower; a five-predicate WHERE clause collapses into a handful of bitwise ops over compressed data. Columnar engines and OLAP warehouses lean on exactly this. The mistake is generalizing that triumph. Bitmap is the right answer to a specific, recurring, read-mostly analytical question. It is the wrong answer to 'what index should this table have' asked in a vacuum — and that vacuum is where most engineers actually stand.

Quick Comparison

Factor	Bitmap Indexing	Range Indexing
High-cardinality columns	Falls apart — millions of sparse bitstrings	Native strength — sorted keys scale fine
Low-cardinality multi-filter analytics	Bitwise AND/OR in one fast pass	Multiple scans plus merge
Write/update concurrency	Segment rewrites, lock contention on DML	One leaf touched, built for OLTP
Ordered queries (BETWEEN, ORDER BY)	No inherent ordering	Free from the sorted structure
Storage on repetitive values	Compresses to near nothing	Stores every key regardless

The Verdict

Use Bitmap Indexing if: Your column is low-cardinality (status, gender, region, boolean flags), the table is mostly read, and you run analytical queries combining many filters with AND/OR — bitmaps turn that into one fast bitwise pass.

Use Range Indexing if: Your column is high-cardinality (timestamps, prices, IDs), you need ORDER BY, BETWEEN, or sorted pagination, and the table takes concurrent writes — range/B-tree indexes are the default for a reason.

Consider: Most OLTP systems should just use range indexing. Reach for bitmaps in a data warehouse or columnar store where columns are few-valued and writes are batch loads, not live transactions.

Bitmap Indexing vs Range Indexing: FAQ

Is Bitmap Indexing or Range Indexing better?

Range Indexing is the Nice Pick. Range indexing (B-tree style) is the safe default that handles equality, ordering, range scans, and high-cardinality columns without falling apart under writes. Bitmap is a specialist that wins big but only in narrow, mostly-read analytical territory. For a general-purpose pick, range wins.

When should you use Bitmap Indexing?

Your column is low-cardinality (status, gender, region, boolean flags), the table is mostly read, and you run analytical queries combining many filters with AND/OR — bitmaps turn that into one fast bitwise pass.

When should you use Range Indexing?

Your column is high-cardinality (timestamps, prices, IDs), you need ORDER BY, BETWEEN, or sorted pagination, and the table takes concurrent writes — range/B-tree indexes are the default for a reason.

What's the main difference between Bitmap Indexing and Range Indexing?

Bitmap indexing crushes low-cardinality analytical filters with bitwise math; range indexing owns ordered scans and high-cardinality lookups. Picking the wrong one bloats your index or kills your query plan.

How do Bitmap Indexing and Range Indexing compare on high-cardinality columns?

Bitmap Indexing: Falls apart — millions of sparse bitstrings. Range Indexing: Native strength — sorted keys scale fine. Range Indexing wins here.

Are there alternatives to consider beyond Bitmap Indexing and Range Indexing?

Most OLTP systems should just use range indexing. Reach for bitmaps in a data warehouse or columnar store where columns are few-valued and writes are batch loads, not live transactions.

The Bottom Line

Range Indexing wins

Range indexing (B-tree style) is the safe default that handles equality, ordering, range scans, and high-cardinality columns without falling apart under writes. Bitmap is a specialist that wins big but only in narrow, mostly-read analytical territory. For a general-purpose pick, range wins.

Related Comparisons