EXECPLAN

Inspects a SQL query without executing it. Each EXECPLAN mode returns a single focused, CLI-friendly slice of plan information; the syntax mirrors the GUI Query Explorer's two-axis structure (plan type x view mode) plus orthogonal selectors for skip statistics and detected datasources.

Category: systemDeltaForge extension

Syntax

EXECPLAN [PHYSICAL [RAW|DEBUG] | LOGICAL [RAW|DEBUG] | DATASOURCES] <sql_statement>

Description

## Overview EXECPLAN inspects a SQL query without executing it. Each mode returns one focused slice of plan information so callers (CLI, ODBC, HTTP, GUI) ask for exactly what they need rather than wading through a kitchen-sink dump. The grammar mirrors the GUI Query Explorer's plan panel: - The Query Explorer exposes two orthogonal toggles, **plan type** (`Physical` / `Logical`) and **view mode** (`Visual` / `Raw` / `Debug`). EXECPLAN's `PHYSICAL [RAW|DEBUG]` and `LOGICAL [RAW|DEBUG]` modes correspond directly to those four panels (Visual is purely a frontend tree rendering of Raw, so the SQL surface only needs Raw and Debug). - The Query Explorer also surfaces a detected-datasource sidebar; `EXECPLAN DATASOURCES` returns the same data. - Skip statistics are unique to EXECPLAN and are returned by the bare `EXECPLAN <sql>` form. ## Result Schemas (CLI-Friendly) Each mode chooses the result shape that's most natural for its content. The plan-string modes deliberately emit **one row per line of plan text** so a CLI or ODBC grid renders the plan tree naturally, instead of stuffing every line into a single multi-line cell that gets wrapped or truncated. | Mode | Schema | Rows | |---|---|---| | `EXECPLAN <sql>` | `(section: utf8, key: utf8, value: utf8)` | one per stat (Total Files, Files Skipped, Skip Rate, ...) | | `EXECPLAN PHYSICAL [RAW] <sql>` | `(plan: utf8)` | one per line of the readable physical plan + optimizer-stats appendix | | `EXECPLAN PHYSICAL DEBUG <sql>` | `(plan: utf8)` | one per line of the `{:#?}` physical plan dump | | `EXECPLAN LOGICAL [RAW] <sql>` | `(plan: utf8)` | one per line of the `display_indent_schema` logical plan | | `EXECPLAN LOGICAL DEBUG <sql>` | `(plan: utf8)` | one per line of the `{:#?}` logical plan dump | | `EXECPLAN DATASOURCES <sql>` | `(datasource: utf8)` | one per detected ExecutionPlan node label (Delta, Parquet, ...) | A CLI consumer can therefore pipe any plan-string mode straight to `cat` / file output and get the plan as plain text without having to extract a single huge cell. ODBC clients drain the result with `SQLFetch` + `SQLGetData` over a single column without dealing with multi-line UTF8 cell handling. ## Mode Reference ### `EXECPLAN <sql>` (default: skip-stats) Returns the data-skipping summary the engine would apply if the query ran. Sections (in `section` column): - **File Statistics**: Total Files, Files to Scan, Files Skipped, Skip Rate, Columns Projected. - **Skip Breakdown** (only non-zero entries): per-decision counts for min/max statistics, partition pruning, bloom filters, IN-list bloom filters, and bloom-only filters. - **Bloom Filters** (when applicable): Files Skipped by Bloom (totalled across bloom mechanisms). - **Summary**: Files Kept. No plan strings are returned. Use the dedicated PHYSICAL or LOGICAL modes for those. ### `EXECPLAN PHYSICAL [RAW] <sql>` One row per line of `displayable(plan).indent(true).to_string()` followed by the `== Optimizer Statistics ==` appendix when the cost-based optimizer recorded any activity. Joining the column with `\n` reconstructs exactly what the GUI Query Explorer renders in the Physical / Raw tab. `RAW` is the implicit default for `PHYSICAL`; you can omit it. ### `EXECPLAN PHYSICAL DEBUG <sql>` One row per line of `format!("{:#?}", plan)` for the `Arc<dyn ExecutionPlan>`. Per-operator state including partitioning, output ordering, child wiring, and operator-specific metadata. Identical reconstruction to the GUI Physical / Debug tab. ### `EXECPLAN LOGICAL [RAW] <sql>` One row per line of `df.logical_plan().display_indent_schema().to_string()`. Identical reconstruction to the GUI Logical / Raw tab. `RAW` is the implicit default for `LOGICAL`. ### `EXECPLAN LOGICAL DEBUG <sql>` One row per line of `format!("{:#?}", df.logical_plan())`. Every internal field of the DataFusion `LogicalPlan` (projections, predicates, expressions, table-scan provenance). Identical reconstruction to the GUI Logical / Debug tab. ### `EXECPLAN DATASOURCES <sql>` One row per detected datasource ExecutionPlan node label, ordered alphabetically. Empty result set when no recognised connector is present. The detection set is shared with the GUI Query Explorer (via `delta_forge_sql::unified_executor::command::extract_datasources`) and covers Delta, file-format readers (CSV, Parquet, JSON, Avro, ORC, Excel, XML, HL7, EDI, FHIR, Iceberg), the database connector, the pseudonymisation operator, materialised Cypher subqueries, and the graph-algorithm operators (PageRank, Betweenness, Closeness, Degree, Louvain, Components, SCC, Triangle Count, Shortest Path, BFS, DFS, MST, KNN, Similarity). ## Implementation Notes - Plan-string modes delegate to `UnifiedSqlExecutor::get_query_plans`, the same in-process method `Session::get_execution_plan` uses to populate the GUI's `ExecutionPlan` payload. EXECPLAN therefore produces byte-identical strings to the GUI Query Explorer (multi-statement `-- Statement N --` headers, `VERSION AS OF` time-travel rewrite, Cypher plan-text branch); the only difference is that EXECPLAN splits the string on `\n` before returning so the result is grid-friendly. - The skip-stats mode runs its own `df.create_physical_plan().await` to feed `ExecutionStats::from_physical_plan`, since the stats path needs the live `Arc<dyn ExecutionPlan>` rather than a formatted string. - EXECPLAN runs entirely inside the in-process executor and does **not** require the control plane. It works in any environment that has a `UnifiedSqlExecutor`, including the embedded CLI, the ODBC driver, integration tests, and standalone debugging harnesses. Tables referenced by the inner SQL must be registered in the active `SessionContext` (typically via `CREATE EXTERNAL TABLE`, `OPEN DELTA TABLE`, or `CREATE GRAPH`). ## Compatibility EXECPLAN is a DeltaForge extension that replaces the standard SQL EXPLAIN command for Delta tables. While EXPLAIN shows the logical plan, EXECPLAN creates the physical plan to trigger file filtering and provides Delta-specific metrics (bloom filter usage, data-skipping rates). For non-Delta tables registered through DataFusion, use the standard EXPLAIN statement instead.

Parameters

Name	Type	Description
`mode`		Optional mode prefix selecting which slice of plan information to return. Without a mode, EXECPLAN returns the data-skipping summary. PHYSICAL [RAW] / PHYSICAL DEBUG return the readable / Debug-format physical plan. LOGICAL [RAW] / LOGICAL DEBUG return the readable / Debug-format logical plan. DATASOURCES returns one row per detected ExecutionPlan node label.
`statement`		SQL statement to explain.

Examples

-- Default: data-skipping summary for a point lookup
EXECPLAN SELECT * FROM warehouse.sales.orders WHERE id = 42;

-- Readable physical plan, one row per line
EXECPLAN PHYSICAL RAW SELECT * FROM warehouse.sales.orders WHERE id = 42;

-- Full {:#?} dump of physical plan, one row per line
EXECPLAN PHYSICAL DEBUG SELECT * FROM warehouse.sales.orders WHERE id = 42;

-- Schema-annotated logical plan, one row per line
EXECPLAN LOGICAL RAW SELECT * FROM warehouse.sales.orders WHERE id = 42;

-- Full {:#?} dump of logical plan, one row per line
EXECPLAN LOGICAL DEBUG SELECT * FROM warehouse.sales.orders WHERE id = 42;

-- One row per detected datasource (Delta, Parquet, Database, ...)
EXECPLAN DATASOURCES
SELECT d.*, c.region
FROM warehouse.sales.orders d
JOIN warehouse.refdata.regions_csv c ON d.region_id = c.id;

Pitfalls

EXECPLAN creates the physical plan but does not execute it. The file counts reflect the plan's pruning decisions at planning time, not actual I/O. Runtime adaptive optimizations may further reduce the files scanned during real execution.
The statement following EXECPLAN (or a mode prefix such as `PHYSICAL DEBUG`) must be a complete SQL statement. Do not include a trailing semicolon within the EXECPLAN argument; the semicolon terminates the EXECPLAN command itself.
EXECPLAN routes exclusively through the Delta executor. For tables registered only in DataFusion (e.g., CSV, Parquet external tables), use EXPLAIN SELECT instead.
Plan-string modes use `str::lines()` to split, which strips terminating newlines and is `\r\n` aware. Joining the column with `\n` (LF) reconstructs the canonical plan text; the engine never re-emits `\r\n`.
The optimizer-statistics appendix on `PHYSICAL RAW` is process-global and reflects the most recent planner activity, not a query-isolated snapshot. Numbers in the `== Optimizer Statistics ==` block are accurate for the current EXECPLAN call only when no other queries planned concurrently in the same process.
The optional `RAW` keyword is a no-op (it is the default) and exists only for symmetry with the GUI's view-mode toggle. `EXECPLAN PHYSICAL <sql>` and `EXECPLAN PHYSICAL RAW <sql>` produce identical results.