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 ✓

Never Do ✗

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

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)

# Comprehensive guides
references/error-catalog.md              # All 12 errors + solutions
references/explain-analysis.md           # Reading query plans
references/index-strategies.md           # Index types & selection
references/query-rewrites.md             # Before/after optimizations
references/performance-monitoring.md     # Long-term monitoring
references/optimization-workflow.md      # Systematic process

Dependencies

PostgreSQL Extensions:

• pg_stat_statements - Query performance tracking (built-in)
• pg_trgm - Trigram similarity search (optional, for fuzzy matching)

MySQL:

• performance_schema - Performance monitoring (enabled by default in 8.0+)

No additional dependencies required.

Known Issues Prevention

Complete Setup Checklist

Production Deployment:

• Enable pg_stat_statements or performance_schema
• Index all foreign key columns
• Index columns in WHERE, JOIN, ORDER BY clauses
• Replace SELECT * with specific columns
• Add LIMIT to all unbounded queries
• Use prepared statements (parameterized queries)
• Implement connection pooling (20-50 connections)
• Configure work_mem (256MB-1GB per connection)
• Configure shared_buffers (25% of RAM for PostgreSQL)
• Enable slow query logging (threshold: 100-1000ms)
• Run EXPLAIN ANALYZE on all critical queries
• Set up daily monitoring of pg_stat_statements
• Schedule ANALYZE after nightly bulk operations
• Monitor cache hit ratio (target: >99%)
• Review and drop unused indexes monthly

Production Example

Before Optimization:

-- Query: Fetch user orders
SELECT * FROM orders WHERE user_id = 123;

-- Performance:
-- Execution time: 2500ms
-- Seq Scan on orders (1M rows scanned)
-- Network: 50MB transferred
-- No index on user_id

After Optimization:

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

-- Optimize query
SELECT id, total, status, created_at
FROM orders
WHERE user_id = 123
LIMIT 100;

-- Performance:
-- Execution time: 12ms (208x faster!)
-- Index Scan using idx_orders_user_id (100 rows)
-- Network: 50KB transferred (1000x less!)
-- Covering index with INCLUDE

Result: 208x faster execution, 1000x less data transfer

For comprehensive optimization guidance, error resolution, and production patterns, load the appropriate reference files listed in "When to Load References" above.