SQL-Query-Optimierung mit Begründung

Optimize this SQL query. Database: [POSTGRES / MYSQL / SQLITE / MSSQL].

Steps:
1. SCHEMA ASSUMPTIONS - list what you assume about indexes, row counts, data types. Flag unknowns.
2. PROBLEMS IN CURRENT QUERY - list, with impact estimate (e.g. "forces seq scan", "executes subquery per row", "loses index due to function on column")
3. REWRITTEN QUERY - the optimized version. Include inline comments where the change matters.
4. INDEX SUGGESTIONS - only if missing indexes are a likely cause. Specify column order and type.
5. EXPLAIN-PLAN CHECK - what to look for in EXPLAIN ANALYZE to verify the fix actually helped.

Common fixes to check:
- SELECT * → explicit columns
- Functions on WHERE columns (avoid: `WHERE DATE(col)=...`)
- Correlated subqueries → JOIN / window function
- OR across multiple columns → UNION ALL or different index
- Late row elimination - move filters closer to source
- Missing LIMIT on exploratory queries

Rules:
- Do not rewrite a query you can't prove is equivalent. If unsure, annotate "semantics likely same, verify with test case"
- Do not propose index explosion - each index costs writes
- Be explicit about row-count dependencies (what helps on 10M rows may hurt on 1K)

Schema (relevant tables):
[SCHEMA]

Query:
[QUERY]

Typical size + known slow patterns (optional):
[INFO]

Wann nutzen

Wenn eine Query zu langsam ist oder EXPLAIN schlecht aussieht. Der Schema-Assumption-Teil zwingt das Modell, Annahmen offenzulegen - dort stecken die Fehler.

Use-Cases

• Report-Query läuft über Nacht durch.
• N+1-Query in ORM: gesammelte Variante erforderlich.
• Neue Migration: vorher Query-Pfade durchdenken.

Getestet mit

• Claude Opus 4.7
• GPT-5