Composite, Partial, Expression, and Covering Indexes

A composite index stores keys in a defined order; leading-column order matters for many query patterns. A partial index includes only rows matching a predicate, which can be effective for selective subsets such as active records. An expression index indexes a computed expression, such as lower(email), enabling matching case-insensitive searches. INCLUDE can store non-key columns for potential index-only scans.

Indexes should match real predicates and ordering. For example, an index on (customer_id, created_at DESC) may help a query that filters by customer and sorts that customer's records by creation time. It may not provide the same benefit for a query that filters only by created_at.

Composite and partial indexes

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

CREATE INDEX idx_users_lower_email
ON users (lower(email));

CREATE INDEX idx_active_jobs
ON jobs (created_at)
WHERE status = 'active';

💡 Each index supports a different pattern: customer-specific chronological access, case-insensitive email lookup, and queries restricted to active jobs.

Key Takeaways

Composite index column order matters.
Partial indexes can reduce index size for selective predicates.
Expression indexes can support searches on computed values.

Knowledge Check

Which index is designed to include only rows matching a predicate?

Composite, Partial, Expression, and Covering Indexes

STAY UPDATED WITH BYTES!