SQL Indexes and CREATE INDEX

CREATE INDEX builds a lookup structure over one or more columns so the database can find matching rows without reading the whole table. A UNIQUE index also refuses any row whose key is already there. In a row-store database like PostgreSQL or MySQL, indexes are the main tool for making lookups and joins fast. In DuckDB, a columnar engine, they matter far less for speed, and the reason to create one is usually the uniqueness rule.

A unique index

Garden Shop's orders carry a coupon_code. Build a small table of the codes in use, then make each code unique:

create table coupons as
select distinct coupon_code as code
from orders
where coupon_code is not null

create unique index coupons_code_idx on coupons (code)

The table holds three codes: SPRING10, SUMMER15, and WELCOME5. Inserting one of them again is refused:

insert into coupons values ('SPRING10')

DuckDB answers Constraint Error: Duplicate key "code: SPRING10" violates unique constraint. The index compares values exactly, though, so a different spelling of the same code gets in:

insert into coupons values ('spring10')

NULL gets in too, as many times as you insert it: a unique index treats each NULL as different from every other.

Index an expression

To make the rule case-insensitive, index lower(code) instead of the column. While spring10 and SPRING10 are both in the table, DuckDB refuses to build it:

create unique index coupons_code_lower_idx on coupons (lower(code))

Constraint Error: Data contains duplicates on indexed column(s). The rule has to hold for the rows already there before it can hold for new ones. Remove the duplicate, and the index builds:

delete from coupons
where code = 'spring10';

create unique index coupons_code_lower_idx on coupons (lower(code))

insert into coupons values ('Spring10')

This time the error names the expression: Duplicate key "lower(code): spring10" violates unique constraint.

Adding a unique rule to an existing table

In DuckDB, a unique index is the only way to make an existing column unique. ALTER TABLE can add a primary key, but not a UNIQUE constraint:

alter table customers add unique (email)

That fails with Not implemented Error: No support for that ALTER TABLE option yet! The index does the same job:

create unique index customers_email_idx on customers (email)

It works because all 20 customers have different emails. Try the same on city and it fails with the same Data contains duplicates error, because five cities appear twice. Find the rows in the way before you index:

select city, count(*) as customers
from customers
group by city
having count(*) > 1
order by city

A unique index is not a constraint as far as the catalog is concerned: duckdb_constraints() lists nothing for customers, and DESCRIBE leaves its key column empty. Look for it with duckdb_indexes(), below. For the rules CREATE TABLE can declare, see table constraints.

Ordinary and composite indexes

Without UNIQUE, an index enforces nothing. List several columns to index them together, as a composite index:

create index orders_customer_date_idx on orders (customer_id, order_date)

DuckDB accepts it, but read whether DuckDB uses it before you expect it to speed anything up. DuckDB does not support partial indexes (create index ... where status <> 'shipped' fails with Creating partial indexes is not supported currently).

Inspecting indexes

select index_name, table_name, is_unique, expressions
from duckdb_indexes()
order by index_name

After the steps above that is four rows: the two coupon indexes, the email index, and the composite one, whose expressions reads [customer_id, order_date]. The sql column holds each index's CREATE INDEX statement. Primary keys do not appear here even though DuckDB builds an index for them; find those with duckdb_constraints(). See SHOW TABLES and DESCRIBE for the rest of the catalog.

Does DuckDB use it?

EXPLAIN will not tell you. DuckDB decides whether to use an index while the query runs, so a plain EXPLAIN shows a sequential scan even when the index will be used. Run it with EXPLAIN ANALYZE instead:

explain analyze
select *
from customers
where email = 'theo.brandt@example.com'

The TABLE_SCAN box at the bottom says Type: Index Scan: DuckDB looked the email up in customers_email_idx. Change the query slightly, and it scans the whole table again (Type: Sequential Scan):

• where lower(email) = '...': the index holds email, not lower(email).
• where email like 'theo%': a pattern is not an equality.
• The same email = filter in a query that joins customers to orders.
• where customer_id = 1 on orders, even with the composite index on (customer_id, order_date), and even when both columns are filtered.

Why DuckDB needs fewer indexes

DuckDB's documentation describes its ART indexes (adaptive radix trees, the kind CREATE INDEX builds) as mainly for enforcing primary keys and unique rules, and for speeding up point lookups and very selective filters. Its performance guide is specific about the rest:

• Every table already has a lighter index. DuckDB keeps the minimum and maximum of each column for every block of rows (a "zonemap") automatically, and skips blocks whose range cannot match a filter. That works best when the data is stored roughly in order, which is why the guide recommends loading data sorted by the column you filter on.
• An index helps only a few rows at a time. DuckDB uses one only when a filter matches at most 2,048 rows or 0.1% of the table, whichever is larger (the index_scan_max_count and index_scan_percentage settings). For anything wider, reading the column is faster.
• It does not speed up joins, aggregation, or sorting. The guide says so directly. In a row-store database, an index on a join key is often the first fix for a slow join; in DuckDB it does nothing for it.
• It costs on every write. Inserts, updates, and deletes on an indexed table are slower, and the index must fit in memory while it is built. The guide recommends creating an index only when you need the constraint or have highly selective lookups, and creating it after a bulk load rather than before.

On SQLShed's datasets, a few dozen rows each, none of this is measurable. Use indexes here for the uniqueness rule, and read the speed advice as what applies when you load millions of rows.

Dropping an index

drop index orders_customer_date_idx;

drop index if exists orders_customer_date_idx

The second statement does nothing, without an error. Dropping a table drops its indexes with it. There is no CREATE OR REPLACE INDEX; it is a Parser Error. To change an index, drop it and create it again.

What can go wrong

• An index pins the table's shape. While any index exists on a table, DuckDB refuses to rename or drop its columns, or rename the table itself.

alter table customers rename column email to email_address

The message is Dependency Error: Cannot alter entry "customers" because there are entries that depend on it. It names no index, so check duckdb_indexes() for the table. Adding a column still works. Drop the index, make the change, and create the index again:

drop index customers_email_idx;

alter table customers rename column email to email_address;

create unique index customers_email_idx on customers (email_address)

• Index names are shared across tables. An index name must be unique within the schema, not just the table, so name indexes after their table (customers_email_idx), or a second table's email_idx fails with Index with name "email_idx" already exists!
• A unique index on a column does not protect an expression. SPRING10 and spring10 are different keys. Index lower(code) if case should not matter.
• Updates can trip a unique index on large tables. DuckDB's documentation warns that an UPDATE to an indexed column runs as a delete plus an insert, one batch of 2,048 rows at a time, so swapping values between rows in different batches can report a duplicate that would not exist at the end.
• An index you expect to help may not be used. Check with EXPLAIN ANALYZE, not EXPLAIN, before you keep one for speed.

Across databases

CREATE [UNIQUE] INDEX name ON table (columns) is close to universal. What differs is what indexes do for speed, and the options around them.