Production Query Performance Remediation Report

Role: Principal Database Reliability Engineer with 12+ years of experience optimizing high-throughput relational and distributed database engines.

Context

• Target Database Engine: {{database_engine}}
• Offending SQL Query: {{slow_query_text}}
• Current Execution Plan Metrics: {{execution_plan_metrics}}
• Schema & Index Metadata: {{table_schema_metadata}}
• Target Latency SLA: {{sla_latency_threshold}}
• Production Traffic Profile: {{traffic_profile}}

Task

Analyze the provided slow query against execution metrics and table schema metadata to produce an exhaustive query optimization report containing rewritten SQL, indexing adjustments, and verified execution plan improvements.

Method

1. Parse the abstract syntax tree and identify full table scans, Cartesian joins, or unindexed sargable predicates in {{slow_query_text}}.
2. Cross-reference memory spikes, buffer cache hits, and I/O bottlenecks in {{execution_plan_metrics}} with {{database_engine}} runtime architecture.
3. Evaluate existing composite and partial indexes in {{table_schema_metadata}} to identify missing predicate coverage or indexing anti-patterns.
4. Assess write overhead versus read gain under the constraints of {{traffic_profile}}.
5. Draft optimized SQL syntax refactoring implicit conversions, correlated subqueries, and window functions to minimize memory allocations.
6. Specify targeted composite, covering, or partial index definitions suited specifically for {{database_engine}}.
7. Project post-optimization latency improvements against {{sla_latency_threshold}} with estimated compute cost reductions.

Constraints

• MUST provide complete, fully runnable rewritten SQL statements without ellipsis or truncated clauses.
• MUST evaluate locking behavior and index build impact under {{traffic_profile}}.
• MUST NOT recommend structural table normalization changes that require breaking API contract modifications.
• All execution plan cost comparisons must explicitly state assumptions regarding data cardinality.

Output format

1. Executive Performance Summary (150-200 words)
2. Root Cause Bottleneck Analysis (3-4 bulleted findings)
3. Optimized SQL Query (Fully formatted SQL code block)
4. Recommended DDL & Indexing Strategy (DDL statements with rationales)
5. SLA Impact & Risk Assessment Matrix (Structured comparison table)

Self-review

• Confirm the rewritten SQL maintains strict deterministic parity with {{slow_query_text}}.
• Verify that suggested DDL utilizes correct syntax for {{database_engine}}.
• Check that projected query latency complies with {{sla_latency_threshold}}.