sql-optimization-patterns

name	sql-optimization-patterns
description	Transforms slow PostgreSQL queries into fast operations through systematic EXPLAIN ANALYZE, indexing strategies, N+1 elimination, cursor pagination, and materialized views. Use when diagnosing slow queries, optimizing database performance, or designing index strategies — always profile before optimizing.
argument-hint	[slow query, table name, or optimization goal]
allowed-tools	Read
context	fork
metadata	{"triggers":"slow query, query optimization, EXPLAIN ANALYZE, N+1 query, index strategy, cursor pagination, missing index, seq scan, query performance, database performance, slow endpoint, database slow, optimize SQL, PostgreSQL optimization, batch operations, materialized view, table partitioning, pg_stat_statements","related-skills":"database-schema-designer, sql-pro, python-dev, java-spring-api, nestjs-api, vector-database","domain":"infrastructure","role":"optimizer","scope":"implementation","output-format":"document"}
last-reviewed	2026-03-15

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

Problem	Diagnosis	Fix
Slow endpoint	EXPLAIN (ANALYZE, BUFFERS) — look for Seq Scan	Add index on WHERE/JOIN columns
N+1 queries	Count DB calls per request > 1	JOIN + eager load or batch query
Slow pagination	OFFSET on large table	Cursor-based pagination
Slow aggregation	GROUP BY without index	Partial index + filter before GROUP BY
Slow COUNT(*)	Full table count	pg_class.reltuples estimate or index-only count
Repeated expensive query	Same query hits DB repeatedly	Materialized view + scheduled refresh
Large table slow scans	Millions of rows, date filter	Table partitioning by date range

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 
LIMIT ;