Query Optimization

Query optimization begins with the query planner: EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL reveals whether the planner chose a sequential scan, index scan, bitmap heap scan, or nested-loop/hash/merge join, and how many rows were actually returned vs estimated. The most common production performance killers are the N+1 problem (executing one query per row of a parent result set) and missing indexes on JOIN and WHERE columns. Systematic query tuning follows: profile, identify, rewrite, re-index, verify with load.

Key Points

• EXPLAIN ANALYZE runs the query and shows actual row counts vs planner estimates; large discrepancies indicate stale statistics — run ANALYZE or increase default_statistics_target.
• Seq Scan on a large table is not always wrong: if the query returns >10–20% of rows, a seq scan can outperform an index scan due to sequential I/O being faster than random reads.
• N+1 problem: fetching 100 posts then issuing 100 separate author queries = 101 round-trips; fix with JOIN, or ORM eager loading (Rails includes:, Hibernate JOIN FETCH), or DataLoader batching.
• The IN clause with subquery can trigger a correlated subquery on each row; rewrite as a JOIN or use EXISTS for large datasets.
• CTEs (Common Table Expressions): in PostgreSQL pre-12, CTEs were optimization fences (materialized); PostgreSQL 12+ inlines them by default — use MATERIALIZED keyword explicitly when needed.
• Partial result caching with materialized views: CREATE MATERIALIZED VIEW CONCURRENTLY refreshes without locking reads; schedule refresh via pg_cron for reporting tables.
• Connection-level query hints in MySQL (USE INDEX, FORCE INDEX) override the planner; prefer fixing statistics and rewriting queries over hints, which become maintenance burdens.
• Lazy loading (ORM default) defers queries to property access time — safe for single objects but catastrophic in loops; always profile ORM-generated SQL in staging with query log enabled.

Eliminating N+1 with a JOIN and reading EXPLAIN ANALYZE output

-- Identify N+1 in PostgreSQL: check total queries in slow log
-- Rewrite: fetch posts WITH authors in one query
SELECT p.id, p.title, p.body,
       a.id AS author_id, a.name AS author_name
FROM   posts p
JOIN   authors a ON a.id = p.author_id
WHERE  p.created_at > NOW() - INTERVAL '7 days'
ORDER  BY p.created_at DESC
LIMIT  50;

-- EXPLAIN ANALYZE to verify plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;
-- Look for: "Index Scan" (good), "Seq Scan" (check if expected),
-- "Hash Join" vs "Nested Loop" (Hash better for large sets),
-- "Rows Removed by Filter" (high = missing index or poor predicate)