Query Optimization

EXPLAIN ANALYZE: EXPLAIN ANALYZE runs the query and shows the actual plan, actual row counts, and actual timings per node. Key nodes:

  • Seq Scan: full table scan. Reasonable for small tables or low-selectivity queries.
  • Index Scan: per-row heap fetch after index lookup. Best for high-selectivity queries.
  • Index Only Scan: no heap access needed (covering index).
  • Bitmap Index Scan + Bitmap Heap Scan: builds a page bitmap, then reads pages in order. Good middle ground.
  • Hash Join: O(n+m), requires memory. Check Batches > 1 — means hash spilled to disk.
  • Merge Join: O(n+m) after sort. Look for Sort nodes upstream.
  • Nested Loop: O(n×m). Fine with indexed inner, catastrophic without.

Large discrepancy between rows= (estimate) and actual rows=: stale statistics. Run ANALYZE tablename.

Avoid SELECT *: Fetches all columns, including large TEXT/JSONB columns you don't need. Increases I/O, network transfer, and prevents index-only scans (the heap fetch is required for columns not in the index).

N+1 Problem: The most common ORM-related performance bug. You load N rows, then for each row, make an additional query — resulting in N+1 total queries.

Example: load 100 orders, then for each order load the customer: 1 query for orders + 100 queries for customers = 101 queries. Correct approach: JOIN orders o ON o.customer_id = c.id — 1 query.

In ORMs (like Go's GORM), this manifests as Preload being forgotten. In GraphQL, the DataLoader pattern batches all N sub-requests into one IN-clause query. Detection: log all queries during a request and look for identical queries repeating N times.

Pagination:

graph LR o["OFFSET 1000 LIMIT 20"] --> os["Scans and discards 1000 rows,<br/>THEN returns 20.<br/>Cost grows with page depth."] c["WHERE created_at < :cursor LIMIT 20"] --> cs["Index seeks straight to :cursor,<br/>returns next 20.<br/>Cost is constant regardless of page depth."]

OFFSET pagination: SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 1000. The database scans and discards 1000 rows before returning 20. At large offsets (page 50,000), this is slow: O(offset + limit).

Cursor-based pagination: WHERE created_at < :last_seen_cursor ORDER BY created_at DESC LIMIT 20. Starts from the last-seen record — O(log n + limit) using the index. More robust (no drift if rows are inserted between pages), always fast regardless of page depth. Downside: cannot jump to arbitrary pages.

Reduce JOINs with denormalization: For read-heavy, rarely-updated data, store redundant columns to avoid joins. A orders table storing customer_name (duplicated from customers) avoids the JOIN on every read. Tradeoff: if the customer's name changes, all denormalized copies must be updated.

Common pitfall

Reading EXPLAIN (without ANALYZE) and trusting the estimated costs as if they were real measurements is a frequent mistake — EXPLAIN alone never executes the query, it only shows the planner's predicted plan and cost, based on table statistics that might be stale. EXPLAIN ANALYZE actually runs the query and reports real timings and real row counts per node — the rows= (estimate) vs actual rows= gap it surfaces is exactly the signal for "statistics are stale, run ANALYZE tablename" mentioned above, and that signal is invisible without the ANALYZE keyword.