sql-optimization-patterns

Run EXPLAIN ANALYZE on the query to see actual execution times, scan types, and join methods. Look for sequential scans on large tables, then add appropriate indexes, rewrite functions in WHERE clauses, and filter rows before joining.

Focus on scan type (Seq Scan vs Index Scan), estimated versus actual rows, cost, and actual time per node. Large row estimate mismatches indicate stale statistics, which running ANALYZE on the table can fix.

Use B-Tree for equality and range queries, GIN for full-text search and JSONB, and partial or covering indexes for filtered subsets. Composite index column order must match your query's filter and sort patterns.

Common causes include functions wrapping the column in WHERE, implicit type conversion, leading-wildcard LIKE patterns, and stale table statistics. Create a functional index matching the expression or rewrite the predicate to match the index.

Avoid over-indexing because every index slows down INSERT, UPDATE, and DELETE operations and consumes storage. Check pg_stat_user_indexes for indexes with zero scans and drop unused ones.

Replace per-row queries with a single JOIN or a batched query using WHERE id IN (...). Then group the results by the parent key in application code to reconstruct the original object graph.