sql-optimization-patterns

Start by running EXPLAIN ANALYZE on the query to see the execution plan. Look for sequential scans on large tables, then add appropriate indexes, rewrite functions in WHERE clauses, and select only needed columns.

Replace per-row queries with a single JOIN or a batch query using WHERE id IN (...). Load related records in one query, then group results by foreign key in application code.

Use B-Tree for equality and range queries, GIN for full-text search, arrays, and JSONB, and BRIN for very large tables with natural ordering. Partial and covering indexes further reduce size and enable index-only scans.

OFFSET forces the database to scan and discard all preceding rows, so cost grows with offset size. Cursor-based pagination using WHERE on the last seen key with a matching index keeps performance constant.

Common causes include functions wrapping the column in WHERE, implicit type conversion, leading-wildcard LIKE patterns, and stale statistics. Create a functional index, match types exactly, and run ANALYZE to refresh statistics.

Use materialized views to pre-compute expensive aggregations or joins that are read frequently but tolerate slightly stale data. Refresh them on a schedule, using CONCURRENTLY in PostgreSQL to avoid blocking reads.