sandbaseai/workbuddy-skill/database-query-optimizer
Claude Code and agent skill by sandbaseai/workbuddy-skill
- community
Checking that the SKILL.md file is still in the repository.
Use when investigating slow SQL, execution plans, missing or redundant indexes, lock contention, deadlocks, or database scaling bottlenecks in PostgreSQL or MySQL.
Install
npx vskill@latest install sandbaseai/workbuddy-skill/database-query-optimizerSKILL.md
The same SKILL.md was published earlier as sandbaseai/workbuddy-skill/database-query-optimizer.
Database Query Optimizer
Purpose and safety boundary
Optimize database performance from measured evidence, not intuition. Start with read-only inspection and a reproducible query shape. Do not run arbitrary customer queries, expose sensitive rows, change schemas, tune global configuration, kill sessions, or rebuild indexes in production without explicit authorization, a maintenance plan, and recovery evidence.
EXPLAIN ANALYZE executes a statement. Never run it against an untrusted write query or live production traffic without a reviewed transaction/rollback plan. Prefer a sanitized representative dataset, a read-only transaction where valid, or plain EXPLAIN when execution itself is unsafe.
Inputs and baseline
Record database engine/version, schema and statistics freshness, query fingerprint, parameter class, dataset scale, concurrency, cache state, connection pool, replica role, and the exact environment. Capture:
- wall time and p50/p95/p99 latency under representative load;
- execution plan with estimates vs. actual rows, buffers/I/O, sort/hash spills, and parallelism;
- CPU, memory, I/O, cache hit rate, locks, deadlocks, connections, and replication lag;
- read/write mix, query frequency, business SLO, and acceptable change window.
Redact literals, identifiers, tokens, and customer data. Preserve the query fingerprint and enough shape to reproduce the issue.
Workflow
- Reproduce safely. Confirm the symptom and query fingerprint in a test or approved replica. Verify that the measured query is the same shape as the production hotspot.
- Read the plan. Compare estimated and actual rows, scan type, join order, join method, filter selectivity, sort/hash memory, buffer hits/reads, and planning/execution time.
- Rank bottlenecks. Separate stale statistics, missing/poor indexes, non-sargable predicates, bad cardinality, excessive rows, lock waits, connection pressure, I/O, and application-level N+1 behavior.
- Design one change. Choose the smallest candidate: query rewrite, predicate/parameter correction, targeted index, statistics refresh, partition/pruning adjustment, pool/backpressure change, or configuration change. Explain why it addresses the evidence.
- Check side effects. Estimate storage, write amplification, vacuum/maintenance, lock duration, replication, cache churn, plan stability, and compatibility with other queries. Reject redundant indexes and changes that trade an unmeasured regression for a local win.
- Validate in isolation. Apply one approved change in a disposable or canary environment. Use a bounded representative workload and keep the previous plan and rollback steps.
- Compare fairly. Repeat the same query class and workload, report warm/cold cache separately, and compare latency, rows, buffers, CPU/I/O, locks, writes, and replication. A lower planner cost alone is not success.
- Promote cautiously. Use a reviewed migration or deployment path, observe the agreed window, and stop/rollback if SLO, write latency, lock time, error rate, or replication health worsens.
- Document and monitor. Store before/after evidence, decision, scope, owner, expiry/review trigger, and a dashboard or alert for regression.
Plan-reading cues
| Evidence | Questions to test | |
|---|---|---|
| Large sequential scan | Is the table large, the predicate selective, and the data type/index expression compatible? | |
| Estimated rows far below actual | Are statistics stale, correlated, skewed, or filtered after a join? | |
| Nested loop with a large outer side | Is the inner lookup indexed and is the join order stable at production cardinality? | |
| Sort/hash spills to disk | Is the query shape or per-operation memory budget appropriate, without unsafe global tuning? | |
| High buffers read / low hit rate | Is the working set, access path, cache, or storage throughput the actual limit? | |
| Lock waits or deadlocks | Which transaction holds the lock, what is the order, and can scope/duration be reduced safely? | |
| Fast read but slow writes after an index | What is the write amplification, maintenance cost, and index usage over a representative window? |
Safe inspection examples
Use placeholders and a reviewed environment; do not paste raw customer data.
For an approved PostgreSQL index, prefer CREATE INDEX CONCURRENTLY where supported and plan for its longer build, failure cleanup, storage, and replica effects. Never assume a new index is useful until usage and write cost are measured.
Optimization record
Handoff checklist
- [ ] Query shape, engine/version, environment, data scale, and sensitive data handling are explicit.
- [ ] Baseline includes execution evidence and system/replication health, not only planner cost.
- [ ] Root cause and proposed change are tied to plan evidence.
- [ ] Only one approved change is tested at a time with rollback available.
- [ ] Read-only versus executing plan analysis is deliberate and safe.
- [ ] Before/after comparisons use equivalent workload and account for cache state.
- [ ] Write amplification, locks, maintenance, replicas, and neighboring queries are checked.
- [ ] Result, residual risk, monitoring, owner, and review trigger are documented.
Security
- Security scanPassed, 95 of 100Pattern checks on the skill's files.
- Model reviewPassed
Quality evaluation
- Task evaluationNot runNo evaluation yet. Run one in Skill Studio.
Source
- SKILL.md in the repositoryCheckingskills/database-query-optimizer/SKILL.md
Popularity
- 7-day trend score
- 0
Not refreshed yet.
Works with
Any agent vskill installs to: 56 targets in all. These 39 have a page on this site.
- Claude Code
- Cursor
- GitHub Copilot
- Windsurf
- Codex
- Gemini CLI
- Cline
- Amp
- Roo Code
- Goose
- Continue
- Kilo Code
- Junie
- Trae
- OpenHands
- Qwen Code
- AI Agent
- Tabnine Chat
- Sourcegraph Cody
- Amazon Q
- Firebase Genkit
- Sweep
- Devin
- Phind
- Mentat
- Claude Desktop
- Aider
- GPT Pilot
- Plandex
- bolt.new
- v0
- Replit Agent
- Anse
- TypingMind
- BetterChatGPT
- LibreChat
- Warp AI
- Pieces
- Fig AI
Embed badge
[](https://verified-skill.com/skills/sandbaseai/workbuddy-skill/database-query-optimizer)