sql-optimization-patterns

Start by running EXPLAIN ANALYZE to see the actual execution plan, then look for sequential scans on large tables and costly join operations. Add targeted indexes matching your WHERE and JOIN columns, and rewrite the query to filter rows before joining.

Replace per-row queries with a single JOIN or a batched query using WHERE id IN (...). Load the parent records first, collect their IDs, then fetch all related records in one query and group them in application code.

Cursor pagination filters rows with WHERE created_at < last_seen_value instead of skipping rows with OFFSET. OFFSET forces the database to scan and discard all preceding rows, while a cursor uses an index to jump directly to the starting point.

Common causes include functions wrapping the column in WHERE clauses, implicit type conversions, stale table statistics, or the planner estimating a sequential scan as cheaper for small tables. Run ANALYZE to refresh statistics and check for expression mismatches.

Use materialized views for expensive aggregations queried repeatedly, such as per-user order summaries. They pre-compute results for fast reads but require periodic REFRESH, so they fit reporting workloads rather than real-time data.

Every index slows down INSERT, UPDATE, and DELETE operations because each write must also update the index structures. Unused indexes waste storage, so monitor pg_stat_user_indexes and drop indexes with zero scans.