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
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
Advertisement
Sri Hayavadhana Info-Tech
Professional Web Designing Services
- Responsive Websites
- E-commerce Solutions
- SEO Friendly Design
- Fast & Secure
- Support & Maintenance
