Report Format

# SQL Query Explainability — Diagnostic Report

Preview Only - not for distribution

## Query Information

| Field            | Value                                                            |
| ---------------- | ---------------------------------------------------------------- |
| Query Identifier | {query_id}                                                       |
| Planning Time    | {planning_time} ms                                               |
| Execution Time   | {execution_time} ms                                              |
| DPU Estimate     | Compute: {compute}, Read: {read}, Write: {write}, Total: {total} |

## SQL Statement

\`\`\`sql
{sql_statement}
\`\`\`

## Plan Overview

\`\`\`
{formatted_plan_tree}
\`\`\`

## Findings

Each finding is presented with three H4 subsections, verbatim: "What we observed" → "Why it happened" → "Recommendation".
Findings are ordered by duration impact, starting from the most expensive.

{findings}

## Summary

{summary_table}

### Finding N: {Title} ({Severity} — {duration_or_context})

**Applies to:** {query_variant_tag}

#### What we observed

{Specific problem identified. Include a metrics table when quantitative evidence is available:}

| Metric   | Estimated | Actual | Error    |
| -------- | --------- | ------ | -------- |
| {metric} | {est}     | {act}  | {ratio}x |

#### Why it happened

{Root cause analysis with evidence from the plan, optimizer statistics, and actual cardinalities.
Show the optimizer's calculation when relevant (selectivity math, independence assumption).}

#### Recommendation

{Specific, actionable recommendation.}

{When the recommendation involves SQL, include the exact statement:}

\`\`\`sql
{recommended_sql}
\`\`\`

**Expected impact:** {What improvement the customer should expect. Ground the prediction in the
evidence you gathered — actual-vs-estimated row counts, Node Duration math, filter selectivity,
DPU breakdown. When the evidence supports a concrete prediction, state it that way (e.g.,
"Storage Lookup drops from 50 rows per loop × 2000 loops to 1 per loop ≈ 50× less read DPU;
execution should go from ~4s to ~80ms"). When the evidence is insufficient for a numeric
prediction, **do not fabricate one** — name the missing evidence explicitly (e.g., "Cannot
predict magnitude without `most_common_freqs` on this column; expected qualitative direction
is a reduction in Node Duration"). Honesty about what you don't know is always preferable to
a plausible-sounding number with no data behind it.}

#### Recommendation

This finding is a consequence of Finding N — resolving that finding addresses this one.
No separate action needed.

## Summary

| # | Finding | Severity   | Recommendation            | Expected Impact   |
| - | ------- | ---------- | ------------------------- | ----------------- |
| 1 | {title} | {severity} | {one-line recommendation} | {one-line impact} |
| 2 | {title} | {severity} | {one-line recommendation} | {one-line impact} |

## GUC Experiment Results

| Metric                        | Default    | Merge Join Only |
| ----------------------------- | ---------- | --------------- |
| Plan structure                | {describe} | {describe}      |
| Execution time                | {X}ms      | {Y}ms           |
| DPU (Total)                   | {N}        | {M}             |
| Key differences               | {describe} | {describe}      |
| Disabled strategy still used? | N/A        | {Yes/No}        |

## GUC Experiment Results

GUC experimentation skipped — original query execution time ({X}s) exceeds 30-second threshold.
Recommend testing alternative strategies manually:

\`\`\`sql
SET enable_hashjoin = off;
SET enable_nestloop = off;
SET enable_mergejoin = on;
EXPLAIN ANALYZE VERBOSE {original_sql};
\`\`\`

## Support Request Template

**Subject:** {one-line description of the anomaly}

**Query Identifier:** {query_id}

**Description:**
{2-3 sentences explaining what was observed, why it is anomalous, and that the query
results are correct but diagnostic output appears affected.}

**Table Statistics:**

- {table}: reltuples={N}, relpages={M}, actual COUNT(*)={X}
- Index used: {index_name} ({index_columns})
- {additional context specific to the anomaly}

**DPU Estimate:** Compute={N}, Read={M}, Write={W}, Total={T}

**Full EXPLAIN ANALYZE VERBOSE output:**
\`\`\`
{full_plan_output}
\`\`\`

## Next Steps

1. Apply the recommendations in order — Finding 1 first, then re-measure before deciding whether the subsequent findings still matter.
2. When any recommendation is in place, say **"reassess"** (or "I added the index" / "re-run the analysis"). I'll re-capture the plan, compare against the numbers above, and append an "Addendum: After-Change Performance" section to this report — so you can see the actual impact against the Expected Impact column.
3. If the observed change diverges significantly from the Expected Impact, I'll investigate the gap as a new finding rather than closing it out.

## Addendum: After-Change Performance

**Change applied:** {one-line description of what the user did, e.g., "Added composite index (clientid, _transactionstartdatetime) on associate"}

**Re-captured plan:** Query Identifier {new_query_id}, Execution Time {new_ms} ms, DPU {new_total}

| Metric                 | Before        | After        | Improvement      |
| ---------------------- | ------------- | ------------ | ---------------- |
| Total Query Cost       | {before_cost} | {after_cost} | {pct}% ↓         |
| Scan Type (main node)  | {before_scan} | {after_scan} | {status}         |
| Estimated Rows Scanned | {before_est}  | {after_est}  | {pct}% ↓         |
| Execution Time         | {before_ms}   | {after_ms}   | {pct}% ↓         |
| DPU (Total)            | {before_dpu}  | {after_dpu}  | {pct}% ↓         |
| Result Set             | {before_rows} | {after_rows} | Unchanged / Diff |

**Match against Expected Impact:** {Yes — matches the N% latency reduction predicted in Finding 1 / No — only X% observed, investigating}.

**Remaining findings status:** {Finding 2 still applies / Findings 2–3 now trivial given this change}.