OPTIMIZATION OBJECTIVE
Speed up the monthly revenue report that aggregates two years of order history.
Optimization goal: reporting query optimization — aggregates, grouping, and analytical workloads.
Establish why this query is expensive before changing anything. Every recommendation must trace to evidence from this query, this plan, or this schema — no generic database advice.
DATABASE CONTEXT
Platform: not specified — keep recommendations standard-SQL where possible, and mark every platform-dependent claim as such.
- Where behavior differs by engine (index types, plan tooling, optimizer features), name which engines the advice applies to.
QUERY CONTEXT
Query:
```sql
[Paste the SQL query here]
```
Execution plan: not provided. Do not invent one. State the exact command to capture it — your engine's actual-execution-plan facility — and mark every plan-dependent conclusion as pending that evidence.
Tables and row counts: not provided — do not assume row counts. State the cardinality questions that block firm conclusions.
Existing indexes: not provided — do not assume any index exists. Before recommending new indexes, list what must be checked about the current ones.
PERFORMANCE SYMPTOMS
Runs 11 minutes; finance runs it daily; reads the whole orders table to return 24 rows.
OPTIMIZATION GOAL
Primary goal: Reporting Query Optimization.
Analysis priorities for this goal:
1. Separate the aggregation from the retrieval: how many rows are read to produce how few — that ratio is the optimization target.
2. Push filters below aggregations wherever semantics allow: aggregate the subset, never the universe.
3. Evaluate pre-aggregation honestly: materialized or indexed views, summary tables, incremental aggregation — each with its staleness tradeoff stated.
4. Check the grouping mechanics: hash versus ordered aggregation, and whether an index can feed the groups already in order.
EVIDENCE REVIEW
Evidence mode: Standard Analysis.
- Prefer evidence from the provided query, plan, and schema; where evidence is missing, label the assumption explicitly.
- Carry at least two candidate bottlenecks until evidence separates them.
BOTTLENECK ANALYSIS
- Identify the bottlenecks and, for each: what it costs, why it matters for this workload, and the estimated share of the total cost.
- Expected analysis areas for this goal: Rows-read vs rows-returned ratio; Filter pushdown opportunities; Pre-aggregation candidates and staleness; Aggregation strategy.
- Distinguish the bottleneck from its symptom — a slow sort caused by a missing filter is a filter problem, not a sort problem.
OPTIMIZATION OPPORTUNITIES
- Order opportunities by expected impact, highest first; state the basis for each estimate.
- For each opportunity: the change, the expected effect, and the evidence that predicts it.
- Any query rewrite must return identical results — call out every difference in NULL handling, duplicates, and ordering, or state explicitly that there is none.
- Do not tune speculatively: a change without an evidenced problem is risk without benefit.
INDEX RECOMMENDATIONS
- Every index recommendation must name the exact predicates, joins, or sorts it serves — no index without a clause.
- Justify column order for composite indexes, and say whether the index should cover (and at what storage and write cost).
- State each index's write tax: which inserts and updates it slows, and whether the workload can afford that.
- Check existing indexes first: a near-miss index that could be extended beats a new overlapping one.
TRADEOFF ANALYSIS
- Every recommendation carries its costs: write amplification, storage, maintenance burden, plan-stability risk, staleness (for pre-aggregation).
- State when NOT to apply each recommendation — the workload shape under which it backfires.
ASSUMPTIONS
- List every assumption made about data volumes, value distributions, index state, or workload patterns.
- Mark each assumption VERIFIED (with its evidence) or UNVERIFIED (with the query or command that would resolve it).
- Any recommendation that depends on an UNVERIFIED assumption must be flagged as conditional on it.
NON-GOALS
- Do not invent execution plans.
- Do not assume indexes exist.
- Do not assume row counts.
- Do not recommend changes without justification.
- Separate facts from assumptions throughout.
- Explain the tradeoffs of every change that has them.
- Schema redesign and application-level changes are out of scope — unless the evidence shows no query-level fix exists, in which case say so and stop.
OUTPUT REQUIREMENTS
- Present recommendations as an ordered list, highest expected impact first, each with its evidence and its tradeoffs.
- For each recommendation, include the verification step: how to measure the improvement — your engine's actual-execution-plan facility before and after, on production-shaped data.
- Where essential evidence is missing, the first recommendations are the commands to gather it — not guesses in its place.
- End with the open questions: what could not be determined from the provided context, and what would settle each.