sql-query-optimization

SQL Query Optimization

Status: Production Ready ✅ Last Updated: 2025-12-15 Latest Versions: PostgreSQL 17, MySQL 8.4 Dependencies: None

Quick Start (10 Minutes)

1. Identify Slow Query

-- PostgreSQL: Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

2. Analyze with EXPLAIN

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE user_id = 123;

-- Look for:
-- - Seq Scan on large tables → needs index
-- - High "Rows Removed by Filter" → poor selectivity
-- - Temp read/written → increase work_mem

3. Create Index

-- Add missing index
CREATE INDEX CONCURRENTLY idx_orders_user
ON orders(user_id);

-- Verify improvement
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123;
-- Execution time should drop 10-100x

Critical Rules

Always Do ✓

| Rule | Why | Example | |------|-----|---------| | Index foreign keys | JOINs need indexed columns | CREATE INDEX idx_orders_user ON orders(user_id) | | Use EXPLAIN ANALYZE before production | Verify query plan is optimal | EXPLAIN (ANALYZE, BUFFERS) <query> | | Select specific columns | Reduces data transfer 90% | SELECT id, name FROM users not SELECT | | Add LIMIT to unbounded queries | Prevents memory exhaustion | SELECT FROM logs ORDER BY id LIMIT 100 | | Use prepared statements | Prevents SQL injection + faster | db.query('SELECT * FROM users WHERE id = $1', [id]) | | Run ANALYZE after bulk operations | Updates query planner statistics | ANALYZE table_name | | Monitor pg_stat_statements | Track query performance over time | Review daily for regressions | | Use connection pooling | Reduces connection overhead 10x | new Pool({ max: 20 }) |

Never Do ✗

| Anti-Pattern | Problem | Fix | |--------------|---------|-----| | SELECT * in production | Fetches unnecessary columns | Select specific columns only | | Leading wildcard LIKE '%term%' | Cannot use index | Use full-text search instead | | String concatenation for SQL | SQL injection vulnerability | Use parameterized queries | | No LIMIT on large results | Memory exhaustion | Always add LIMIT + pagination | | N+1 queries in loops | Network latency × N | Use JOIN or batch loading | | Ignoring EXPLAIN output | Deploy slow queries to production | Always EXPLAIN before deploy | | Multiple INSERTs in loop | Slow bulk operations | Use batch INSERT with multiple VALUES | | OFFSET for pagination | O(n) time, scans skipped rows | Use cursor-based pagination |

Top 7 Critical Errors

1. Sequential Scan on Large Table

Symptom: Seq Scan on orders (cost=0.00..150000.00) on 1M+ rows Cause: No index on filter column Fix: CREATE INDEX idx_orders_column ON orders(column) Impact: 10-100x faster

2. Missing Index on Foreign Key

Symptom: Slow JOINs (5+ seconds) Cause: Foreign key columns not indexed Fix: CREATE INDEX idx_orders_user_id ON orders(user_id) Impact: 50-500x faster JOINs

3. N+1 Query Problem

Symptom: 1 + N queries for N records Cause: ORM lazy loading in loop Fix: Use JOIN or eager loading: SELECT u., o. FROM users u LEFT JOIN orders o ON u.id = o.user_id Impact: N queries → 1 query

4. Leading Wildcard LIKE

Symptom: WHERE name LIKE '%search%' sequential scan Cause: Index cannot match middle of string Fix: Use full-text search (GIN index) or trigrams Impact: 100-1000x faster

5. SELECT * in Production

Symptom: High network traffic, slow responses Cause: Fetches all 50 columns instead of needed 3 Fix: SELECT id, name, email (explicit column list) Impact: 90% less data transfer

6. Missing LIMIT on Large Results

Symptom: Server out of memory, query timeout Cause: Attempting to return 5M rows Fix: SELECT * FROM logs WHERE ... LIMIT 100 + pagination Impact: Constant memory usage

7. Stale Statistics After Bulk Load

Symptom: Wrong query plan chosen despite index Cause: PostgreSQL statistics outdated Fix: ANALYZE table_name after bulk operations Impact: Correct query plan selection

See references/error-catalog.md for all 12 errors with detailed solutions.

Common Patterns Summary

| Pattern | Use Case | Example | Performance | |---------|----------|---------|-------------| | B-Tree Index | Equality, range, sort queries | CREATE INDEX idx ON t(col) | Default, best general purpose | | Composite Index | Multi-column WHERE clauses | CREATE INDEX idx ON t(c1, c2) | 5-50x faster than single index | | Covering Index | Include all query columns | CREATE INDEX idx ON t(c1) INCLUDE (c2) | 2-10x faster (no heap fetch) | | Partial Index | Filter subset of rows | CREATE INDEX idx ON t(c) WHERE status='active' | 50-90% smaller index | | JOIN Rewrite | Replace IN subquery | INNER JOIN users u ON o.user_id = u.id | 5-20x faster than subquery | | Batch INSERT | Bulk data loading | INSERT INTO t VALUES (..),(..) | 10-100x faster than individual | | Cursor Pagination | Large offset performance | WHERE id > last_id LIMIT 100 | Constant time vs O(n) |

Configuration Summary

PostgreSQL Config

-- Increase work_mem for complex queries (reloadable - no restart needed)
SET work_mem = '256MB';

-- Increase shared_buffers for better caching (25% of RAM)
ALTER SYSTEM SET shared_buffers = '8GB';

-- IMPORTANT: shared_buffers requires a full PostgreSQL server restart!
-- This setting is NOT reloadable via pg_reload_conf()
--
-- To apply shared_buffers change:
-- 1. Stop PostgreSQL:   sudo systemctl stop postgresql
-- 2. Start PostgreSQL:  sudo systemctl start postgresql
-- OR use:              sudo systemctl restart postgresql
--
-- Verify the change took effect:
-- SHOW shared_buffers;

-- Enable auto-vacuum (reloadable - can use pg_reload_conf)
ALTER SYSTEM SET autovacuum = on;

-- Reload config (ONLY works for parameters that don't require restart)
-- This will NOT reload shared_buffers - restart required for that!
SELECT pg_reload_conf();

MySQL Config

# my.cnf
[mysqld]
innodb_buffer_pool_size = 8G  # 70% of RAM
max_connections = 500
slow_query_log = 1
long_query_time = 1

When to Load References

Performance Analysis:

• Load references/explain-analysis.md when: Reading EXPLAIN output, understanding query plans, analyzing buffer statistics, comparing PostgreSQL vs MySQL EXPLAIN
• Load references/performance-monitoring.md when: Setting up monitoring, tracking slow queries over time, monitoring cache hit ratios, identifying bloated tables

Index Optimization:

• Load references/index-strategies.md when: Choosing index type (B-Tree, GIN, GiST, Hash), creating composite indexes, determining column order, using covering indexes, implementing partial indexes, monitoring index usage

Query Optimization:

• Load references/query-rewrites.md when: Rewriting slow queries, converting subqueries to JOINs, eliminating N+1 queries, implementing pagination, optimizing LIKE queries, batching operations

Systematic Process:

• Load references/optimization-workflow.md when: Following step-by-step optimization process, creating optimization hypothesis, measuring improvements, monitoring long-term performance

Error Resolution:

• Load references/error-catalog.md when: Debugging specific errors (sequential scans, missing indexes, N+1 queries, etc.), understanding root causes, implementing verified solutions

Using Bundled Resources

Templates (Copy-Paste SQL)

# EXPLAIN query templates
templates/explain-query.sql

# Index creation patterns
templates/index-examples.sql

# Query rewrite examples
templates/query-rewrites.sql

# Monitoring queries
templates/monitoring-queries.sql

References (Deep Dives)

# Comprehens