SQL planning and statistics

Baseline: v4.2.5. SQL text, logical plans, physical Scope/Pipeline and execution statistics describe different phases.

SQL phases

protocol request → AST → bind database/table/column/type/context
    → QueryBuilder and rules/statistics → logical plan
    → Compile → Scope/Pipeline/operators → execute/encode result

Start with Frontend dispatch/compiler context, then pkg/sql/plan/build.go and query_builder.go, then pkg/sql/compile/compile.go, scope.go and the operators. Prepared statements, internal executors and retries have their own context/lifecycle beyond one string parse.

Statistics and choices

Statistics estimate rows, selectivity, NDV, join sizes and scan costs. DistTAE updates involve Logtail, object metadata and background work; memory increments and persisted objects are different inputs. New/rapidly changing tables, skew and sampling affect estimates.

Inspect filtering, column pruning, join order, indexes, Runtime Filters and Shuffle in the actual plan. table_stats has get/refresh and testing patch paths; patch is not a production data repair method. Check code/configuration for thresholds and cadence.

Invariant: inaccurate estimates may affect efficiency but not SQL results; degrading an optional Runtime Filter must not discard valid rows.

EXPLAIN and execution

EXPLAIN inspects plans; EXPLAIN ANALYZE executes, so check side effects. Compare scans, predicates, join direction, estimates, actual data and remote operators rather than an index name alone.

Diagnose correctness/grain, scan/filtering, estimates, join distribution, memory/spill and cache/I/O in that order. Retain SQL, DDL, scale, parameters and build with every plan comparison.

Hints and limitations

Table suffixes USE INDEX, FORCE INDEX and IGNORE INDEX use the index-hint implementation. Accepting Oracle-style comments /*+ INDEX(...) */ or ORDERED does not prove an effect. Record missing indexes, conflicting hints, insufficient coverage and unsuitable plans rather than promising arbitrary forced plans.

See Hints and EXPLAIN.

Source and regression entry points