Database index
A database index is a data structure that stores a subset of a table's data in an order optimized for fast lookups, letting queries find matching rows without scanning the whole table.
Overview
A database index is an auxiliary data structure — commonly a B-tree, hash table, or radix tree — that maps column values to the rows that contain them. Instead of scanning every row to find matches, the database can jump directly to the relevant entries. Indexes speed up lookups and filters dramatically, but they cost extra storage and add overhead on every insert, update, or delete, since the index has to be kept in sync with the table.
How Indexes Work
Most general-purpose indexes are ordered structures (B-trees) that support equality and range lookups in logarithmic time. Hash indexes support only equality lookups but can be faster for that specific case. A query planner decides whether to use an index or scan the table based on selectivity: an index on a column with few distinct values (like status) rarely helps, while an index on a highly selective column (like order_id) can turn a full scan into a handful of I/Os.
Indexes in an OLAP Engine
Traditional B-tree indexes are built for OLTP workloads that fetch small numbers of rows by key. Analytical engines lean on different structures instead. DuckDB automatically maintains a min-max index (zone map) for every column in every row group, letting it skip whole blocks that can't match a filter without any manual index creation. On top of that, DuckDB supports an Adaptive Radix Tree (ART) index, created automatically for columns with a PRIMARY KEY or UNIQUE constraint, and manually via CREATE INDEX for point lookups on highly selective columns:
Copy code
CREATE TABLE orders (order_id BIGINT PRIMARY KEY, customer_id INTEGER, total DECIMAL);
CREATE INDEX idx_orders_customer ON orders(customer_id);
ART indexes must fit in memory during creation, so for large analytical tables it's often better to rely on zone maps and predicate pushdown than to index every column.
Related terms
A zone map, or min-max index, is a lightweight index that stores the minimum and maximum value of a column for each block of data, letting queries skip blocks that can't match a filter.
UNIQUE constraint →A UNIQUE constraint ensures that all values in a column, or combination of columns, are distinct across every row in a table.
Key-value store →A key-value store is a simple database model that maps unique keys to values, optimized for very fast lookups, writes, and updates by key, without the fixed schema or query flexibility of a relational database.
Full-text search →Full-text search is a technique for searching text content by relevance to a query — matching keywords, handling stemming and ranking results — rather than requiring an exact substring or equality match.
table →A table is a fundamental structure in a relational database that organizes data into rows and columns, similar to a spreadsheet.
DELETE statement →The DELETE statement removes rows from a table that match an optional WHERE condition, without dropping the table itself.
FAQS
No. Indexes speed up selective lookups but add storage and write overhead, and the optimizer may ignore a low-selectivity index in favor of a full scan if that's cheaper.
Not usually. DuckDB's automatic min-max zone maps and vectorized scans make most analytical filters fast without manual indexing; ART indexes mainly help with point lookups and constraint enforcement.
