How to CREATE INDEX in Redshift

Does Amazon Redshift support the CREATE INDEX command?

Redshift is a column-oriented, massively parallel analytic database that does not implement secondary B-tree indexes. Any attempt to run CREATE INDEX returns an error. Instead, Redshift relies on SORT KEYs, DIST KEYs, zone maps, and result caching to accelerate queries.

What is the standard CREATE INDEX syntax?

PostgreSQL uses:

CREATE INDEX index_name ON table_name (column1, column2);

Redshift’s SQL parser lacks this statement, so the same syntax is unsupported.

How can I speed up lookups without indexes?

Use SORT KEY for range filters and ORDER BY

A SORT KEY physically orders data blocks, enabling zone maps to skip entire blocks during scans. Choose columns frequently filtered by range or used in ORDER BY, such as order_date.

CREATE TABLE Orders (
id BIGINT IDENTITY(1,1),
customer_id BIGINT,
order_date DATE,
total_amount NUMERIC(12,2)
)
SORTKEY (order_date);


Add DIST KEY for large joins

Set DISTKEY on a high-cardinality column used in joins—customer_id for Orders and Customers tables keeps related rows on the same node and avoids network shuffles.

CREATE TABLE OrderItems (
id BIGINT IDENTITY(1,1),
order_id BIGINT,
product_id BIGINT,
quantity INT
)
DISTKEY(order_id)
SORTKEY(order_id);


Example: speeding up recent-orders lookup

The query below filters the last 7 days. With order_date as SORT KEY, only the most-recent storage blocks are scanned, delivering sub-second results.

SELECT o.id,
c.name,
o.total_amount
FROM Orders o
JOIN Customers c
ON c.id = o.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '7 day';


Best practices when indexes are unavailable

• Store time-series data in append-only tables with order_date as SORT KEY.
• Use EVEN distribution when no clear join key exists.
• Refresh materialized views on heavy aggregations instead of creating indexes.

Common mistakes and fixes

Running CREATE INDEX and waiting for it to appear

The command fails because Redshift does not implement it. Rewrite the table with proper SORT and DIST keys.

Choosing a low-cardinality column as SORT KEY

A column with few distinct values, like status, clusters similar rows together, forcing full scans. Pick high-cardinality or time-based columns.