SQL Optimization Patterns

Iron Law

PROFILE BEFORE OPTIMIZING — RUN EXPLAIN (ANALYZE, BUFFERS) ON THE ACTUAL SLOW QUERY BEFORE WRITING A SINGLE INDEX

When to Use This Skill

• Debugging slow-running queries in PostgreSQL
• Endpoint response times exceeding target SLA
• Designing performant database schemas for high-traffic tables
• Reducing database load and infrastructure costs
• Resolving N+1 query problems from ORM usage (SQLAlchemy, Prisma, R2DBC)
• Analyzing EXPLAIN query plans from production
• Implementing efficient composite and partial indexes
• Migrating from OFFSET pagination to cursor-based pagination

Do Not Use This Skill When

• You need cloud-native SQL or analytics platforms (BigQuery, Snowflake) — use sql-pro
• You need schema migration design — use database-schema-designer
• The database is not PostgreSQL (patterns are PostgreSQL-specific)

Quick Reference

Process

Step 1 — Identify the Slow Query

-- Find the top-10 slowest queries by mean execution time
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Step 2 — Profile with EXPLAIN

-- Always use ANALYZE + BUFFERS for real data
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email;

Key nodes to watch:

• Seq Scan on large table → add index
• Index Scan → good; Index Only Scan → best
• Nested Loop on large tables → may need Hash Join
• Hash Batches > 1 → memory spill, increase work_mem
• Rows estimate far from Actual Rows → run ANALYZE <table>

Step 3 — Apply the Right Fix

See resources/implementation-playbook.md for SQL code for each pattern.

Step 4 — Verify Improvement

-- Re-run EXPLAIN ANALYZE after index/change
-- Compare: Execution Time before vs after
-- Compare: Seq Scan → Index Only Scan

Optimization Patterns (Summary)

Load resources/implementation-playbook.md for full SQL code examples.

Stack Integration

Python / FastAPI (asyncpg + SQLAlchemy)

• N+1 → use selectinload or joinedload in SQLAlchemy async
• Batch insert → execute_many with asyncpg
• Slow query → enable echo=True on engine to capture SQL, then EXPLAIN

NestJS / Prisma

• N+1 → use Prisma include: { orders: true } instead of loop queries
• Raw SQL → prisma.$queryRaw for complex optimized queries
• Slow endpoint → enable Prisma query logging, identify the statement

Java / Spring Boot WebFlux (R2DBC)

• N+1 → use DatabaseClient with JOIN query instead of reactive loop
• Batch → R2DBC executeBatch() method
• Slow query → enable R2DBC logging, capture SQL for EXPLAIN

Anti-Patterns

• Creating indexes without running EXPLAIN ANALYZE first
• Function in WHERE clause preventing index use: WHERE LOWER(email) = ? — use expression index
• LIKE '%term' with leading wildcard — cannot use B-Tree index; use GIN + pg_trgm
• Over-indexing: each index slows INSERT/UPDATE/DELETE — only index proven slow paths
• OFFSET 100000 pagination on large tables — always replace with cursor
• Individual row inserts in a loop — always batch

Documentation Sources

• PostgreSQL EXPLAIN: Query MCP context7 with library ID /postgresql/postgresql
• pg_stat_statements: Query MCP context7

Reference Files

• resources/implementation-playbook.md — Full SQL code for all 8 optimization patterns, EXPLAIN annotation guide, monitoring queries

Related Skills

• sql-pro — Cloud analytics, HTAP, dimensional modeling, BigQuery/Snowflake
• database-schema-designer — Schema design, migration patterns, FK/index rules
• vector-database — pgvector HNSW/IVFFlat index alignment
• python-dev — SQLAlchemy async, asyncpg patterns
• java-spring-api — R2DBC reactive database access
• nestjs-api — Prisma ORM, raw query patterns