Indexing in an analytical warehouse looks little like indexing in an OLTP database. A transactional system leans on B-tree indexes to find single rows fast; a warehouse scans huge ranges and cares about skipping data it doesn't need. So warehouse "indexes" are mostly lightweight structures that let the engine prune blocks before reading them, rather than per-row pointers.

Zone maps and min/max pruning

The workhorse of columnar engines is the zone map (a.k.a. min/max index or data-skipping index): for each block of rows, the engine stores the minimum and maximum value of a column. A query filtering WHERE order_date = '2026-06-01' reads each block's min/max and skips every block whose range can't contain that date. No per-row index needed - just tiny per-block summaries. This is most effective when data is sorted or clustered on the filtered column, so each block covers a narrow value range; on randomly-ordered data, every block's range is wide and nothing gets skipped.

Engine-specific skip indexes

Beyond zone maps, engines add targeted structures. ClickHouse has data-skipping indexes - minmax, set, and bloom-filter indexes for high-cardinality columns like UUIDs or emails, where a bloom filter cheaply answers "is this value possibly in this block?" Bitmap indexes suit low-cardinality columns (gender, status) and make multi-predicate filters fast through bitwise AND/OR. The classic B-tree still appears for selective point lookups and for enforcing keys, but it's the exception in scan-heavy analytics, not the default.

Sort order is the real index

In a columnar warehouse, the table's physical sort/clustering key does more than any secondary index. Sorting by the columns you filter and group on makes zone maps tight, boosts compression, and clusters related rows for sequential reads. Choosing that key - and pairing it with partitioning on a coarse dimension like date - is the highest-impact indexing decision; secondary skip indexes are a refinement on top.

Frequently asked questions

Why don't warehouses rely on B-tree indexes? Analytical queries scan large ranges rather than fetching single rows, so per-row B-trees add overhead without helping. Block-pruning structures (zone maps, skip indexes) fit the scan-and-skip access pattern.

What is a zone map? A per-block summary of a column's min and max values that lets the engine skip blocks whose range can't satisfy a query's filter - data skipping without a full index.

When is a bloom-filter index useful? For equality filters on high-cardinality columns (IDs, emails) where min/max ranges are too wide to prune; the bloom filter cheaply rules blocks out.

What's the single most important indexing choice? The physical sort/clustering key. Sorting on frequently-filtered columns makes pruning and compression work; secondary indexes are secondary.