# Plan And Rewrite Verification

Use this reference for bad access paths, large scans, join/subquery changes,
index proposals, statistics divergence, spill, or Plan Replayer review.

## Minimum evidence

Prefer, in order:

1. Complete `EXPLAIN ANALYZE` or decoded plan with operator timing and rows.
2. Exact SQL shape or digest plus Slow Query evidence.
3. Relevant schema/index metadata only when the plan does not already expose it.
4. Statistics health or Plan Replayer when optimizer choice remains unexplained.

Compare `estRows` with `actRows`, but do not equate every divergence with a bad
plan. Name the operator that dominates time, scan work, memory, spill, RPC,
backoff, or wait.

## SQL rewrite safety

Before showing a rewrite:

- preserve every filter and join condition;
- preserve placeholder semantics and call out parameter-order changes;
- preserve the result set, NULL behavior, and row cardinality when pushing an
  outer predicate into a derived table, subquery, or join input;
- preserve duplicate semantics when replacing `DISTINCT`, `IN`, or a derived
  table with `EXISTS` or a semi-join;
- avoid claiming a covering index without all referenced/filter columns;
- keep the proposal as a candidate until tested.

When a selective predicate exists only on the outer query, evaluate whether an
equivalent predicate can safely reduce rows inside a derived table or subquery.
Do not present that pushdown as a fix until equivalence is established: outer
joins, NULL-extension, aggregation, `LIMIT`, window functions, nondeterministic
expressions, and duplicate-producing joins can change semantics.

For an `EXISTS` rewrite, determine whether TiDB decorrelates it into a semi-join
or retains correlated `Apply`. A selective outer side plus an indexed inner
correlation key may benefit from correlated probes; a large outer side may make
that shape worse.

## Verification

Require a before/after comparison from the same representative parameters and
environment:

- total execution time;
- dominant operator time;
- `actRows`, scanned/processed keys and result rows;
- memory and disk spill;
- backoff/RPC/wait time;
- plan stability across representative parameter shapes.

For predicate pushdown or join/subquery rewrites, also compare returned row
count and representative result equality before relying on the performance
comparison.

Use Plan Replayer when schema, statistics, bindings, variables, or version are
needed to reproduce the optimizer choice. If the candidate shows no material
improvement, say so and continue diagnosis; do not re-label it as a fix.