sql-optimization

name	sql-optimization
description	SQL query optimization expert covering execution plan analysis, indexing strategies, query rewriting patterns, and slow query diagnosis. Use when optimizing slow SQL queries, analyzing EXPLAIN output, designing indexes for performance, debugging database performance issues, or rewriting queries for better performance. Triggers on tasks involving slow queries, EXPLAIN ANALYZE, query tuning, index recommendations, N+1 query problems, or database performance bottlenecks.
metadata	{"version":"1.0.0"}

SQL Query Optimization

A comprehensive guide for analyzing and optimizing SQL queries — covering execution plan reading, indexing strategies, query rewriting patterns, common anti-patterns, and database-specific tuning.

When to Apply

• A query is running slower than expected
• You need to read and interpret EXPLAIN / EXPLAIN ANALYZE output
• Designing indexes for frequently-run queries
• Debugging N+1 query problems in application code
• Optimizing JOINs, subqueries, or aggregations
• Reducing database load in production
• Choosing between query approaches (JOIN vs subquery, CTE vs subquery, etc.)

---

1. The Optimization Workflow

1.1 Step-by-Step Process

1. IDENTIFY   → Find the slow query (slow query log, monitoring, user reports)
2. MEASURE    → Run EXPLAIN ANALYZE to get baseline metrics
3. ANALYZE    → Read the execution plan, find bottlenecks
4. OPTIMIZE   → Apply optimization techniques (index, rewrite, restructure)
5. VERIFY     → Compare before/after performance
6. MONITOR    → Set up alerts for regression

1.2 Before Optimizing — Ask These Questions

• Is this query run frequently or rarely? (Optimize hot queries first)
• Is the table large enough to matter? (< 10k rows: probably not worth it)
• Is the problem the query or missing indexes? (Most common cause)
• Can the data model be improved instead? (Sometimes better than query tuning)
• Is caching a better solution? (For read-heavy, rarely-changing data)

---

2. Reading Execution Plans

2.1 EXPLAIN vs EXPLAIN ANALYZE

-- EXPLAIN: Shows the planned execution (no actual execution)
EXPLAIN SELECT * FROM users WHERE email = '[e-mail]';

-- EXPLAIN ANALYZE: Actually runs the query and shows real timings
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[e-mail]';

Always use EXPLAIN ANALYZE for optimization — planned vs actual can differ significantly.

2.2 Key Metrics to Watch

Metric	Good	Bad	Meaning
Execution time	< 100ms	> 1s	Total query duration
Rows removed by filter	< 10%	> 50%	Inefficient filtering
Seq Scan on large table	Avoid	Present	Missing index
Nested Loop with many rows	< 1000 outer	> 10000 outer	Wrong join strategy
Sort with high cost	In-memory	Disk-based (external merge)	Work_mem too small
Buffers: shared read	Mostly hit	Mostly miss	Data not in cache

2.3 PostgreSQL Execution Plan Example

QUERY PLAN
──────────────────────────────────────────────────────────────────
Index Scan using idx_user_email on users  (cost=0.42..8.44 rows=1 width=100) (actual time=0.021..0.022 rows=1 loops=1)
  Index Cond: (email = '[e-mail]'::text)
Planning Time: 0.082 ms
Execution Time: 0.048 ms

What to look for:

• Index Scan → Good, using an index
• Seq Scan → Bad on large tables, means full table scan
• Nested Loop → OK for small datasets, bad for large
• Hash Join → Usually the best for large JOINs
• Merge Join → Good when both inputs are pre-sorted
• Sort → Check if it spills to disk (external merge Disk)
• actual time → Real time spent on this operation
• rows → Actual vs estimated (large difference = outdated statistics)

---

3. Indexing for Performance

3.1 The Golden Rule

Index every column that appears in WHERE, JOIN, ORDER BY, or GROUP BY clauses.

3.2 Index Types

Index Type	When to Use	Example
B-tree (default)	Equality, range, ORDER BY	WHERE price > 100
Hash	Equality only	WHERE status = 'active'
GIN	Full-text search, JSONB, arrays	WHERE data @> '{"key": "value"}'
GiST	Geospatial, range types	WHERE location <@ box
Partial	Frequently queried subset	WHERE is_active = true
Composite	Multi-column queries	WHERE user_id = 1 ORDER BY created_at

3.3 Composite Index Design

Rule: Leftmost prefix rule — the order of columns matters.

-- Index: (status, created_at)
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

-- These queries USE the index:
WHERE status = 'active'                          -- Uses first column
WHERE status = 'active' ORDER BY created_at      -- Uses both columns
WHERE status = 'active' AND created_at > '2024-01-01'  -- Uses both columns

-- These queries DO NOT use the index:
WHERE created_at > '2024-01-01'                  -- Skips first column
ORDER BY created_at                              -- Skips first column

3.4 Column Order in Composite Indexes

Order of priority:
1. Equality columns (=, IN) — Put first
2. Range/sort columns (>, <, ORDER BY) — Put after equality columns
3. Most selective (highest cardinality) first among equality columns

Example:

-- Query: WHERE tenant_id = 1 AND status = 'active' ORDER BY created_at DESC
-- Optimal index:
CREATE INDEX idx_orders_tenant_status_created
ON orders(tenant_id, status, created_at DESC);

3.5 When Indexes Don't Help

• SELECT * on small tables (< 1000 rows)
• LIKE '%pattern%' (leading wildcard) — use full-text search instead
• OR conditions across different columns (may not use index)
• Functions on indexed columns: WHERE LOWER(email) = 'test@...'
• Casting: WHERE created_at::date = '2024-01-01'

Fix for function issues:

-- Bad: Function on column prevents index use
WHERE LOWER(email) = '[e-mail]'

-- Good: Use expression index
CREATE INDEX idx_user_lower_email ON users(LOWER(email));

-- Bad: Casting prevents index use
WHERE created_at::date = '2024-01-01'

-- Good: Use range
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'

---

4. Query Rewriting Patterns

4.1 Avoid SELECT *

-- Bad: Fetches all columns, wastes I/O and memory
SELECT * FROM orders WHERE user_id = 123;

-- Good: Only fetch needed columns
SELECT id, total_amount, status, created_at
FROM orders WHERE user_id = 123;

4.2 JOIN vs Subquery

-- Bad: Correlated subquery (runs once per row)
SELECT u.name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- Good: JOIN with GROUP BY (runs once)
SELECT u.name, COALESCE(o.order_count, 0) AS order_count
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) o ON o.user_id = u.id;

4.3 EXISTS vs IN

-- Good: EXISTS (stops at first match, better for large subquery results)
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'active');

-- OK: IN (good when subquery result is small)
SELECT * FROM users u
WHERE u.id IN (SELECT user_id FROM orders WHERE status = 'active');

-- Rule: Use EXISTS for large subquery results, IN for small results

4.4 CTE vs Subquery

-- CTE: More readable, can be referenced multiple times
WITH active_users AS (
    SELECT id, name FROM users WHERE is_active = true
),
user_orders AS (
    SELECT u.id, u.name, COUNT(o.id) AS order_count
    FROM active_users u
    LEFT JOIN orders o ON o.user_id = u.id
    GROUP BY u.id, u.name
)
SELECT * FROM user_orders WHERE order_count > 10;

-- Note: In PostgreSQL 12+, simple CTEs are automatically inlined (no performance penalty)
-- In older versions, CTEs act as optimization fences (may be slower)

4.5 Pagination Optimization

-- Bad: OFFSET pagination (gets slower with larger offsets)
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;
-- Database must scan and discard 100000 rows

-- Good: Keyset pagination (constant time regardless of offset)
SELECT * FROM orders
WHERE created_at < '2024-01-15T10:30:00Z'  -- Last item from previous page
ORDER BY created_at DESC
LIMIT 20;

-- Good: Covering index for pagination
CREATE INDEX idx_orders_created ON orders(created_at DESC);

4.6 Avoid DISTINCT When Possible

-- Bad: DISTINCT requires expensive sort/dedup
SELECT DISTINCT u.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id;

-- Good: GROUP BY or EXISTS (often faster)
SELECT u.id, u.name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Or: Already-unique JOIN
SELECT u.id, u.name
FROM users u
JOIN (SELECT DISTINCT user_id FROM orders) o ON o.user_id = u.id;

---

5. JOIN Optimization

5.1 JOIN Type Selection

JOIN Type	When Database Chooses It	Manual Hint
Hash Join	Large table JOIN large table	Usually best choice
Merge Join	Both inputs already sorted	Good for range joins
Nested Loop	Small table JOIN large table (with index)	Good with index on inner table

5.2 JOIN Order Matters

Database joins tables in the order that minimizes intermediate results.

Rule of thumb:
1. Filter early (WHERE before JOIN)
2. Join most restrictive tables first
3. Use JOIN conditions (not WHERE) for join predicates

-- Bad: JOIN first, filter later (larger intermediate result)
SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.is_active = true AND o.status = 'completed';

-- Good: Filter first, then JOIN (smaller intermediate result)
SELECT u.name, o.total
FROM (SELECT id, name FROM users WHERE is_active = true) u
JOIN (SELECT user_id, total FROM orders WHERE status = 'completed') o
ON o.user_id = u.id;

5.3 LEFT JOIN Anti-Pattern

-- Bad: Using LEFT JOIN then filtering with WHERE (converts to INNER JOIN)
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.total > 100;  -- This eliminates users with no orders!

-- Good: Put the filter in the JOIN condition
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.total > 100;

-- Or: Use a subquery if you truly need LEFT JOIN behavior

---

6. N+1 Query Problem

6.1 Detection

N+1 pattern:
  1 query to fetch N parent records
  + N queries to fetch child records for each parent
  = N+1 total queries

Example (ORM pseudocode):
  users = User.all()                    # 1 query
  for user in users:
      orders = user.orders.all()        # N queries (one per user)
  # Total: N+1 queries

6.2 Solutions

Solution 1: Eager Loading (JOIN)

# Django
users = User.objects.prefetch_related('orders').all()

# SQLAlchemy
users = session.query(User).options(joinedload(User.orders)).all()

# Rails
users = User.includes(:orders).all

Solution 2: Batch Loading (IN clause)

# 1. Fetch all users
users = User.all()  # 1 query

# 2. Fetch all orders in one query
user_ids = [u.id for u in users]
orders = Order.filter(user_id__in=user_ids)  # 1 query

# 3. Group in memory
orders_by_user = defaultdict(list)
for order in orders:
    orders_by_user[order.user_id].append(order)
# Total: 2 queries (constant, not N+1)

---

7. Common Slow Query Patterns & Fixes

7.1 Pattern: Full Table Scan

Symptom: Seq Scan on table with millions of rows
Fix: Add appropriate index

7.2 Pattern: Filesort (External Merge Sort)

Symptom: "Sort Method: external merge Disk: 50MB"
Fix: Increase work_mem or add covering index for ORDER BY

-- PostgreSQL: Increase work_mem for current session
SET work_mem = '256MB';

-- Or: Create covering index
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC)
INCLUDE (total_amount, status);

7.3 Pattern: Row Estimate Mismatch

Symptom: Planned rows = 1000, Actual rows = 500000
Fix: Run ANALYZE to update statistics

-- PostgreSQL: Update table statistics
ANALYZE users;

-- For more thorough analysis
VACUUM ANALYZE users;

7.4 Pattern: Cartesian Product

Symptom: Nested Loop with rows = table1_count × table2_count
Fix: Add missing JOIN condition

-- Bad: Missing JOIN condition (cartesian product)
SELECT * FROM users, orders;  -- Every user × every order

-- Good: Explicit JOIN condition
SELECT * FROM users JOIN orders ON orders.user_id = users.id;

7.5 Pattern: Over-Counting

-- Bad: COUNT(*) scans all rows
SELECT COUNT(*) FROM orders WHERE status = 'completed';

-- Good: Use partial index + approximate count for large tables
CREATE INDEX idx_orders_completed ON orders(id) WHERE status = 'completed';
SELECT COUNT(*) FROM orders WHERE status = 'completed';

-- Or use estimate (PostgreSQL)
SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';

---

8. Database-Specific Tips

8.1 PostgreSQL

• Use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) for detailed output
• CREATE INDEX CONCURRENTLY to create indexes without locking the table
• Use INCLUDE columns for covering indexes
• Set random_page_cost lower for SSD storage
• Use pg_stat_statements extension to track query performance over time

8.2 MySQL

• Use EXPLAIN FORMAT=JSON for detailed execution plan
• ANALYZE TABLE to update index statistics
• Use FORCE INDEX hint when optimizer chooses wrong plan
• Enable slow_query_log to capture slow queries
• Use pt-query-digest (Percona Toolkit) for query analysis

8.3 SQLite

• Use EXPLAIN QUERY PLAN for execution plan
• Always use transactions for bulk operations
• ANALYZE command updates statistics
• Use WAL journal mode for better concurrency
• Consider WITHOUT ROWID for read-heavy tables

---

9. Quick Reference: Optimization Checklist

• Run EXPLAIN ANALYZE to identify bottlenecks
• Check for missing indexes on WHERE/JOIN/ORDER BY columns
• Verify composite index column order (leftmost prefix rule)
• Replace SELECT * with specific columns
• Replace correlated subqueries with JOINs
• Use keyset pagination instead of OFFSET for large datasets
• Check for N+1 query patterns in application code
• Ensure table statistics are up to date (ANALYZE)
• Consider covering indexes for frequent queries
• Use LIMIT during development/testing to avoid full table scans
• Check for cartesian products (missing JOIN conditions)
• Monitor pg_stat_statements / slow query log for regression

---

10. Quick Reference: Common Anti-Patterns

Anti-Pattern	Impact	Fix
Missing indexes on FK columns	Slow JOINs	Index all foreign keys
SELECT *	Wasted I/O, memory	Select only needed columns
N+1 queries	O(N) database calls	Use eager/batch loading
OFFSET pagination	Gets slower with page number	Use keyset pagination
Functions on indexed columns	Index not used	Use expression index or rewrite
Correlated subqueries	Runs N times	Rewrite as JOIN
LIKE '%pattern%'	Full table scan	Use full-text search
Outdated statistics	Bad query plans	Run ANALYZE regularly
No query limit in dev	Accidental full scans	Always LIMIT during testing
Cartesian product	Exponential row explosion	Add JOIN conditions