Does a bitmap index store the original records?

Its main purpose is to identify membership and locate matching rows. It does not generally replace the table's complete records.

Is fewer distinct values always better?

Not automatically. Compression, query selectivity, combinations of filters and modification patterns also affect the result.

Does a PostgreSQL bitmap scan require a persistent bitmap index?

No. PostgreSQL can construct an in-memory bitmap from ordinary indexes while executing a query.

AI Glossary / Data engineering and architecture / Intermediate

Bitmap index

Definition

A bitmap index is a database index that represents membership of rows in indexed values using sequences of bits. Logical operations on these bitmaps can identify rows satisfying combinations of conditions before the database retrieves the underlying records.

What it means

Imagine a table of products with a region column. A simple bitmap representation has a bit sequence for each region: a one indicates that the corresponding row belongs to that region, and a zero indicates that it does not. A second indexed attribute can have its own sequences.

This is especially useful when analytical queries combine several filters across many rows. The database can combine compact representations of matching rows instead of immediately inspecting every full record. Compression and implementation details strongly affect the actual space and processing cost.

The term needs care when reading query plans. PostgreSQL can build temporary bitmaps from ordinary indexes during a query. A PostgreSQL “Bitmap Index Scan” therefore does not by itself mean that the database has a stored bitmap index of the kind described in Oracle documentation.

How it works

In a simplified design, each distinct indexed value has a bitmap whose positions map to table rows. An equality condition selects the relevant bitmap. AND combines conditions that must both hold; OR combines alternatives. The resulting set identifies candidate rows for further processing.

Implementations compress bitmaps, map positions to row identifiers and maintain the index as data changes. Some operations can use the index without fetching all matching rows, but that depends on the query and database. A representation with one uncompressed bitmap per distinct value would use approximately rows multiplied by distinct values bits, before overhead.

Low cardinality, meaning relatively few distinct values, often makes this approach attractive. It is not a universal requirement or a sufficient reason to choose the index. Update patterns, compression, selectivity and query combinations matter. Oracle advises against bitmap indexes for workloads with heavy concurrent data modification because their concurrency behavior differs from ordinary row-oriented expectations.

Three practical examples

A retail warehouse combines region, product category and availability filters for a stock analysis.
A reporting database counts records matching several status fields without treating each condition as a separate full-table pass.
An analyst investigating a PostgreSQL query plan distinguishes its temporary bitmap combination from a persistent bitmap index design.

Think of it this way

Think of transparent sheets laid over the same numbered seating plan. One sheet marks people holding a workshop ticket; another marks people requesting a printed handout. Overlaying the marks can identify people meeting both conditions.

The analogy explains shared positions and combinations. Real bitmap indexes use digital representations and may compress long patterns; they are not necessarily large, literal sheets stored one bit at a time. Keeping marks current when the underlying rows change also requires database work.

Worked example

Consider eight inventory rows, numbered from left to right. Rows 1, 3, 6 and 7 belong to the North region, represented as 10100110. Rows 1, 2, 4, 6 and 7 are in stock, represented as 11010110.

For “North AND in stock”, combine corresponding positions. The result is 10000110, selecting rows 1, 6 and 7. There are three matching rows. For “North OR in stock”, the result is 11110110, selecting six rows: 1, 2, 3, 4, 6 and 7.

These eight-position strings illustrate the logic only. They exclude index keys, row mapping, compression and other storage costs. They also do not predict elapsed time. A small table might be faster to scan, and retrieving most rows can make an index less attractive than its compact intermediate calculation suggests.

Illustrative case study

Illustrative case study: A fictional distributor analyses a mostly read-only inventory snapshot. Reports combine warehouse region, item class and several availability states. Its database team tests bitmap indexes on the reporting copy, using the actual report mix and representative refresh operations.

A colleague proposes adding identical indexes to the live order system. The team treats that as a separate decision because many users change inventory concurrently there. It measures modification behavior and retains a different index strategy for that workload.

The example illustrates workload-specific evaluation. It supplies no measured improvement or real company result. The useful outcome is an explicit decision about where the index fits and how the reporting copy is refreshed.

Common misconceptions and mistakes

Assuming that bitmap indexes only support Boolean columns. They can represent multiple distinct values.
Equating low cardinality with guaranteed performance improvement.
Ignoring update and concurrency costs when copying an analytical design into a transaction system.
Interpreting every query-plan reference to a bitmap as evidence of a persistent bitmap index.
Treating the number of logical bit operations as a complete estimate of query latency.

Business use

For businesses, bitmap indexing is relevant when recurring analytical reports combine dimensions across substantial datasets. Begin with representative queries and their response-time needs. Include refresh frequency and concurrent writes in the evaluation rather than testing only a quiet reporting snapshot.

Ask the database specialist to explain the actual index type, storage cost and execution plan. Compare it with suitable alternatives using realistic data distributions. Preserve a rollback path for index changes and reassess after the workload changes. An index that helps one report can add maintenance cost to every relevant update.

Check your understanding

In the eight-row example, which rows satisfy both North and in stock?

Answer: Rows 1, 6 and 7. AND retains a one only where both input bitmaps have a one; it does not add the counts of the two groups.

Related terms

Database index
Cardinality (data modeling)
Data Warehouse
Cardinality
Bitset
Query optimizer
Index scan
Compression
Boolean algebra
B-tree

Learn next

Draw two small membership bitmaps and calculate AND and OR results by hand. Then inspect a database execution plan and determine whether its bitmap is stored persistently or constructed during execution.

Execution plan

Sources

Primary sources: Oracle Database 19c Data Warehousing Guide, bitmap indexing and workload considerations: https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/data-warehouse-optimizations-techniques.html ; PostgreSQL 17, Combining Multiple Indexes: https://www.postgresql.org/docs/17/indexes-bitmap-scans.html . Relevant documentation sections reviewed. Inventory strings and distributor scenario are original illustrations; product-specific behaviour should be checked for the deployed version.

Previous: Bitext word alignment Next: Bitstream format