From app-dev
This skill should be used when the user asks to "optimize a database query", "analyze a slow query", "review EXPLAIN output", "design indexes", "fix N+1 queries", or mentions "query optimization", "slow query", "EXPLAIN", "index", "performance", "query plan", "table scan", "index tuning", "N+1", "query analysis". Provides database query optimization, performance tuning, index design, and EXPLAIN plan interpretation.
npx claudepluginhub iwritec0de/claude-plugin-marketplace --plugin app-devThis skill uses the workspace's default tool permissions.
You are a database performance expert specializing in query optimization and index design.
Provides Ktor server patterns for routing DSL, plugins (auth, CORS, serialization), Koin DI, WebSockets, services, and testApplication testing.
Conducts multi-source web research with firecrawl and exa MCPs: searches, scrapes pages, synthesizes cited reports. For deep dives, competitive analysis, tech evaluations, or due diligence.
Provides demand forecasting, safety stock optimization, replenishment planning, and promotional lift estimation for multi-location retailers managing 300-800 SKUs.
You are a database performance expert specializing in query optimization and index design.
Key metrics to check in query plans:
| Metric | Good | Bad |
|---|---|---|
| Scan type | Index Scan, Index Only Scan | Seq Scan on large tables |
| Rows | Estimated ≈ Actual | Off by 10x+ (stale statistics) |
| Loops | 1 (or low) | Thousands (nested loop on unindexed join) |
| Sort | Index-backed | In-memory or disk sort on large sets |
Common node types: Seq Scan, Index Scan, Index Only Scan, Bitmap Index Scan, Hash Join, Merge Join, Nested Loop. Read reference/explain-analysis.md for full interpretation guide.
-- Composite index: column order matters (most selective first for equality)
CREATE INDEX idx_orders_status_date ON orders (status, created_at);
-- Partial index: index only what you query
CREATE INDEX idx_orders_active ON orders (created_at) WHERE status = 'active';
-- Covering index: include columns to avoid heap lookup
CREATE INDEX idx_orders_cover ON orders (user_id) INCLUDE (total, status);
Index types: B-tree (default, most cases), Hash (equality only), GIN (arrays, JSONB, full-text), GiST (geometry, range), BRIN (naturally ordered large tables). Read reference/index-strategies.md for details.
WHERE id > ? ORDER BY id LIMIT ?)Read reference/query-patterns.md for efficient pagination, CTEs, window functions, and materialized views.
# N+1 pattern (BAD): 1 query for list + N queries for details
SELECT * FROM orders; -- 1 query
SELECT * FROM items WHERE order_id = ?; -- N queries
# Fixed with JOIN or subquery (GOOD): 1-2 queries total
SELECT o.*, i.* FROM orders o
JOIN items i ON i.order_id = o.id; -- 1 query
ORM fixes: use eager loading (include, JOIN FETCH, with()), batch loading, or data loaders.
| Symptom | Likely Cause | Fix |
|---|---|---|
| Slow single query | Missing index or bad plan | EXPLAIN + add index |
| Many fast queries | N+1 pattern | Eager load / batch |
| Slow writes | Too many indexes | Audit and remove unused |
| Lock waits | Long transactions | Shorten tx, use SKIP LOCKED |
| Connection errors | Pool exhaustion | Increase pool, fix leaks |
| Gradual slowdown | Table/index bloat | VACUUM, REINDEX, OPTIMIZE |
reference/explain-analysis.md — Full EXPLAIN output interpretation for PostgreSQL and MySQLreference/index-strategies.md — Index types, composite ordering, partial indexes, maintenancereference/query-patterns.md — Efficient pagination, batch ops, CTEs, window functions