SQL Query Optimization Cheat Sheet
Provides practical techniques for reading EXPLAIN plans, indexing strategy, and rewriting slow SQL queries to reduce latency and resource use.
2 PagesAdvancedMar 2, 2026
Reading EXPLAIN ANALYZE
Spot the warning signs in a Postgres query plan.
sql
EXPLAIN ANALYZESELECT o.id, c.nameFROM orders oJOIN customers c ON o.customer_id = c.idWHERE o.status = 'pending';-- Look for:-- Seq Scan on large tables -> missing index-- Nested Loop with high row estimates -> consider hash/merge join-- "actual time" vs "cost" mismatch -> stale statistics, run ANALYZE-- rows=X (estimated) vs actual rows=Y -> large gap means bad planner estimate
Index Types
Different index structures and when to use them.
- B-tree index- Default index type; supports equality and range queries (<, >, BETWEEN), sorted output
- Hash index- Supports only equality lookups, faster than B-tree for exact matches but no range support
- Composite (multi-column) index- Indexes multiple columns together; column order matters — matches queries that filter on a leading prefix of the columns
- Partial index- Indexes only rows matching a WHERE condition (e.g., WHERE status = 'active'), smaller and faster for narrow queries
- Covering index- Includes all columns a query needs so the engine can answer from the index alone without hitting the table (index-only scan)
- GIN / GiST index- Postgres index types for full-text search, arrays, JSONB, and geometric data
Creating Effective Indexes
Composite, partial, and covering index examples.
sql
-- Composite index: order matters, put the equality column firstCREATE INDEX idx_orders_status_date ON orders (status, created_at);-- Partial index for a common filtered queryCREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';-- Covering index (Postgres INCLUDE) avoids a table lookupCREATE INDEX idx_orders_cover ON orders (customer_id) INCLUDE (status, total);-- Build without locking writes on a live tableCREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
Query Optimization Checklist
Common fixes for slow queries.
- Avoid SELECT *- Fetch only needed columns to reduce I/O and enable covering/index-only scans
- Sargable predicates- Avoid wrapping indexed columns in functions (e.g., WHERE YEAR(created_at)=2024) since it prevents index use; rewrite as a range
- LIMIT with ORDER BY- Pair LIMIT with an indexed ORDER BY column so the planner can stop early instead of sorting the full result
- N+1 queries- Looping and issuing one query per row instead of a single JOIN or batched IN() query kills performance
- Statistics freshness- Run ANALYZE (Postgres) or UPDATE STATISTICS (SQL Server) after large data changes so the planner's row estimates stay accurate
- Batch large writes- Break huge UPDATE/DELETE statements into chunks to avoid long locks and huge transaction logs
Window Functions Instead of Self-Joins
Replace correlated subqueries and self-joins with window functions for large speedups.
sql
-- Slow: correlated subquery re-scans orders per rowSELECT o.id, o.total, (SELECT AVG(total) FROM orders o2 WHERE o2.customer_id = o.customer_id) AS avg_totalFROM orders o;-- Fast: single pass with a window functionSELECT id, total, AVG(total) OVER (PARTITION BY customer_id) AS avg_total, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rnFROM orders;-- Top-N per group without a self-joinSELECT * FROM ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn FROM orders o) rankedWHERE rn <= 3;
CTE Materialization Control
Force or prevent CTE materialization to avoid accidental optimization fences.
sql
-- Postgres 12+: CTEs are inlined by default unless forced otherwise.-- NOT MATERIALIZED lets the planner push predicates into the CTEWITH recent_orders AS NOT MATERIALIZED ( SELECT * FROM orders WHERE created_at > now() - interval '30 days')SELECT * FROM recent_orders WHERE status = 'pending';-- MATERIALIZED forces the CTE to run once and spool results,-- useful when the same expensive CTE is referenced multiple timesWITH stats AS MATERIALIZED ( SELECT customer_id, COUNT(*) AS cnt FROM orders GROUP BY customer_id)SELECT * FROM stats WHERE cnt > 10UNION ALLSELECT * FROM stats WHERE cnt = 0;
Forcing Join Strategy and Plan Hints
Diagnose and, when necessary, steer the planner's join and scan choices.
sql
-- Postgres: disable a join/scan type for a session to compare plansSET enable_seqscan = off;EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';RESET enable_seqscan;-- SQL Server: force a specific join algorithmSELECT o.id, c.nameFROM orders oINNER LOOP JOIN customers c ON o.customer_id = c.idOPTION (HASH JOIN, RECOMPILE);-- MySQL: optimizer hints target a specific indexSELECT /*+ INDEX(orders idx_orders_status_date) */ *FROM orders WHERE status = 'pending';
Advanced EXPLAIN Signals
Deeper diagnostic signals beyond seq scans and row estimate mismatches.
- Buffers: shared hit vs read- EXPLAIN (ANALYZE, BUFFERS) shows cache hits vs disk reads; high 'read' counts mean the working set doesn't fit in shared_buffers
- Sort Method: external merge- Indicates the sort spilled to disk because work_mem was too small; raise work_mem or add an index that avoids the sort
- Rows Removed by Filter- A large value means the index/scan pulled many rows that were then discarded — the predicate isn't sargable or the index is on the wrong column
- Heap Fetches (index-only scan)- Non-zero heap fetches mean the visibility map is stale; VACUUM the table so index-only scans can skip the heap entirely
- Parallel Seq Scan / Gather- Shows the planner split work across background workers; if workers launched < workers planned, check max_parallel_workers_per_gather
- Bitmap Heap Scan + Recheck Cond- Recheck rows indicate the bitmap was lossy (exceeded work_mem), forcing a page-level recheck instead of exact row filtering
- Planning Time vs Execution Time- A planning time much larger than execution time signals overly complex queries or too many partitions/indexes to consider
Diagnosing Index Bloat and Unused Indexes
Find bloated or unused indexes that slow writes without helping reads.
sql
-- Unused indexes (never scanned) waste write throughput and storageSELECT relname AS table_name, indexrelname AS index_name, idx_scanFROM pg_stat_user_indexesWHERE idx_scan = 0ORDER BY pg_relation_size(indexrelid) DESC;-- Estimate index bloat ratio (simplified) via pgstattuple extensionCREATE EXTENSION IF NOT EXISTS pgstattuple;SELECT * FROM pgstatindex('idx_orders_status_date');-- Rebuild a bloated index without blocking writesREINDEX INDEX CONCURRENTLY idx_orders_status_date;
Pro Tip
When EXPLAIN shows a Seq Scan on a large table, don't assume you automatically need an index — check pg_stat_user_tables first; the planner may be choosing a seq scan because the table is small or the predicate isn't selective enough for an index to pay off.
Was this cheat sheet helpful?
Explore Topics
#SQLQueryOptimization#SQLQueryOptimizationCheatSheet#Database#Advanced#ReadingEXPLAINANALYZE#IndexTypes#CreatingEffectiveIndexes#QueryOptimizationChecklist#Databases#CheatSheet#SkillVeris
