Use when database slowness stems from infrastructure concerns rather than query authoring: server memory and I/O configuration, connection pooling, lock contention, VACUUM and statistics maintenance, partitioning design, or cloud-managed database...
Use when:
shared_buffers, work_mem, or InnoDB buffer poolDo not use when:
sql-pro)connection-pool-tuner)EXPLAIN (ANALYZE, BUFFERS) before any changesEXPLAIN ANALYZE, compare costs, measure wall-clock improvementOn cloud-managed databases (RDS, Cloud SQL, Aurora):
ALTER SYSTEMandmy.cnfedits are unavailable. Use parameter groups or the console instead.
Use sequential-thinking if available — it enforces the baseline-capture step and prevents skipping directly to index creation.
For each optimization task, provide:
| Claude | You |
|---|---|
| Reads EXPLAIN output and identifies plan patterns | Provide the actual EXPLAIN output |
| Recommends index type (B-tree, covering, partial, expression) | Run CREATE INDEX CONCURRENTLY in your environment |
| Generates parameter tuning recommendations | Apply via parameter group or ALTER SYSTEM |
| Writes validation queries to measure improvement | Confirm improvement in production-scale data |
| Flags cloud-managed platform constraints | Verify access level (console vs. direct connection) |
| Topic | Reference | Load When |
|---|---|---|
| Query Optimization | references/query-optimization.md | Slow queries, execution plan analysis |
| Index Design | references/index-design-patterns.md | B-tree, covering, partial, expression indexes |
| PostgreSQL Memory & WAL | references/postgresql-memory-wal.md | shared_buffers, work_mem, WAL config |
| PostgreSQL VACUUM & Locking | references/postgresql-vacuum-locking.md | VACUUM, connection pooling, lock management |
| MySQL Memory & I/O | references/mysql-memory-io.md | InnoDB memory, I/O config |
| PostgreSQL Monitoring | references/monitoring-postgresql.md | pg_stat_statements, connections, locks |
| MySQL Monitoring | references/monitoring-mysql.md | Performance schema, InnoDB status |
| Pattern | Symptom | Typical Remedy |
|---|---|---|
Seq Scan on large table | No filter selectivity | Add B-tree index on filter column |
Nested Loop with large outer set | Exponential row growth | Consider Hash Join; index inner join key |
cost=... rows=1 but actual rows=50000 | Stale statistics | Run ANALYZE <table> |
Buffers: hit=10 read=90000 | Low cache hit rate | Increase shared_buffers; add covering index |
Sort Method: external merge | Sort spilling to disk | Increase work_mem for the session |
-- Always use BUFFERS to see cache hit vs. disk read ratio
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
AND o.created_at > now() - interval '7 days';
MUST DO:
EXPLAIN (ANALYZE, BUFFERS) before any changes — this is the baselineCONCURRENTLY to avoid table locksMUST NOT DO:
ALTER SYSTEM on Amazon RDS or other cloud-managed databasessql-pro — rewriting slow queries when the server is correctly configuredconnection-pool-tuner — pool sizing after server config is validatedsre-engineer — monitoring and alerting on database golden signals