Slow Query Optimizer

Optimize this slow query. Work from evidence (the EXPLAIN output), not from generic advice — "add an index" without reading the plan is malpractice. **Query:** ```sql {{query}} ``` **EXPLAIN/EXPLAIN ANALYZE output:** ``` {{explain_output}} ``` **Engine & version:** {{engine}} **Table sizes & existing indexes (if known):** {{table_info}} **Step 1 — Read the plan aloud.** Walk through the execution plan in plain language: what the engine actually does, in order, and where the time/rows blow up. Identify THE bottleneck node — the one operation dominating cost — and say why the planner chose it (missing index? stale statistics? non-sargable predicate? misestimated row count?). **Step 2 — Check estimation quality.** Where estimated rows and actual rows diverge badly (in ANALYZE output), flag it: bad estimates cause bad plans, and the fix might be statistics, not indexes. **Step 3 — Fixes, ordered by effort:** 1. **Query rewrite only** — sargable predicates (no functions on indexed columns), EXISTS vs IN, eliminating SELECT *, pushing filters before joins, avoiding row-by-row correlated subqueries. Show the rewritten query. 2. **Index changes** — the exact CREATE INDEX statement, why THIS column order (equality → range; covering columns via INCLUDE where the engine supports it), and its write-amplification cost. 3. **Structural** — materialized views, denormalization, partitioning — only if 1 and 2 can't get there, with the maintenance burden named honestly. For each fix: predicted impact (order-of-magnitude reasoning from the plan) and how to verify after applying. If the plan shows the query is actually fine and the problem is elsewhere (lock contention, cold cache, network), say exactly that.