How do I choose composite index column order?

Put the most selective column first (highest cardinality). For WHERE clauses, put equality conditions before range conditions. For a query WHERE a = 1 AND b > 2 ORDER BY c, use (a, b, c). The leading columns must match the query — a index on (b, a) cannot serve WHERE a = 1 efficiently.

When should I use a partial index?

When you query a subset of rows frequently. CREATE INDEX ON orders (customer_id) WHERE status = 'active' indexes only active orders — smaller, faster, less write overhead. Use for common queries on a small subset (active users, unpaid invoices, recent records).

TASK

Design database indexes for performance

Indexes speed up reads but slow down writes. Learn B-tree, hash, composite, and partial indexes, and when to use each.

1

Index types: B-tree (default, supports range and equality), Hash (equality only, faster), GIN/GiST (full-text, geometry), Composite (multiple columns, order matters). Indexes speed up WHERE, JOIN, ORDER BY, and GROUP BY but slow down INSERT/UPDATE/DELETE (index maintenance). Index selectively — every index has a cost.
2

SQL Formatter →
Format CREATE INDEX statements. For composite indexes, put equality conditions first, then range conditions, then sort columns. Example: CREATE INDEX ON orders (customer_id, status, created_at) for WHERE customer_id = ? AND status = ? ORDER BY created_at.

Frequently asked questions

How do I choose composite index column order?: Put the most selective column first (highest cardinality). For WHERE clauses, put equality conditions before range conditions. For a query WHERE a = 1 AND b > 2 ORDER BY c, use (a, b, c). The leading columns must match the query — a index on (b, a) cannot serve WHERE a = 1 efficiently.
When should I use a partial index?: When you query a subset of rows frequently. CREATE INDEX ON orders (customer_id) WHERE status = 'active' indexes only active orders — smaller, faster, less write overhead. Use for common queries on a small subset (active users, unpaid invoices, recent records).