# Database Query Performance Investigator

Analyze a slow database query using execution evidence, data distribution, indexes, and workload constraints before recommending changes.

## Prompt

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.

## Best for

Backend engineers and database administrators investigating slow or unstable query performance in realistic workloads.

## Compatible tools

- Claude
- ChatGPT

## 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.

## 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.

## 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.