From qe-framework
Optimizes database queries and improves performance across PostgreSQL and MySQL systems. Analyzes slow queries, execution plans, index design, configuration tuning, partitioning, and lock contention.
How this skill is triggered — by the user, by Claude, or both
Slash command
/qe-framework:Qdatabase-optimizerThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Senior database optimizer with expertise in performance tuning, query optimization, and scalability across multiple database systems.
Senior database optimizer with expertise in performance tuning, query optimization, and scalability across multiple database systems.
EXPLAIN ANALYZE before any changesEXPLAIN ANALYZE, compare costs, measure wall-clock improvement, document changes⚠️ Always test changes in non-production first. Revert immediately if write performance degrades or replication lag increases.
Load detailed guidance based on context:
| Topic | Reference | Load When |
|---|---|---|
| Query Optimization | references/query-optimization.md | Analyzing slow queries, execution plans |
| Index Strategies | references/index-strategies.md | Designing indexes, covering indexes |
| PostgreSQL Tuning | references/postgresql-tuning.md | PostgreSQL-specific optimizations |
| MySQL Tuning | references/mysql-tuning.md | MySQL-specific optimizations |
| Monitoring & Analysis | references/monitoring-analysis.md | Performance metrics, diagnostics |
-- Requires pg_stat_statements extension
SELECT query,
calls,
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
round(stddev_exec_time::numeric, 2) AS stddev_ms,
rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;
-- Use BUFFERS to expose 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';
| Pattern | Symptom | Typical Remedy |
|---|---|---|
Seq Scan on large table | High row estimate, no filter selectivity | Add B-tree index on filter column |
Nested Loop with large outer set | Exponential row growth in inner loop | 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 buffer cache hit rate | Increase shared_buffers; add covering index |
Sort Method: external merge | Sort spilling to disk | Increase work_mem for the session |
-- Covers the filter AND the projected columns, eliminating a heap fetch
CREATE INDEX CONCURRENTLY idx_orders_status_created_covering
ON orders (status, created_at)
INCLUDE (customer_id, total_amount);
-- Before optimization: save plan & timing
EXPLAIN (ANALYZE, BUFFERS) <query>; -- note "Execution Time: X ms"
-- After optimization: compare
EXPLAIN (ANALYZE, BUFFERS) <query>; -- target meaningful reduction in cost & time
-- Confirm index is actually used
SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders';
-- Inspect slow query log candidates
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;
-- Execution plan
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL 7 DAY;
EXPLAIN (ANALYZE, BUFFERS) output before optimizing — this is the baselineCONCURRENTLY (PostgreSQL) to avoid table locksANALYZE after bulk data changes to refresh statisticsVACUUM / statistics maintenancePattern 1: EXPLAIN ANALYZE Reading
-- Baseline query: full scan detected
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped' AND user_id = 42;
-- Output: Seq Scan, cost=0..5000 rows=1200, buffers: shared read=450
-- Remedy: CREATE INDEX idx_orders_user_status ON orders(user_id, status);
Pattern 2: Index Creation Strategy
-- Covering index eliminates heap fetches for frequent queries
CREATE INDEX CONCURRENTLY idx_orders_covering
ON orders (user_id, status)
INCLUDE (total, created_at);
-- After creation: verify with pg_stat_user_indexes (idx_scan > 0)
Pattern 3: Query Rewrite for Performance
-- Inefficient: correlated subquery (executes N times)
SELECT u.id, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count FROM users u;
-- Optimized: single join with aggregation
SELECT u.id, COUNT(o.id) AS order_count FROM users u
LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id;
-- OPTIMIZATION: [name]
-- Rationale: [why this change — e.g., "eliminates Seq Scan on large table"]
-- Baseline: [before metric — e.g., "cost=5000 rows=50000, 320ms"]
-- Expected: [after metric — e.g., "cost=50 rows=100, <5ms"]
-- Date: [YYYY-MM-DD] | Author: [name]
-- Validated: [yes/no] | Metrics: [actual after value if measured]
sqlfluff lint --dialect postgres <file>.sql to catch missing indexes, N+1 patterns, missing WHERE clauseslog_statement = 'all' (PostgreSQL) or general log enabled; DML changes trackedEXPLAIN before shippingEXPLAIN ANALYZE before & after; save baseline; document improvementWhen optimizing database performance, provide:
npx claudepluginhub inho-team/qe-framework --plugin qe-frameworkCreates, edits, and optimizes skills for Claude Code, including drafting, evaluating with test prompts, iterating on performance, and improving skill descriptions for better triggering accuracy.