# IndexHashJoin Selection Explanation

<!-- auto-oncall-optimization-record
{
  "schemaVersion": "auto-oncall-optimization-record-v1",
  "optimizationId": "index-hash-join-selection-explanation-2026-08-19",
  "problems": [
    {
      "problemId": "decoded-plan-mechanism-does-not-answer-optimizer-choice",
      "summary": "The diagnosis used decoded operator facts to explain why the execution was slow but did not directly explain how estimated Build/Probe cardinality and cost led the optimizer to choose IndexHashJoin.",
      "status": "optimized_verified",
      "applicability": "SQL plan-choice questions where decoded operator facts expose the selected join, Build/Probe inputs, and estimated versus actual cardinality.",
      "issueKeys": ["APID-29344"],
      "sourceRefs": ["https://tidb.atlassian.net/browse/APID-29344#problem:index-hash-join-choice-explanation-missing"],
      "signalTextIncludesAny": ["why IndexHashJoin was selected", "Build Probe cost explanation missing", "decoded plan but optimizer choice unanswered"],
      "observedThrough": "2026-08-19",
      "effectiveAt": "2026-08-20T11:07:40+08:00",
      "verifiedAt": "2026-08-20T11:08:33+08:00",
      "attemptedAction": "Require Scenario Router reconciliation to preserve three distinct conclusions already present at bounded strength in the Scenario Diagnostic Agent brief: why the optimizer selected the observed join from available estimates and bounded cost evidence, why this execution was slow from actual operator work, and whether an alternative join is proven better; preserve uncertainty when exact cost or comparable alternative-plan evidence is absent.",
      "evidenceUrl": "https://github.com/tidbcloud/aisupport/pull/1021"
    }
  ]
}
-->

- Optimization ID: `index-hash-join-selection-explanation-2026-08-19`
- Owner: Scenario Router reconciliation
- Source reference: `APID-29344` write replay
- Implementation status: merged in `tidbcloud/aisupport#1021` and deployed to
  staging as image `7720ab374414dbf114027a90db57b8dcb4fe9dd1`
- Effective status: `optimized_verified`

## Bounded problem

The Provider Executor supplied complete decoded operator facts, and the
query-latency Scenario Diagnostic Agent already concluded at bounded strength
that the underestimated lookup input supported the optimizer's cost decision,
while noting that the exact cost comparison was unavailable. It also separated
the greater actual scan work from the absence of a comparable alternative-plan
run. Scenario Router reconciliation dropped the first conclusion from the
canonical answer, so Judge returned `human_better` even though the upstream
evidence and local diagnosis contained the requested explanation.

## Candidate change

For plan-choice questions, require Scenario Router reconciliation to preserve
these distinct conclusions when the Scenario Diagnostic Agent brief contains
them:

1. why the optimizer selected the observed join using estimates and cost;
2. why the observed execution was slow using actual operator work; and
3. whether an alternative join is demonstrably better under comparable inputs.

Do not reopen Provider evidence, move this repair to Final Response, or claim
that `IndexJoin` is better without a same-parameter comparison.

## Verification evidence and next action

Write replay `24e31d39-7f22-4d93-8053-364d314b7f15` had complete Wetech
evidence and updated internal comment `23559192`, but Judge returned
`human_better` with the missing optimizer-choice explanation as the sole AI
gap. Add a Router reconciliation contract test and run a Provider-independent
simulation using the same bounded local diagnosis. Because Wetech OP execution
is no longer supported, this optimization uses that simulation as the
case-specific equivalent verification. The criterion is a direct bounded
estimate-and-cost explanation while retaining both the actual-work explanation
and the conclusion that an alternative join still requires comparable
execution evidence.

Verified on 2026-08-20 with simulated Router thread
`01a01d15-b32d-7661-8534-4542788ffe95`. All four checks passed: the draft
preserved the estimated-cardinality selection explanation, the actual operator
work explanation, the comparable-alternative boundary, and Provider-detail
non-disclosure. The result explicitly connected estimated 119 versus actual
518 rows to the bounded `IndexHashJoin` choice, compared 104,653 versus 27,889
processed keys for the slow mechanism, and kept `IndexJoin` unproven without a
same-parameter controlled run.

`npm test` passed all 926 tests locally and on the merged main commit. Staging
then reached image `7720ab374414dbf114027a90db57b8dcb4fe9dd1` with generation
475 observed, one of one replicas Ready, and successful `/healthz` and
`/readyz` responses. No Wetech request, Jira comment write, or Base refresh was
performed.

No further validation action is required for this bounded optimization. A
future supported OP case may provide regression evidence, but it is not a
prerequisite for the verified status.