| 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
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time
LIMIT ;
