KnowForgeSQL 参考
EXPLAIN
师成· 更新于 2026-09-28· 阅读 21 分钟· 0 次阅读登录后可跨设备保存划线和私人笔记登录
EXPLAIN
EXPLAIN 命令用于显示指定 SQL 语句的逻辑执行计划和物理执行计划。
语法
EXPLAIN [ANALYZE] [VERBOSE] [FORMAT format] statementEXPLAIN
显示语句的执行计划。若需要更详细的输出，请使用 EXPLAIN VERBOSE。注意 EXPLAIN VERBOSE 仅支持 indent 格式。
可选的 [FORMAT format] 子句控制计划的显示方式，说明如下。如果未指定该子句，则计划将按照配置项 datafusion.explain.format 所指定的格式显示。
tree 格式（默认）
tree 格式以 DuckDB 计划为蓝本，旨在让人更容易看清计划的高层结构。
> EXPLAIN FORMAT TREE SELECT SUM(x) FROM t GROUP BY b;
+---------------+-------------------------------+
| plan_type | plan |
+---------------+-------------------------------+
| physical_plan | ┌───────────────────────────┐ |
| | │ ProjectionExec │ |
| | │ -------------------- │ |
| | │ sum(t.x): sum(t.x)@1 │ |
| | └─────────────┬─────────────┘ |
| | ┌─────────────┴─────────────┐ |
| | │ AggregateExec │ |
| | │ -------------------- │ |
| | │ aggr: sum(t.x) │ |
| | │ group_by: b@0 as b │ |
| | │ │ |
| | │ mode: │ |
| | │ FinalPartitioned │ |
| | └─────────────┬─────────────┘ |
| | ┌─────────────┴─────────────┐ |
| | │ CoalesceBatchesExec │ |
| | └─────────────┬─────────────┘ |
| | ┌─────────────┴─────────────┐ |
| | │ RepartitionExec │ |
| | │ -------------------- │ |
| | │ input_partition_count: │ |
| | │ 1 │ |
| | │ │ |
| | │ partitioning_scheme: │ |
| | │ Hash([b@0], 16) │ |
| | └─────────────┬─────────────┘ |
| | ┌─────────────┴─────────────┐ |
| | │ AggregateExec │ |
| | │ -------------------- │ |
| | │ aggr: sum(t.x) │ |
| | │ group_by: b@1 as b │ |
| | │ mode: Partial │ |
| | └─────────────┬─────────────┘ |
| | ┌─────────────┴─────────────┐ |
| | │ DataSourceExec │ |
| | │ -------------------- │ |
| | │ bytes: 224 │ |
| | │ format: memory │ |
| | │ rows: 1 │ |
| | └───────────────────────────┘ |
| | |
+---------------+-------------------------------+
1 row(s) fetched.
Elapsed 0.016 seconds.indent 格式
indent 格式会同时显示逻辑计划和物理计划，计划中的每个算子占一行。子计划会进行缩进，以体现层级关系。
有关如何解读这些计划的更多信息，请参阅阅读 EXPLAIN 计划。
> CREATE TABLE t(x int, b int) AS VALUES (1, 2), (2, 3);
0 row(s) fetched.
Elapsed 0.004 seconds.
> EXPLAIN FORMAT INDENT SELECT SUM(x) FROM t GROUP BY b;
+---------------+-------------------------------------------------------------------------------+
| plan_type | plan |
+---------------+-------------------------------------------------------------------------------+
| logical_plan | Projection: sum(t.x) |
| | Aggregate: groupBy=[[t.b]], aggr=[[sum(CAST(t.x AS Int64))]] |
| | TableScan: t projection=[x, b] |
| physical_plan | ProjectionExec: expr=[sum(t.x)@1 as sum(t.x)] |
| | AggregateExec: mode=FinalPartitioned, gby=[b@0 as b], aggr=[sum(t.x)] |
| | CoalesceBatchesExec: target_batch_size=8192 |
| | RepartitionExec: partitioning=Hash([b@0], 16), input_partitions=1 |
| | AggregateExec: mode=Partial, gby=[b@1 as b], aggr=[sum(t.x)] |
| | DataSourceExec: partitions=1, partition_sizes=[1] |
| | |
+---------------+-------------------------------------------------------------------------------+
2 row(s) fetched.
Elapsed 0.004 seconds.pgjson 格式
pgjson 格式是参照 Postgres JSON 格式设计的。
你可以使用该格式在现有的执行计划可视化工具中查看计划，例如 dalibo。
> EXPLAIN FORMAT PGJSON SELECT SUM(x) FROM t GROUP BY b;
+--------------+----------------------------------------------------+
| plan_type | plan |
+--------------+----------------------------------------------------+
| logical_plan | [ |
| | { |
| | "Plan": { |
| | "Expressions": [ |
| | "sum(t.x)" |
| | ], |
| | "Node Type": "Projection", |
| | "Output": [ |
| | "sum(t.x)" |
| | ], |
| | "Plans": [ |
| | { |
| | "Aggregates": "sum(CAST(t.x AS Int64))", |
| | "Group By": "t.b", |
| | "Node Type": "Aggregate", |
| | "Output": [ |
| | "b", |
| | "sum(t.x)" |
| | ], |
| | "Plans": [ |
| | { |
| | "Node Type": "TableScan", |
| | "Output": [ |
| | "x", |
| | "b" |
| | ], |
| | "Plans": [], |
| | "Relation Name": "t" |
| | } |
| | ] |
| | } |
| | ] |
| | } |
| | } |
| | ] |
+--------------+----------------------------------------------------+
1 row(s) fetched.
Elapsed 0.008 seconds.graphviz 格式
graphviz 格式使用 DOT 语言，可配合 Graphviz 使用，以生成该计划的可视化表示。
> EXPLAIN FORMAT GRAPHVIZ SELECT SUM(x) FROM t GROUP BY b;
+--------------+------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan |
+--------------+------------------------------------------------------------------------------------------------------------------------------+
| logical_plan | |
| | // Begin DataFusion GraphViz Plan, |
| | // display it online here: https://dreampuf.github.io/GraphvizOnline |
| | |
| | digraph { |
| | subgraph cluster_1 |
| | { |
| | graph[label="LogicalPlan"] |
| | 2[shape=box label="Projection: sum(t.x)"] |
| | 3[shape=box label="Aggregate: groupBy=[[t.b]], aggr=[[sum(CAST(t.x AS Int64))]]"] |
| | 2 -> 3 [arrowhead=none, arrowtail=normal, dir=back] |
| | 4[shape=box label="TableScan: t projection=[x, b]"] |
| | 3 -> 4 [arrowhead=none, arrowtail=normal, dir=back] |
| | } |
| | subgraph cluster_5 |
| | { |
| | graph[label="Detailed LogicalPlan"] |
| | 6[shape=box label="Projection: sum(t.x)\nSchema: [sum(t.x):Int64;N]"] |
| | 7[shape=box label="Aggregate: groupBy=[[t.b]], aggr=[[sum(CAST(t.x AS Int64))]]\nSchema: [b:Int32;N, sum(t.x):Int64;N]"] |
| | 6 -> 7 [arrowhead=none, arrowtail=normal, dir=back] |
| | 8[shape=box label="TableScan: t projection=[x, b]\nSchema: [x:Int32;N, b:Int32;N]"] |
| | 7 -> 8 [arrowhead=none, arrowtail=normal, dir=back] |
| | } |
| | } |
| | // End DataFusion GraphViz Plan |
| | |
+--------------+------------------------------------------------------------------------------------------------------------------------------+
1 row(s) fetched.
Elapsed 0.010 seconds.EXPLAIN ANALYZE
显示语句的执行计划与指标。EXPLAIN ANALYZE 支持 indent 格式（默认）以及 pgjson 格式；ANALYZE 不支持 tree 和 graphviz 格式。
EXPLAIN ANALYZE SELECT SUM(x) FROM table GROUP BY b;
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Plan with Metrics | CoalescePartitionsExec, metrics=[] |
| | ProjectionExec: expr=[SUM(table.x)@1 as SUM(x)], metrics=[] |
| | HashAggregateExec: mode=FinalPartitioned, gby=[b@0 as b], aggr=[SUM(x)], metrics=[outputRows=2] |
| | CoalesceBatchesExec: target_batch_size=4096, metrics=[] |
| | RepartitionExec: partitioning=Hash([Column { name: "b", index: 0 }], 16), metrics=[sendTime=839560, fetchTime=122528525, repartitionTime=5327877] |
| | HashAggregateExec: mode=Partial, gby=[b@1 as b], aggr=[SUM(x)], metrics=[outputRows=2] |
| | RepartitionExec: partitioning=RoundRobinBatch(16), metrics=[fetchTime=5660489, repartitionTime=0, sendTime=8012] |
| | DataSourceExec: file_groups={1 group: [[/tmp/table.csv]]}, has_header=false, metrics=[] |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+默认情况下，EXPLAIN ANALYZE 会显示每个算子在所有分区上聚合后的指标。若需显示各分区的指标，请使用 EXPLAIN ANALYZE VERBOSE。
你也可以通过配置项设置 datafusion.explain.analyze_level，以控制所显示指标的详细程度。
pgjson 格式与 ANALYZE
EXPLAIN ANALYZE 也可以按 pgjson 格式输出物理计划及其运行时执行指标，从而可以将分析后的计划加载到 PostgreSQL 计划可视化工具中，例如 dalibo。每个节点使用 PostgreSQL 的键名报告 Actual Rows 和 Actual Total Time（计算耗时，单位为毫秒），其余的 DataFusion 指标则归入 Extras。
可以使用关键字形式（EXPLAIN ANALYZE FORMAT pgjson ...）请求该格式，更惯用的方式是使用 PostgreSQL 的选项列表形式，它还允许你将 METRICS 和 LEVEL 这两个调节项与格式在同一条语句中组合使用：
> CREATE TABLE t(x int, b int) AS VALUES (1, 2), (2, 3);
> EXPLAIN (ANALYZE, FORMAT pgjson, METRICS 'rows') SELECT x FROM t WHERE b > 2;
+-------------------+---------------------------------------------------------------------------+
| plan_type | plan |
+-------------------+---------------------------------------------------------------------------+
| Plan with Metrics | [ |
| | { |
| | "Plan": { |
| | "Node Type": "FilterExec", |
| | "Details": "FilterExec: b@1 > 2, projection=[x@0]", |
| | "Actual Rows": 1, |
| | "Extras": { |
| | "output_batches": 1, |
| | "selectivity": "50% (1/2)" |
| | }, |
| | "Plans": [ |
| | { |
| | "Node Type": "DataSourceExec", |
| | "Details": "DataSourceExec: partitions=1, partition_sizes=[1]", |
| | "Plans": [] |
| | } |
| | ] |
| | } |
| | } |
| | ] |
+-------------------+---------------------------------------------------------------------------+评论
登录后参与评论
正在加载评论…