OverviewHistoryStatsSecurity
npx skills add ...
Documentation
SKILL.md
npx skills add farmage/opencode-skills --skill database-optimizer
Optimizes database queries and improves performance across PostgreSQL and MySQL systems. Use when investigating slow queries, analyzing execution plans, or optimizing database performance. Invoke for index design, query rewrites, configuration tuning, partitioning strategies, lock contention resolution.
npx skills add farmage/opencode-skills --skill database-optimizer
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 |
| 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 |
EXPLAIN (ANALYZE, BUFFERS) output before optimizing — this is the baselineCONCURRENTLY (PostgreSQL) to avoid table locksANALYZE after bulk data changes to refresh statisticsVACUUM / statistics maintenanceWhen optimizing database performance, provide:
-- 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';-- 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;