Production Database Query Performance Tuning Roadmap

Role: Principal Database Performance Architect with twenty years of database internals and query engine optimization experience.

Context

• Target RDBMS engine: {{database_engine}}
• Problematic SQL query text: {{problematic_query}}
• Table schema definitions and volume: {{table_volume_and_schema}}
• Query execution plan or explain output: {{current_execution_plan}}
• Target P99 latency threshold: {{p99_latency_target}}

Task

Generate an actionable query optimization and indexing plan to resolve production bottlenecks and safely meet the target latency threshold without introducing locking contention.

Method

1. Analyze the execution plan in {{current_execution_plan}} to identify full table scans, expensive hash joins, sorting bottlenecks, and temporary disk spills.
2. Cross-reference row cardinality and data distribution from {{table_volume_and_schema}} against filter predicates and join conditions in {{problematic_query}}.
3. Evaluate existing primary, foreign, and secondary indexes on {{database_engine}} to detect index misses or redundant indexing overhead.
4. Design targeted single-column, composite, or covering indexes tailored specifically to the query access patterns.
5. Rewrite non-sargable query predicates, correlated subqueries, or inefficient joins into sargable, engine-optimized alternatives.
6. Formulate a canary testing and benchmarking protocol to validate query cost reduction against {{p99_latency_target}}.
7. Detail a zero-downtime DDL deployment sequence including concurrent index creation and rollback safety triggers.

Constraints

• MUST prioritize zero-lock DDL index creation syntax specific to {{database_engine}}.
• MUST NOT recommend query rewrites that alter result set data types or deterministic ordering without explicitly stating trade-offs.
• All index recommendations must include explicit column ordering justifications based on selectivity.
• Every optimization step must map directly to an identified node in the execution plan.

Output format

• Section 1: Bottleneck Root Cause Analysis (ordered by compute cost impact, max 200 words)
• Section 2: Refactored SQL Query (annotated code block)
• Section 3: Indexing Strategy Specification (DDL statements and selectivity rationale)
• Section 4: Safe Rollout & Verification Checklist (5-7 sequential steps)

Self-review

• Did I verify that rewritten predicates are fully sargable for {{database_engine}}?
• Are composite index columns arranged by strict selectivity and equality-first ordering?
• Is the deployment plan completely safe against table-level write locks in production?