An 11-minute report that reads two years of orders to return 24 rows: the rows-read to rows-returned ratio is the target — filters pushed down, pre-aggregation costed honestly.
Reporting queries have their own physics: the question is rarely the join, it is the sheer volume read to produce a small answer. This prompt uses the reporting goal: separate the aggregation from the retrieval and target the rows-read to rows-returned ratio; push filters below aggregations wherever semantics allow — aggregate the subset, never the universe; evaluate pre-aggregation honestly (materialized or indexed views, summary tables, incremental aggregation) with the staleness tradeoff stated, because finance deciding on day-old numbers is a business question, not a technical one; and check the grouping mechanics — whether an index can feed the groups already in order.
Measure the ratio
Rows read versus rows returned — an 11-minute, 24-row report states its own problem.
Aggregate the subset
Filters pushed below the aggregation wherever semantics allow — never aggregate the universe.
Cost the staleness
Pre-aggregation buys speed with freshness — the tradeoff is stated, and the business decides.
Because guesses aren't evidence. The QUERY CONTEXT says "Execution plan: not provided. Do not invent one," and NON-GOALS repeat "Do not assume indexes exist" and "Do not assume row counts." Where essential evidence is missing, the first recommendations become the commands to capture it — like your engine's actual-execution-plan facility — not tuning guesses. You run the generated prompt and gather that evidence yourself.
It requires result-identity. OPTIMIZATION OPPORTUNITIES states "any query rewrite must return identical results — call out every difference in NULL handling, duplicates, and ordering, or state explicitly that there is none," and pre-aggregation must state its staleness tradeoff. The prompt frames day-old finance numbers as a business decision, not a technical one — a recommendation for you to weigh, not an applied change.
It's the volume read to produce the answer — here 11 minutes reading the whole orders table to return 24 rows. The first analysis priority is to "separate the aggregation from the retrieval: how many rows are read to produce how few — that ratio is the optimization target," then push filters below aggregations so you aggregate the subset, never the universe.
Because it refuses indexes without evidence. INDEX RECOMMENDATIONS require every index to "name the exact predicates, joins, or sorts it serves — no index without a clause," to justify composite column order, and to state its write tax, after checking existing indexes first. With indexes 'not provided,' it lists what to check about current ones before proposing any new one.
A five-table join that got slow as data grew: establish cardinality first, check each join strategy against the data shape, and hunt the fan-out doing wasted work.
"Optimize this query" gets generic indexing advice. The optimization contract demands evidence: real bottlenecks, justified indexes with their write tax, and no invented plans.
Optimize what the engine does, not what the SQL looks like: cost concentration, estimate-vs-actual gaps, and plan warnings — with forensic evidence rules.
"Fix this error" gets guesses. The investigation contract gets a ten-stage diagnosis: facts separated from assumptions, alternatives weighed, fixes justified.
"Review this code" gets shallow comments. The review contract gets findings with severities, a checklist, and a verdict.
"Refactor this code" invites silent behavior changes. The refactoring contract preserves business rules, outputs, and side effects — and flags uncertainty instead of deciding it.
Build evidence-based SQL optimization prompts — goal, platform, and the evidence you have turn into a query tuning contract.