planner proposal: introduce trivial plan

## Introduction

This design proposes a SQL Server-like "Trivial Plan" optimization-level framework for TiDB's Volcano planner, aiming to solve "simple-enough" queries with lower compile cost. The framework has two levels:

- `TRIVIAL_SAFE`: when the optimization problem can be **proven to have a single feasible physical plan** under semantic constraints, build that plan directly.
- `TRIVIAL_AGGRESSIVE`: when there are still `> 1` candidates but the query shape is simple enough, use a limited set of rules/heuristics (a "trivial stage") to quickly produce a "good-enough" implementation and decide not to enter FULL (not guaranteed to match FULL); fall back to FULL on ineligibility or high-risk signals.

Both paths happen after logical optimization and before FULL physical optimization (`after logicalOptimize` / `before physicalOptimize`), so we can reduce compile cost without sacrificing semantic correctness.

To avoid further scattering shortcut branches on top of today's fast path, this document also proposes a Phase 0 cleanup: explicitly separate **context shaping** (bindings/hints/set_var/engine constraints) from **optimization level selection / plan construction / shared validations**, so `FAST_POINT/TRIVIAL_SAFE/TRIVIAL_AGGRESSIVE/FULL` can reuse the same finalized semantics and common checks.

## Motivation or Background

### From first principles: what an optimizer should minimize

In practice, an optimizer should not only minimize execution cost, but:

```
TotalCost = CompileCost(search effort) + ExpectedExecCost(plan)
```

In OLTP / short-query / high-concurrency compilation workloads, `CompileCost` can be comparable to (or even dominate) `ExpectedExecCost`. For ad-hoc statements with low reuse, deeper search often has diminishing returns.

So we need a mechanism to stop searching early when the potential execution benefit is smaller than the compilation cost of continued search.

### What "trivial" really means

Trivial does not mean execution is trivial. It means the **optimization problem** is trivial: under semantic constraints and physical capabilities, the plan space is already tiny or can be stably solved under low optimization effort, so the marginal value of full cost-based search is near zero.

This document borrows SQL Server's idea of treating trivial as a distinct optimization stage, and then maps it into two engineering levels in TiDB: `TRIVIAL_SAFE` (provably single-solution) and `TRIVIAL_AGGRESSIVE` (limited-rule fast solving with strong fallback).

### TiDB current state and pain points

TiDB's (Volcano) planning pipeline (simplified):

```
executor.(*Compiler).Compile
-> planner.Optimize
-> optimizeNoCache
-> core.TryFastPlan (PointGet/BatchPointGet/...)
-> build logical plan
-> core.VolcanoOptimize
-> logicalOptimize
-> physicalOptimize
- RecursiveDeriveStats
- FindBestTask (skyline + cost search)
-> postOptimize
```

Today there is only one shortcut mode: FAST_POINT (existing fast path).

- Entry points: `pkg/planner/optimize.go:optimizeNoCache`, `pkg/planner/core/point_get_plan.go:TryFastPlan`.
- Behavior: recognizes point-access patterns from AST and builds a physical plan directly, bypassing Volcano physical optimization.

All non-FAST_POINT queries go through FULL. The main compile hotspot is `pkg/planner/core/optimizer.go:physicalOptimize`:

- `RecursiveDeriveStats` builds ranges and loads/estimates stats for many candidate access paths on `DataSource`;
- then `FindBestTask` performs skyline + cost-based search over those candidates.

The pain points:

- FAST_POINT is narrow (point reads/updates/deletes).
- Many "simple but single-table with many indexes" queries still go through FULL, with compile cost roughly linear in the number of access path candidates.
- Fast path happens too early in the pipeline (e.g. before bindings are finalized), forcing ad-hoc semantic eligibility checks inside fast path (e.g. stable result mode disables it) and risking inconsistency in common semantic checks (table mode/lock/privilege). Adding another shortcut layer without cleanup would further hurt maintainability.

## Detailed Design

### Goals and Non-goals

Goals (target state):

- Introduce two trivial levels in Volcano, `TRIVIAL_SAFE` and `TRIVIAL_AGGRESSIVE`, positioned between `FAST_POINT` and `FULL`.
- Enable `TRIVIAL_SAFE` only when "trivial" is provable (single solution), and treat "FULL-equivalent" as a hard constraint.
- Enable `TRIVIAL_AGGRESSIVE` only for very simple query shapes (in `AGGRESSIVE` mode): use a limited set of rules/heuristics to build a "good-enough" plan inside a trivial stage and stop; it is not guaranteed to match FULL, but must preserve semantic correctness and must fall back to FULL when ineligible or risky.
- Reduce compile overhead in Volcano's physical stage:
- avoid repeating range building, stats derivation, and cost evaluation across a large number of candidates;
- for `TRIVIAL_SAFE` hits, skip FULL `physicalOptimize` entirely (especially the full `FindBestTask` search).
- Provide observability: record hit/fallback reasons.

Phased delivery: phase 1 focuses on `TRIVIAL_SAFE`. We still include `TRIVIAL_AGGRESSIVE` in the overall framework and observability, but land it later behind `AGGRESSIVE` mode.

Non-goals (phase 1):

- Not trying to cover all "simple" SQL (start conservative).
- Not primarily about "more aggressive pruning" or cost model changes.
- Not covering join/agg/window/subquery/CTE/union (strong coupling).
- Not covering TiFlash/MPP initially.

### Terminology and Optimization Levels

We make "optimization level" a first-class concept:

- `FAST_POINT`: existing PointGet/BatchPointGet and point DML fast path.
- `TRIVIAL_SAFE`: new. Prove "single feasible physical plan" and build it directly.
- `FULL`: existing Volcano physical optimization (stats + cost search).
- `TRIVIAL_AGGRESSIVE`: a SQL Server-like trivial stage (only in `AGGRESSIVE` mode). Use a limited set of rules/heuristics to quickly build a "good-enough" plan and stop; it is not guaranteed to match FULL and must fall back to FULL when ineligible or risky (see below).

Core distinction (first principles):

- `TRIVIAL_SAFE`: because `|P(C)| = 1` (feasible plan set under constraints `C`), search is unnecessary.
- `TRIVIAL_AGGRESSIVE`: when `|P(C)| > 1` but the query is simple enough, accept an "optimization effort tradeoff": end optimization quickly within a limited trivial stage to reduce compile cost materially. The plan is not guaranteed to match FULL, and risk is controlled via strong fallback conditions.

### Architecture: shape semantics first, then pick an optimization level

To avoid scattered `if/else` shortcuts, we explicitly layer planner responsibilities:

1. Context Shaping
- Resolve/apply: statement hints, bindings, `set_var` hints;
- Apply engine/mode constraints: e.g. temporarily removing TiFlash under strict mode;
- Output: a finalized "effective PlannerContext" shared by all optimization levels.
2. Optimization Level Selection
- Order: `FAST_POINT` -> `TRIVIAL_SAFE` -> `TRIVIAL_AGGRESSIVE` -> `FULL`.
3. Plan Construction
- Different solvers per level, but shared semantic checks and post-processing.

This matches SQL Server's placement: after simplification (logicalOptimize), before full optimization.

Target-state pipeline placement (simplified):

```
planner.Optimize
-> Context Shaping (hints/bindings/set_var/engine constraints)
-> Try FAST_POINT (AST-based)
-> Build logical plan
-> logicalOptimize
-> Try TRIVIAL_SAFE (after logicalOptimize, before physicalOptimize)
-> Try TRIVIAL_AGGRESSIVE (after logicalOptimize, before physicalOptimize)
-> physicalOptimize (FULL: RecursiveDeriveStats + FindBestTask)
-> postOptimize
```

### Phase 0: Fast Path Cleanup (clarify responsibilities)

The essence of Phase 0 is clarity, not speed: decouple semantic shaping from solver selection so fast/trivial/full can share one context and common semantic checks.

Phase 0 establishes the structural prerequisites for TRIVIAL_* levels:

1. Context shaping first: fast path must not bypass finalized semantics from bindings/hints
- Current: in `optimizeNoCache`, `TryFastPlan` runs before `MatchSQLBinding`, and fast path carries additional semantic eligibility checks / special-case handling (stable result mode, `sql_select_limit`, privilege checks, lock wait time, etc.).
- Target: fold semantics-affecting logic (statement hints, binding hints, `set_var` hints, engine/isolation constraints, `TryAddExtraLimit`, etc.) into a single "context shaping" phase, then select an optimization level.
2. Centralize semantic eligibility checks: whether FAST/TRIVIAL is allowed should be decided by "optimization level selection"
- For example stable result mode, `SELECT ... INTO`, `sql_select_limit`, fixcontrol, etc. should ideally not be scattered inside the fast-path builder.
3. Consolidate shared validations and cross-cutting behavior across fast/trivial/full
- Common checks such as table mode / table lock / privilege should not be skipped by shortcuts; the entry points for collecting access info and validating should be unified.
- Cross-cutting behaviors such as lock wait time, transaction warm-up, and explain observability should be consistent (or explicitly documented) across all optimization levels.
4. Observability: must explain "why hit / why fallback"
- Record optimization level, hit reason, fallback reason, candidate set sizes, etc. for rollout and debugging.

To reduce refactor risk, Phase 0 should be implemented as a minimum-viable cleanup: first ensure context shaping (bindings/hints/set_var/engine constraints) is finalized before any shortcut is attempted; then centralize shortcut eligibility checks and observability. Avoid forcing FAST_POINT to build a full logical plan just to share validations; during the transition it is acceptable to keep defensive checks inside fast path, as long as the centralized eligibility logic is the source of truth.

(Aligning server-side multi-stmt prefetch with planner's fast path is follow-up work and does not block TRIVIAL_SAFE scaffolding.)

### TRIVIAL_SAFE: a provably-single-solution trivial plan

#### Placement

Insert TRIVIAL_SAFE into Volcano between `logicalOptimize` and `physicalOptimize`:

```
VolcanoOptimize
-> logicalOptimize
-> tryTrivialSafeOptimize (new)
- hit -> physical plan (bounded effort; no FULL search)
- miss -> physicalOptimize (existing FULL)
-> postOptimize
```

This is the key constraint for TRIVIAL_SAFE: it does not bypass logical optimization (rules already applied), but it returns before entering FULL physical optimization, avoiding the full stats/cost search.

#### Eligibility (conservative subset in phase 1)

Phase 1 only supports `SELECT` and enforces hard shape constraints:

- Logical tree: single `DataSource` leaf; only allow unary operators:
- `Selection`
- `Projection`
- Must not contain:
- `Join/Apply/Agg/Window/Union/CTE/Subquery`
- Mode/engine constraints (start conservative):
- not in stable result mode (consistent with the current fast path which hard-disables it)
- TiKV-only (no TiFlash/MPP)

If any condition is not met: fallback to `FULL` with no behavior change.

#### Reusable building blocks: stats-free index pruning in logicalOptimize (already exists)

TiDB already has a stats-free "candidate access path shrinking" step during logical optimization:

- Rule entry: `pkg/planner/core/rule/rule_collect_plan_stats.go:CollectPredicateColumnsPoint`
- It prunes `DataSource.AllPossibleAccessPaths` via `PruneIndexesByWhereAndOrder` (`pkg/planner/core/rule/rule_prune_indexes.go`), based on `ds.InterestingColumns`.
- It is controlled by `tidb_opt_index_prune_threshold` (can be disabled by setting it to `< 0`).

This pruning does not depend on statistics and happens before `physicalOptimize`. Therefore, placing TRIVIAL_SAFE after `logicalOptimize` naturally reuses it and reduces the work needed for both "uniqueness proof" and physical construction.

One clarification: `tidb_opt_index_prune_threshold` is fundamentally a "heuristic effort-reduction" knob (it ranks indexes by interesting-column coverage/order and keeps only up to a threshold), which may prune indexes that FULL would otherwise consider. It is an acceptable, controllable engineering trade-off.

Note that this pruning is already part of today's FULL pipeline (it runs in `logicalOptimize`). Therefore, TRIVIAL_SAFE "reusing it" does not introduce extra semantic drift: FULL will see the same candidate set under the same shaped context and the same variable settings; if users disable the rule, FULL/SAFE will both see the unpruned candidates.

Skyline-R phase 1 (moving the `keepIndex` feasibility prefilter earlier) shares a similar goal (reduce candidates and compile work), but has a different semantic positioning: it is intended to be FULL-equivalent (or a strict subset) feasibility filtering — "drop candidates that FULL would definitely drop anyway". The two are complementary and can be stacked.

#### Candidate shrinking before TRIVIAL_SAFE: semantic layering

TRIVIAL_SAFE must remain FULL-equivalent. Therefore, any pre-SAFE candidate shrinking must be classified by semantics:

| Category | Example | Relation to FULL | SAFE may depend? | Restore needed on fallback? |
| --- | --- | --- | --- | --- |
| Effort pruning (heuristic effort reduction) | `tidb_opt_index_prune_threshold` | already in today's FULL pipeline (may change search effort) | reusable (same behavior) | No |
| Feasibility pruning (feasibility filtering) | skyline `keepIndex` feasibility filter, store/engine feasibility, partial-order feasibility | FULL-equivalent / strict subset (drop only what FULL would definitely drop) | **Yes** | No |
| Aggressive-only shortlist (AGGRESSIVE only) | TRIVIAL_AGGRESSIVE shortlist | not FULL-equivalent (engineering trade-off) | No | **Yes** |

TRIVIAL_SAFE depends only on feasibility pruning (plus constraints such as hints/engine isolation). Any stats-aware or heuristic winner-comparisons must stay in FULL.

#### "Provably trivial": FULL feasible plan set collapses to a single equivalence class

TRIVIAL_SAFE is not about picking a good-looking index; it is about proving that, under the same shaped-context constraint set `C` (bindings/hints/isolation read engines/session switches), FULL optimization would enumerate only a single feasible physical-plan **equivalence class**. In other words, there does not exist another plan that is semantically and property-wise feasible but differs in physical implementation.

With the phase-1 hard shape restrictions (single-table unary chain + TiKV-only, and no `Sort/TopN/Limit` property competition), the remaining physical degrees of freedom largely collapse to the `DataSource` access-path choice and its reader shape (table/index/index lookup; single-scan vs double-read). Therefore, a practical sufficient condition for a TRIVIAL_SAFE hit is:

- the feasible access path set is unique under `C` (including single-scan vs double-read as a key attribute); or
- it becomes unique after a FULL-equivalent rule-only feasibility filtering (Skyline-R phase 1: `keepIndex` prefilter; see below).

When we later extend to property-sensitive operators such as `Sort/TopN/Limit`, the definition must include required-property feasibility and enforcer-induced alternatives; otherwise we must fall back to FULL.

Typical convergence signals (ordered by safety):

1. Semantic single-solution cases (safest)
- primary/unique key full equality constraints (point access)
- contradictory predicates -> `TableDual`
2. Constraint shrinkage
- `FORCE/USE INDEX` leading to only one feasible path
- engine capability or session settings making other paths infeasible
3. Property single-solution cases (future extension)
- `ORDER BY ... LIMIT` can be satisfied by only one path (see Unary Chain extension)

Red lines:

- The proof must be stats-free and explainable.
- If we cannot prove "single equivalence class", we must fall back to FULL.

#### Where TRIVIAL_SAFE hits come from (phase 1 intuition)

From first principles, `TRIVIAL_SAFE` can only hit when **constraints collapse the solution space to 1**; it is not about picking "the best" index. In the phase-1 subset (single-table unary chain, no required-property competition), hits typically come from two sources:

1. **Constraint-driven uniqueness**: context-shaping constraints (especially bindings/hints/engine limits) directly reduce feasible access paths to exactly one.
- Typical example: when `USE/FORCE INDEX` (and related constraints) collapses candidates to a single feasible path (or only the table path), FULL enumeration/search is unnecessary.
2. **Rule-pruning-driven uniqueness**: reuse FULL-semantic (or strict-subset) stats-free rules to prune index paths that are guaranteed to be useless, so the remaining candidates naturally converge to one.
- Typical example: logical-phase `PruneIndexesByWhereAndOrder` plus Skyline-R (moving the FULL `keepIndex` feasibility prefilter earlier, sargable-like), which drops indexes that provably have no leading-column access predicate and have no other "must-keep" reason.

So hints/rules can improve SAFE hit rate, but the essence of SAFE remains "provably single-solution", not "more aggressive index choosing".

#### Skyline-R: split skyline pruning into Rule-only and Cost-aware

Today, skyline pruning happens inside `FindBestTask` (`pkg/planner/core/find_best_task.go`), and `compareCandidates()` mixes rule-based and stats-based dimensions.

To support TRIVIAL_SAFE, we explicitly split skyline into:

1. Skyline-R (Rule-only, new, used by TRIVIAL_SAFE)
- Phase 1 does only one thing: move the FULL skyline **`keepIndex` feasibility filter** earlier (a sargable-like rule), so we can drop index paths that are guaranteed to be unusable and avoid eager ranges/stats work for them.
- Here "sargable-like" means a feasibility check ("can the predicate become an access condition for building index ranges?"), not a proof that an index is globally optimal.
- Importantly, `keepIndex` is a **multi-factor keep/filter check**, and `access predicate` is only one factor: even if an index is not sargable for filtering, it may still be kept by FULL due to forced/covering/order/partial-order reasons. Phase 1 prunes only when all such keep reasons are absent and "no access" is provable.
- This filter already exists in FULL `skylinePruning()` (`pkg/planner/core/find_best_task.go`). Intuitively, an index path is worth considering only if it may help filtering, satisfy required properties, is hint-forced, is a covering single-scan, or can be used for partial-order optimization.
- Phase-1 semantics in one sentence: **prune an index path only when we are sure FULL would never keep it**. Concretely, an *index path* is pruned only when all of the following hold:
- it is not hint-forced (`path.Forced=false`);
- it is not a covering single-scan (`path.IsSingleScan=false`);
- we do not need it for properties: if `!prop.IsSortItemEmpty()` then FULL `keepIndex` is trivially true, so phase 1 does not do keepIndex pruning; if there is a partial-order property (TopN optimization), reuse FULL's partial-order feasibility filtering (indexes that cannot match partial-order are dropped);
- and we can *prove* the leading index column has no access predicate (see `AccessSummary` below).

Pseudocode (readability-first):

```text
keep(tablePath) = true
keep(indexPath) =
path.Forced || path.IsSingleScan || !prop.IsSortItemEmpty() || matchPartialOrderIndex ||
AccessSummary.HasLeadingAccessPredicate(path)
```
- To make the "prove no access predicate" check feasible without building full ranges, introduce an `AccessSummary` (stats-free):
- Apply the same expression normalization as FULL (at least `expression.EliminateNoPrecisionLossCast`, consistent with `deriveStats4DataSource()`), to avoid false negatives caused by expression shapes.
- `AccessSummary` uses three values: `YES/NO/UNKNOWN`. We prune only on `NO`; otherwise keep the path for FULL (allow false positives, disallow false negatives). Phase 1 treats top-level OR/DNF as `UNKNOWN` by default.
- Implementation sketch: reuse `pkg/util/ranger`'s `conditionChecker` semantics to do a lightweight "leading column has any access predicate" check; do not build full ranges.
2. Skyline-C (Cost-aware, kept in FULL)
- Remains in `FindBestTask` and continues to use stats-based comparisons to preserve FULL plan quality.

Implementation notes:

- Skyline-R phase 1 does not attempt skyline dominance / `compareCandidates`. Stats-aware comparisons (risk ratio, pseudo, `CountAfterAccess`, empirical thresholds) remain entirely in FULL (Skyline-C).
- Correctness invariant (say it explicitly): `keepIndexEarly(path)=false => keepIndexFULL(path)=false`. Practically, `AccessSummary` may allow false positives (keep extra), but must not allow false negatives (prune incorrectly). If in doubt, keep the path and let FULL decide.
- Fallback isolation and reuse: since this prefilter is FULL-equivalent (or a strict subset), its pruning can be committed and reused by FULL; only `TRIVIAL_AGGRESSIVE` temporary shortlists (not done by FULL) must be restored on fallback.

Essence: push provable pruning earlier; keep uncertain comparisons in FULL.

#### Other front-move boundaries (phase 1)

Phase 1 front-moves only skyline feasibility prefiltering (Skyline-R) and keeps all cost/stats winner comparisons in FULL. `derivePathStatsAndTryHeuristics` depends on artifacts produced by `fillIndexPath()` (ranges/access filters) and is stats-aware, so we do not front-move it in phase 1; we only reduce its total work indirectly via earlier candidate shrinking. Future reuse must be strictly budgeted and uncertainty must fall back to FULL.

#### Plan construction on TRIVIAL_SAFE hit

On hit, two principles:

1. Do necessary work only for the unique candidate path (range building, index choice), avoiding N-times repeated construction.
2. Reuse existing physical post-processing (`postOptimize`) and required semantic checks to keep behavior consistent.

Engineering approach (prioritize maintainability):

- Recommendation: reuse Volcano physical construction, but restrict the candidate set to one path so `RecursiveDeriveStats/FindBestTask` degenerates to constant work.

Must fall back to FULL when encountering unsupported shapes or corner cases.

#### Unary Chain extension: include Sort/TopN/Limit (later)

We can extend to:

```
DataSource -> Selection/Projection -> Sort/TopN/Limit
```

but only under provable conditions:

- `ORDER BY` expressions are deterministic (no non-deterministic functions like `rand()`)
- the required ordering can be satisfied by the unique access path (full match or provable prefix match)
- `LIMIT/OFFSET` are compile-time constants

Otherwise, fallback to FULL.

### TRIVIAL_AGGRESSIVE: exploratory trivial stage (limited rules/heuristics)

`TRIVIAL_AGGRESSIVE` is enabled only when `tidb_opt_trivial_plan=AGGRESSIVE`. This document's primary deliverable is `TRIVIAL_SAFE`; `TRIVIAL_AGGRESSIVE` is intentionally positioned as an exploratory follow-up: we first establish the framework, boundaries, and observability, then expand rules iteratively when we have enough low-cost signals to justify it.

Why SQL Server can do more in a trivial stage (high level): it can afford lightweight index analysis (coarse selectivity/pages) cheaply. In TiDB, access selectivity estimation typically goes through building index ranges and deriving stats per candidate, which is the main compilation cost we try to avoid. Therefore phase 1 does not attempt a broad stats-based index comparison inside the trivial stage.

#### Phase-1 positioning (conservative)

- Handle only obvious cases; if evidence is weak or ambiguous, fall back to FULL.
- Do not introduce heavyweight computation inside the trivial stage (ranges/stats/cost for many candidates). If it needs that work to decide, it belongs to FULL.
- Fallback isolation: temporary shrinking must not leak into FULL on fallback.

Supported shapes (phase 1):

Phase 1 attempts TRIVIAL_AGGRESSIVE only for single-table unary chains (`DataSource -> Selection/Projection`) and the following cases:

- Unique-1Row: provably at most 1 row via unique/PK equality access (with or without `ORDER BY`/`LIMIT`; they do not change the 1-row bound);
- Covering-dominance: no `ORDER BY`/`LIMIT`, and there exists a provably-dominant covering index access (see below);
- LIMIT-based: queries with `LIMIT/OFFSET` (row-goal plus a conservative limit-only subset; see below).

Otherwise, fall back to FULL (TRIVIAL_SAFE may still hit earlier).

#### Phase-1 strategy: shrink via structural signals only (uncertainty => FULL)

Input: `P0` candidates after context shaping + logical pruning + Skyline-R (`keepIndex`) feasibility prefilter.

First principles for entering TRIVIAL_AGGRESSIVE: only stop in the trivial stage when we can reach a **structural dominance conclusion** without statistics (hard bound / early stop / avoiding a heavyweight operator) and shrink candidates to a tiny set; otherwise defer to FULL.

Red lines (phase 1):

- do not compute ranges/stats/cost for many candidates;
- insufficient evidence, ambiguity, or budget exceeded: fall back to FULL;
- internal budgets (TBD): caps on candidate count / shortlist size / exact evals; exceeding any cap falls back to FULL;
- for LIMIT-based scenarios, `k = LIMIT + OFFSET` too large means the query is no longer "trivial" under row-goal/limit-only rules: fall back to FULL (threshold TBD; can start by aligning with `tidb_opt_limit_push_down_threshold` or using a more conservative internal cap).

Why this is a separate (AGGRESSIVE-only) step, not covered by Skyline-R / logical pruning:

- Skyline-R is FULL-equivalent feasibility filtering; with `ORDER BY`, a non-order-matching path can still satisfy required ordering via a `Sort` enforcer, so it cannot be pruned there.
- Logical index pruning (e.g. `tidb_opt_index_prune_threshold`) is a generic effort-control knob; it is not designed for row-goal/limit-only early stop and does not guarantee a tiny candidate set.

Case U: Unique-1Row (hard upper bound 1)

- Eligibility checks (cheap and decidable):
- there exists a PK or unique index with globally-unique semantics (for partitioned tables, only when global uniqueness can be proven; otherwise fall back to FULL);
- its key columns are all covered by single-value equality predicates (e.g. `col = const`);
- exclude NULL corner cases: for nullable unique-key columns, require the equality values are provably non-NULL (e.g. compile-time non-NULL constants), or the columns are declared NOT NULL (so NULL cannot match). PK columns are inherently NOT NULL, no extra proof needed;
- predicates are simple conjunctions (no OR/DNF) and do not contain subqueries.
- Plan selection:
- pick the corresponding unique/PK access path directly; since the result has at most 1 row, the presence of `ORDER BY`/`LIMIT` does not change the bound and we avoid ambiguous trade-offs (sort/double-read) in the trivial stage.
- if multiple unique/PK paths qualify, use a deterministic priority (e.g. clustered PK > covering unique > unique), otherwise fall back to FULL.

Examples (simplified):

- `WHERE pk=1` or `WHERE uk1=1 AND uk2=2`: at most 1 row, pick the corresponding PK/unique access.

Case P: Covering-dominance (provably-dominant covering index access; no `ORDER BY`/`LIMIT`)

- Eligibility checks (cheap and decidable):
- no `ORDER BY`, no `LIMIT/OFFSET`;
- predicates are simple conjunctions (no OR/DNF) and do not contain subqueries;
- `AccessProfile` construction considers only the range-buildable predicate subset (equalities/ranges); other predicates are allowed but are ignored when building `AccessProfile`.
- candidates must be covering single-read and have no residual filter (no back-to-table filtering). If no such candidate exists, fall back to FULL.
- Rule (dominance-only):
- build a lightweight `AccessProfile` (from equality/range predicates only) for each covering index path: the maximal index-range prefix it can use (equality prefix + optional 1 range column). This is similar in spirit to `AccessSummary`: extend "leading column has access predicate" into "maximal access-prefix length/set", and keep a `YES/NO/UNKNOWN` discipline to avoid mis-pruning; if any key column becomes `UNKNOWN`, treat it as "cannot prove" and fall back to FULL.
- Strict definition (provable dominance):
- Let `AccessPredCols(path) = EqPrefixCols(path) ∪ {RangeCol(path)}` (omit the range column if absent).
- For two paths with known `AccessProfile`, `A` dominates `B` iff `AccessPredCols(B) ⊆ AccessPredCols(A)` (so `A`'s scanned key range is a subset of `B`'s).
- Only when there exists a path that dominates all other eligible covering paths do we choose it; otherwise fall back to FULL.

Example (simplified):

- `WHERE a=1 AND b=2 AND c>10`: index `(a,b,c)` dominates `(a,b)` on access prefix; if both are covering, choose `(a,b,c)`.

Case A/B: row-goal (`ORDER BY ... LIMIT`)

- Eligibility checks (cheap and decidable):
- `LIMIT/OFFSET` are compile-time constants and `k` is small enough (threshold TBD);
- predicates are simple conjunctions (no OR/DNF) and do not contain subqueries;
- `IN` list sizes are bounded (threshold TBD).
- Rule A (equality-prefix order-matching):
- allow order-matching under a sub-range where some leading index columns are fixed by single-value equality (`col = const`).
- Rule B (prefer covering single-read):
- if there exist order-matching covering single-read candidates: keep only them in the shortlist;
- if order-matching is only possible via double-read (IndexLookup): fall back to FULL (avoid ambiguous double-read comparisons in the trivial stage).
- Winner decision:
- if shortlist has exactly one candidate: return it;
- if shortlist is small enough: reuse FULL ranger/stats/cost only on the shortlist (bounded exact eval) and return the winner;
- otherwise: fall back to FULL.

Examples (simplified):

- A: `WHERE c=1 ORDER BY a LIMIT 10`, if there is an index `(c,a)`, the scan within `c=1` can be ordered by `a` and early-stop.
- B: `SELECT b FROM t WHERE a>=1 ORDER BY a LIMIT 10`, if there is an index `(a,b)`, it can be covering and early-stop.

Case C/D: limit-only (`LIMIT`, no `ORDER BY`)

- Eligibility: no `ORDER BY`; `LIMIT/OFFSET` are compile-time constants and `k` is small enough (threshold TBD).
- Rule C (no WHERE): if there is a covering single-read candidate, deterministically pick a "narrower" index key (fewer key columns); otherwise fall back to FULL.
- Example: `SELECT a FROM t LIMIT 10`, if there is an index `(a)`, scan the index for `k` rows.
- Rule D (with WHERE): attempt only when we can **prove** it is covering and has no residual filter (all predicates are evaluated on the index side; no back-to-table); otherwise fall back to FULL.
- Example: `SELECT a,b FROM t WHERE a=1 LIMIT 10`, if there is an index `(a,b)` and the predicate can be fully applied on the index side, scan the index for `k` rows.

#### TODO: lightweight index analysis (future work)

Treat `TRIVIAL_AGGRESSIVE` as the host framework for future lightweight index analysis (mid/long term). If we can make "estimate selectivity/bytes read" cheap without fully building ranges for every candidate, `TRIVIAL_AGGRESSIVE` can expand to more base-scenario range predicates in a SQL Server-like way. This is explicitly future work and must remain budgeted and conservative (uncertainty triggers fallback to FULL).

### Configuration, rollout, and observability

#### User-facing switch

- New session/global variable: `tidb_opt_trivial_plan`
- Default: `OFF` (explicit enable for gradual rollout)
- Values:
- `OFF`: disable the trivial framework (behavior unchanged from today).
- `SAFE`: enable `TRIVIAL_SAFE` only (provably single-solution; FULL-equivalence is a hard constraint).
- `AGGRESSIVE`: enable `TRIVIAL_SAFE` + `TRIVIAL_AGGRESSIVE` (trivial-aggressive stage; not guaranteed to match FULL; fall back to FULL when ineligible or risky).

#### StmtCtx / EXPLAIN observability

We must answer:

1. Which optimization level was used, and why?
2. If TRIVIAL_SAFE / TRIVIAL_AGGRESSIVE missed, which hard condition failed?

Suggested fields (in `StmtCtx` or via explain notes):

- `OptimizerOptLevel`: `FAST_POINT | TRIVIAL_SAFE | TRIVIAL_AGGRESSIVE | FULL`
- `TrivialReason` / `FallbackReason`: structured reason (or at least readable strings)
- `Evidence`: candidate counts (before/after shrinking), unique path identifier (SAFE), shortlist rationale (AGGRESSIVE), etc.

### Compatibility and edge cases

Correctness and compatibility must be prioritized:

- Bindings/Hints: optimization level selection must happen after context shaping; bindings must not be bypassed by fast/trivial.
- Stable result mode: conservatively disabled in phase 1; later support requires equivalence proofs.
- Engine/Store: phase 1 is TiKV-only; TiFlash/MPP needs additional proofs over task types and plan alternatives.
- Consistent semantic checks: table mode / lock / privilege checks must be unified or their differences must be explicit.
- Fallback isolation: TRIVIAL_* must not change FULL's candidate plan space on miss/fallback (e.g. access-path shrinking must be reversible).
- Plan cache: include `tidb_opt_trivial_plan` in the plan-cache key (other interaction details are left to implementation).

### Suggested phases

Incremental rollout to reduce risk:

1. S0 (Phase 0): fast path cleanup (context shaping + level selection + common checks + observability)
2. S1: introduce optimization level enum + TRIVIAL_SAFE scaffolding (always miss), add UT to ensure no behavior change
3. S2: implement TRIVIAL_SAFE phase 1 (single-table + Selection/Projection + unique path), add Skyline-R
4. S3: (optional) implement TRIVIAL_AGGRESSIVE initial version (`AGGRESSIVE`: Unique-1Row + Covering-dominance + LIMIT-based scenarios (row-goal + a conservative limit-only subset), shortlist shrinking + bounded exact eval (reusing FULL ranger/stats/cost) + strong fallback; not guaranteed to match FULL)
5. S4: gradually expand coverage and rules (e.g. more complete row-goal `ORDER BY ... LIMIT/TopN`, more single-table operators and DML), continuously evaluating benefits/risks with observability and benchmarks

### Code references (current entry points)

- Planner entry & fast path trigger: `pkg/planner/optimize.go:optimizeNoCache`
- Fast path builder: `pkg/planner/core/point_get_plan.go:TryFastPlan`
- Volcano pipeline: `pkg/planner/core/optimizer.go:VolcanoOptimize`
- FULL physical hotspot: `pkg/planner/core/optimizer.go:physicalOptimize`
- Skyline pruning (FULL): `pkg/planner/core/find_best_task.go`
- Multi-stmt prefetch fast path: `pkg/server/conn.go:prefetchPointPlanKeys`

## Test Design

### Functional Tests (unit tests)

- Eligibility boundaries: each hard condition should have coverage.
- Plan equivalence vs FULL on the "provably single-solution" set:
- access path type (table/index/index lookup) is identical;
- key operator shape remains consistent (e.g. no extra Sort/TopN differences).
- TRIVIAL_AGGRESSIVE (`AGGRESSIVE`) semantic correctness:
- results must be identical;
- plan drift vs FULL is allowed but must be observable and explainable via explain/logs (for rollout comparisons and rollback decisions).
- Fallback stability: unsupported cases must fall back to FULL with unchanged behavior.

### Scenario Tests (integrationtest)

- Single-table with many indexes + simple predicates: verify compile time reduction and identical results; include Unique-1Row / covering-dominance / row-goal / limit-only cases; under `AGGRESSIVE`, compare execution-cost / plan-shape distributions against FULL to avoid obvious regressions.
- Various hints/bindings combinations: verify bindings priority is not bypassed.
- Transaction/locking related statements: validate no behavior changes (TRIVIAL_SAFE can conservatively fall back in phase 1).

### Compatibility Tests

- Interactions with plan cache, bindings, restricted SQL, partition pruning, strict mode (temporary TiFlash removal).
- Upgrade/downgrade: new variables and explain outputs should not break existing clients.

### Benchmark Tests

Measure both:

- compile latency (P50/P95) and CPU, especially for single-table, many-index, short SQL patterns;
- impact on online workloads: reduced compilation contention and latency jitter.
- hit rate and fallback reason distribution, to guide safe coverage expansion.

## Impacts & Risks

Expected impacts:

- For provably single-solution queries, significantly reduce `physicalOptimize` CPU/latency, improving throughput and reducing compilation jitter under concurrency.
- `TRIVIAL_SAFE` may have a limited hit rate, but it provides a low-risk framework and observability foundation for safely expanding `AGGRESSIVE` coverage later.
- For simple Unique-1Row / covering-dominance / row-goal / limit-only queries, `AGGRESSIVE` can further reduce compilation cost and concurrency jitters, at the expense of potential plan drift, which is controlled via switch/fallback/observability.
- With a first-class optimization level concept, boundaries between fast/trivial/full become clearer, improving maintainability.

Main risks and mitigations:

- Plan-quality regressions under `AGGRESSIVE` (performance variance caused by plan drift)
- Mitigation: strict eligibility + strong fallback; gradual rollout; record rationale in explain/logs and allow quick rollback to `SAFE/OFF`.
- Skyline-R diverging from existing skyline behavior
- Mitigation: explicit split (Skyline-R stats-free vs Skyline-C cost-aware) with clear responsibilities.
- Lack of observability hurts debugging and rollout
- Mitigation: hit/fallback reasons must be visible at statement level.

## Unresolved Questions

- How should phase-1 rules evolve to balance compile gain, explainability, and fallback stability?
- Do we need explicit quality guardrails in `AGGRESSIVE` mode (auto fallback / denylist / degrade-on-unreliable-stats), and how should thresholds be defined?
- How should plan cache interaction details (beyond including `tidb_opt_trivial_plan` in the cache key) be finalized?
- What is the final external shape of observability fields (hit reason, shortlist evidence, fallback reason), and how do we keep compatibility?

Start with pkg/planner/optimize.go, pkg/planner/core/point_get_plan.go, and pkg/planner/core/optimizer.go to trace the current FAST_POINT and FULL planning paths. Read the index-pruning rules in pkg/planner/core/rule/rule_collect_plan_stats.go and rule_prune_indexes.go, then use the Phase 0 and TRIVIAL_SAFE eligibility requirements as the completion criteria, including safe fallback to FULL and hit/fallback observability.

Written by the indexing model from the issue text.