Composite Indexes
Indexes spanning multiple columns, ordered to match query filter and sort patterns for optimal lookup performance.
Description
A composite index is a single index defined over two or more columns. The column order within the index is critical because B-tree indexes store entries sorted lexicographically by the column sequence. An index on (status, created_at) efficiently serves queries that filter on status alone, or on status and created_at together, but it cannot efficiently serve a query that filters only on created_at -- this is the leftmost prefix rule.
When designing composite indexes, place columns with equality conditions before columns with range conditions. For a query like WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY created_at, an index on (status, created_at) is ideal because the equality filter narrows to a contiguous range in the index, and the remaining entries are already sorted by created_at, eliminating a separate sort operation.
Avoid creating overlapping composite indexes that share the same leading columns, as this wastes storage and maintenance overhead. If you have an index on (a, b, c) and another on (a, b), the latter is redundant because the first index already covers queries on (a, b). Use database-specific tools like pg_stat_user_indexes to detect redundant or underutilized composite indexes.
Prompt Snippet
Design composite indexes following the ESR (Equality, Sort, Range) rule: place equality-filtered columns first, then sort columns, then range-filtered columns. For the query SELECT * FROM orders WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20, create the index as (tenant_id, status, created_at DESC). Validate with EXPLAIN ANALYZE that the planner uses an Index Scan (not Index Only Scan unless covering) and confirm zero sort operations in the execution plan.
Tags
Related Terms
Database Indexing Strategies
Creating and managing indexes to accelerate query performance while balancing write overhead and storage costs.
Covering Indexes
Indexes that contain all columns needed by a query, allowing the database to satisfy the query entirely from the index without touching the heap table.
Query Execution Plans (EXPLAIN)
Using the database's query planner output to understand how a query is executed and identify performance bottlenecks.