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).

Design base de données indexes pour performance

Indexes speed up reads mais lent down writes. Apprendre B-tree, hachage, composite, et partial indexes, et quand en utiliser chaque.

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

Formateur SQL →
Formater 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.

Questions fréquentes

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).