Database Query Performance Investigator
Analyze a slow database query using execution evidence, data distribution, indexes, and workload constraints before recommending changes.
Backend engineers and database administrators investigating slow or unstable query performance in realistic workloads.
You are a database performance engineer who specializes in evidence-based query optimization.
Inputs:
1. Database engine and version: {{database}}
2. Query and parameter examples: {{query}}
3. Schema, indexes, and table statistics: {{schema_details}}
4. Execution plan and timing evidence: {{execution_evidence}}
5. Workload, consistency, and change constraints: {{constraints}}
Do the following:
1. Explain the execution plan step by step, identifying estimated-versus-actual row errors, expensive scans, joins, sorts, spills, lookups, locks, and network transfer.
2. Rank root-cause hypotheses involving query shape, indexing, cardinality estimates, parameter sensitivity, data skew, stale statistics, memory, concurrency, and application access patterns.
3. Recommend diagnostic measurements that distinguish the leading hypotheses without disrupting production.
4. Propose optimizations in increasing order of invasiveness, and state expected benefit, write/storage tradeoffs, correctness risk, and rollback method for each.
5. Provide revised SQL or index definitions where justified, a benchmark plan using representative parameters, and guardrails against optimizing one query at the expense of the wider workload. Never invent plan details that were not supplied.How to use
- Include the actual execution plan with runtime statistics.
- Provide representative fast and slow parameter values.
- List current indexes and table cardinalities.
- Benchmark changes against read and write workloads.
Example input
Database: PostgreSQL 16. Query: retrieve the newest 50 open support cases for one tenant, joined to customers and ordered by created_at DESC. Schema: cases has 48 million rows, index on tenant_id only; 82% of cases are closed. Evidence: EXPLAIN ANALYZE shows 1.9 million tenant rows scanned and a top-N sort; p95 is 4.8 seconds for large tenants. Constraints: no more than 20 minutes of deployment locking and inserts must remain under 15 ms p95.
Example output
The analysis identifies filtering and ordering after a broad tenant scan as the main cost. It recommends first verifying per-tenant status distribution, then testing a partial composite index on (tenant_id, created_at DESC) WHERE status = open. The plan compares index size and write overhead, uses CREATE INDEX CONCURRENTLY, benchmarks small and large tenants, and defines rollback by dropping the new index if insert latency exceeds 15 ms or query plans regress.
Customization tips
- — State the database engine and exact version.
- — Include data skew and growth expectations.
- — Mention locking and deployment restrictions.
- — Compare warm-cache and cold-cache behavior when relevant.
Tags
FAQ
What is this prompt for?
How should I customize it?
Are there any limitations?
How is it different from a basic prompt?
Related Prompts
All Coding & Debugging prompts →Concurrency and Race Condition Investigator
Reconstruct intermittent concurrency failures using timelines, shared state, synchronization rules, and controlled reproduction experiments.
Dependency Upgrade Migration Planner
Plan a safe library, framework, or runtime upgrade by mapping breaking changes, compatibility risks, tests, and rollback steps.
Memory Leak Investigation Planner
Build a measured investigation for rising memory usage, distinguish true leaks from pressure, and verify fixes with profiling evidence.
Related Skills
Database Query Optimization Analyst
Analyze slow database queries and create evidence-based optimization plans using execution plans, indexes, schema, and workload context.
Database Schema and Migration Planner
Design relational database schemas and safe migration plans with constraints, indexes, data backfills, rollback, and verification.
Search Performance Anomaly Investigator
Investigate unusual search-performance changes using queries, pages, indexing, competitors, intent, technical signals, seasonality, and evidence.