SQLsqlindexingoptimizationperformanceproduction-ready
Index Strategy and Query Optimization
SQL indexing patterns for covering indexes, partial indexes, composite keys, and EXPLAIN ANALYZE interpretation for query tuning.
title: "Index Strategy and Query Optimization" description: "SQL indexing patterns for covering indexes, partial indexes, composite keys, and EXPLAIN ANALYZE interpretation for query tuning." category: "SQL" language: "sql" tags: ["sql", "indexing", "optimization", "performance", "production-ready"] createdAt: "2025-04-01" updatedAt: "2025-04-15"
Overview
Index strategies that dramatically improve query performance: covering indexes for index-only scans, partial indexes for filtered subsets, composite indexes following the leftmost prefix rule, and expression indexes for computed lookups.
Code
-- Covering index (includes all needed columns)
CREATE INDEX idx_orders ON orders (customer_id, date DESC) INCLUDE (amount);
-- Partial index (smaller, faster for common queries)
CREATE INDEX idx_active ON users (email) WHERE is_active = true;Notes
- Composite index column order: equality columns first, then range/sort columns
INCLUDEcolumns are stored in the index but not used for lookup (avoids table access)- Partial indexes are much smaller and faster when you frequently query a subset
CONCURRENTLYprevents table locks during index creation (essential for production)- Monitor
pg_stat_user_indexesto identify and remove unused indexes (they slow writes) - The leftmost prefix rule means (A, B, C) index serves queries on (A), (A, B), and (A, B, C)