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
  • INCLUDE columns 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
  • CONCURRENTLY prevents table locks during index creation (essential for production)
  • Monitor pg_stat_user_indexes to 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)