메뉴
Interprets EXPLAIN/EXPLAIN ANALYZE output to diagnose slow queries and recommend indexes or rewrites. Do NOT use for schema design or ORM configuration.
Codex 또는 Claude로 설치 이 Prompt를 복사해 Codex, Claude 또는 다른 어시스턴트에 붙여 넣으면 Skill 페이지를 검토하고 설치를 진행할 수 있습니다.
SOC 직업 분류 기준
Use to implement a REST or GraphQL API endpoint from a specification or description, including request validation, error handling, and accompanying tests. Activate for building a new API route, resolver, or handler end-to-end. Do NOT use for API design/contract creation, documentation generation, or frontend API consumption.
Use to implement background job processing with a queue, worker, retry with exponential backoff, dead letter queue, and monitoring. Activate when the request involves offloading work from the request cycle (emails, reports, data processing). Do NOT use for real-time event streaming, webhook handling, or scheduled cron jobs.
Use to scaffold a new project from scratch — generating directory structure, configuration files, entry points, and a README skeleton. Activate when starting a new project, service, or package and needing a production-ready starting point. Do NOT use for adding features to existing projects, generating tests, or CI/CD configuration.
Use to design and implement a caching layer for data that is expensive to compute or fetch. Activate for cache-aside, write-through, or write-behind patterns, TTL strategy, and cache invalidation logic. Do NOT use for database query optimization, CDN configuration, or HTTP response caching headers.
Use to generate a CI/CD pipeline configuration for building, testing, and deploying an application. Activate for GitHub Actions, GitLab CI, CircleCI, or similar pipeline tools for any tech stack. Do NOT use for infrastructure provisioning, Dockerfile creation, or deployment strategy design.
Use to generate production-ready code for a new feature, function, class, or module from a description. Activate for writing new code from scratch with error handling, inline documentation, and a self-review checklist. Do NOT use for refactoring existing code, code review, or test generation.
| name | database-query-slow-analysis |
| description | Interprets EXPLAIN/EXPLAIN ANALYZE output to diagnose slow queries and recommend indexes or rewrites. Do NOT use for schema design or ORM configuration. |
| version | 1.1.0 |
| time_saved | 20-40 minutes |
| license | Proprietary — Personal Use Only |
| category | debugging |
| complexity | Advanced |
| tokens | ~5000 |
| tags | ["database","slow-query","explain","index","query-optimization","debugging"] |
| author | vheins |
Analyze slow SQL queries using EXPLAIN data to identify missing indexes, inefficient joins, or stale statistics, providing exact fixes and maintenance steps.
| Variable | Type | Req | Description |
|---|---|---|---|
tech_stack | string | Yes | DB + ORM (e.g., "Postgres + Prisma") |
slow_query | string | Yes | SQL query text |
explain_output | string | No | EXPLAIN / ANALYZE logs |
schema | string | Yes | Table/Index definitions |
CREATE INDEX statements; explain accelerated paths and estimated gain.ANALYZE/VACUUM and checks for pg_stat_user_tables.EXPLAIN ANALYZE commands.| Case | Strategy |
|---|---|
| No EXPLAIN | Analyze query structure and schema; estimate cost based on size. |
| Indexes Ignored | Check for bloat, lock contention, or stale stats; recommend BUFFERS. |
| ORM patterns | Identify N+1 or missing eager loads; provide ORM-level code fixes. |
flowchart TD
A([Start]) --> B[Parse inputs]
B --> C{EXPLAIN available?}
C -- No --> D[Analyze structure for anti-patterns]
C -- Yes --> G[Parse EXPLAIN output]
D --> E[Identify columns to index]
E --> F[Provide EXPLAIN ANALYZE command]
G --> H[Identify scan types]
H --> I[Identify join strategies]
I --> J{Row count mismatch?}
J -- Large discrepancy --> K[Stale stats]
J -- OK --> L[List performance problems]
K --> L
L --> M[Write CREATE INDEX statements]
M --> N{Query suboptimal?}
N -- Yes --> O[Rewrite query]
N -- No --> P{ORM-generated?}
O --> P
P -- Yes --> Q[Identify ORM pattern]
P -- No --> R[Recommend maintenance]
Q --> R
R --> S([Output: Report])
F --> T([Output: Alternative diagnosis — structure-based recommendations])
@upstash/context7-mcp: Library documentation and examples.@modelcontextprotocol/server-sequential-thinking: Complex reasoning.| Version | Date | Description |
|---|---|---|
| 1.1.0 | 2026-03-20 | Restructured: moved examples/references, added fields |
| 1.0.0 | 2026-03-20 | Initial release |