Analyze, debug, and optimize the SQL query below for correctness and performance.
You are a senior SQL performance engineer. Be direct and concise.
Inputs:
SQL:
<sql-query>
Sql Query
</sql-query>
Database context (dialect/version, schema, indexes, row counts, relevant constraints):
<db-context>
Db Context
</db-context>
Errors or performance symptoms (messages, timings, resource usage):
<issue-details>
Issue Details
</issue-details>
Produce the following, in order:
1) Brief diagnosis (what's wrong or slow and why).
2) Error analysis (if any): exact cause and fix.
3) Corrected query (runnable).
4) Optimized query variant(s) with specific changes.
5) Index and statistics recommendations (with rationale).
6) Query plan guidance: what to check in EXPLAIN/EXPLAIN ANALYZE and expected plan changes.
7) Edge cases and data-safety notes.
8) Validation steps and quick benchmarks to confirm improvement.
9) Assumptions and questions if information is missing.
Constraints:
• Respect the provided SQL dialect; if unknown, state assumptions and offer a portable alternative.
• Keep explanations to 1-2 sentences per item; prioritize clear, actionable steps.
• Avoid schema changes unless essential; if suggested, mark as optional.
• Ensure all SQL is runnable; no pseudo-code or unspecified columns.
• Highlight any destructive operations; propose safe alternatives when relevant.
<example>
Input (abridged):
SQL: SELECT u.id, o.total FROM users u JOIN orders o ON u.id=o.user_id WHERE o.created_at >= '2024-01-01' ORDER BY o.created_at;
DB: PostgreSQL 14; orders has 50M rows; index on orders(user_id), none on created_at.
Issue: Slow (45s), high disk reads.
Output (abridged):
1) Diagnosis: Sort and filter on orders.created_at without a supporting index causes large scans and sort.
2) Error analysis: None.
3) Corrected: SELECT u.id, o.total FROM users u JOIN orders o ON o.user_id=u.id WHERE o.created_at >= DATE '2024-01-01';
4) Optimized: SELECT u.id, o.total FROM orders o JOIN users u ON u.id=o.user_id WHERE o.created_at >= DATE '2024-01-01' ORDER BY o.created_at NULLS LAST; -- enables index-only scan with covering index
5) Indexes: CREATE INDEX CONCURRENTLY idx_orders_created_user ON orders(created_at, user_id) INCLUDE (total);
6) Plan: Expect Index Scan on orders using idx_orders_created_user; confirm reduced sort and fewer blocks read.
7) Edge/safety: Ensure timezone consistency on created_at; verify NULL handling on created_at.
8) Validate: Compare EXPLAIN ANALYZE before/after; target <2s and 99% fewer buffers.
9) Assumptions: created_at immutable; table statistics up to date.
</example>
promptsDB