Feature: EXPLAIN / EXPLAIN ANALYZE + Plan Visualizer (with tracing & lineage hooks)

[hoangsonww]

Summary

Add first-class query introspection: EXPLAIN and EXPLAIN ANALYZE with a rich plan graph (logical + physical), operator timings, row counts, memory/IO, and vector/temporal specifics—surfaced in CLI and the GUI with an interactive visualizer. Tie results to the existing observability work (tracing, metrics, lineage/audit).

---

Motivation

• Performance tuning: Developers need visibility into join order, index use, vector prefilters, and temporal pruning.
• Predictability: Show why the optimizer chose a plan; surface cardinality estimates vs. actuals.
• AI/Vector use cases: Make ANN operator parameters and post-filter selectivity visible.
• Temporal & lineage debugging: Reveal how FOR SYSTEM_TIME and validity pruning impact scans.
• Ops & SRE: Correlate a slow query’s plan with traces, logs, and resource usage.

---

Proposed Design

1) SQL / CLI

-- Logical plan only
EXPLAIN SELECT ...;

-- Physical plan, costs/estimates
EXPLAIN (VERBOSE, COSTS, SETTINGS) SELECT ...;

-- Execute and collect runtime metrics
EXPLAIN ANALYZE SELECT ...;

-- Focused operator output
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ...;

CLI supports --format=TEXT|JSON|DOT and --save plan.json.

2) Output Schema (JSON)

• plan_id, query_id, timestamp

• optimizer block: chosen join order, considered alternatives (top-N), reason codes

• Per-node:

  • op: SeqScan, IndexScan[B+Tree], ANN[HNSW|IVF], TemporalPrune, HashJoin, Agg, Sort, Materialize
  • est_rows, est_cost, act_rows, time_ms, mem_bytes, io_pages, spill_bytes
  • predicates (pushdowns), vector params (ef_search, M, nprobe), temporal windows

• policies: RLS/CLS, DP settings applied (names only; no sensitive data)

• storage: index used, compression, page locality hints

• tracing: trace_id, span_ids for correlation

• lineage: input tables/snapshots/branches (if any)

3) GUI Visualizer

• Graph view with tooltips for each node (estimates vs. actuals, % of total time).
• Heatmap for critical path; badges for misestimates.
• Tabs: Logical | Physical | Runtime | Settings.
• Vector & Temporal overlays: show ANN parameters, pre/post-filter selectivity; show time-window pruning.
• One-click links to traces/logs via trace_id.
• Export buttons: JSON / DOT (Graphviz) / PNG.

4) Engine/Planner Hooks

• Instrument operators with lightweight timers and counters; optional page/IO counters gated by EXPLAIN ANALYZE.
• Expose optimizer decision traces (reason codes: index-vs-seq, join algo, vector prefilter).
• For vector ops, record candidate set size before/after prefilter and final recall sample (optional).
• Temporal pruning node emits pruned interval stats.
• Sampling mode for very large scans to bound overhead.

5) Safety

• Respect RLS/CLS: introspection reveals structure, not protected data.
• Redact literals behind a flag: EXPLAIN (..., REDACT_LITERALS).

---

Milestones

1. MVP

• EXPLAIN (logical + physical trees), TEXT/JSON output.
• Basic operator timers for EXPLAIN ANALYZE.
• GUI plan table (tree list) with timings.

2. Vector & Temporal

• ANN operator metrics (params, candidate sizes).
• Temporal prune stats and validity windows visual.

3. Observability Integration

• trace_id correlation, linkouts in GUI.
• Misestimate detection (thresholded warnings).

4. UX Polish

• Graph view, heatmap, export formats, saved plan history per query.

---

Acceptance Criteria

• EXPLAIN ANALYZE overhead ≤10% on OLTP queries (median), bounded via sampling.
• JSON schema stable and documented; round-trippable via CLI --format JSON.
• GUI highlights top-3 time-consuming nodes and any misestimates ≥3×.
• Vector queries show ANN params and pre/post selectivity; temporal queries show pruned ranges.
• Trace correlation works end-to-end (plan → trace view) when tracing is enabled.

---

Checklist

• Planner/Executor instrumentation
• JSON schema + docs
• CLI flags and formatting
• GUI table view (MVP)
• Vector/temporal overlays
• Trace correlation
• Heatmap graph + exports
• Tests & benchmarks (overhead, correctness)

[hoangsonww]

todo