This is an automated email from the ASF dual-hosted git repository. jakevin pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 9c7016f6e7 [docs](query plan) Add a description `explain graph` and
`desc graph` and `explain verbose` (#11697)
9c7016f6e7 is described below
commit 9c7016f6e7dd75ff3ef342151883a4055d8890a4
Author: iceqing <[email protected]>
AuthorDate: Tue Aug 30 19:12:23 2022 +0800
[docs](query plan) Add a description `explain graph` and `desc graph` and
`explain verbose` (#11697)
* docs: 增加explain graph与desc graph描述，避免文档前后描述不一致
* docs: add the usage docs of `explain verbose`
---
.../docs/advanced/best-practice/query-analysis.md | 178 +++++++++++++++++++-
.../docs/advanced/best-practice/query-analysis.md | 179 ++++++++++++++++++++-
2 files changed, 351 insertions(+), 6 deletions(-)
diff --git a/docs/en/docs/advanced/best-practice/query-analysis.md
b/docs/en/docs/advanced/best-practice/query-analysis.md
index 4c569eaaa5..71a9afd69f 100644
--- a/docs/en/docs/advanced/best-practice/query-analysis.md
+++ b/docs/en/docs/advanced/best-practice/query-analysis.md
@@ -95,15 +95,16 @@ And a Fragment will be further divided into multiple
Instances. Instance is the
## View query plan
-You can view the execution plan of a SQL through the following two commands.
+You can view the execution plan of a SQL through the following three commands.
-- `EXPLAIN GRAPH select ...;`
+- `EXPLAIN GRAPH select ...;` OR `DESC GRAPH select ...;`
- `EXPLAIN select ...;`
+- `EXPLAIN VERBOSE select ...;`
The first command displays a query plan graphically. This command can more
intuitively display the tree structure of the query plan and the division of
Fragments:
```sql
-mysql> desc graph select tbl1.k1, sum(tbl1.k2) from tbl1 join tbl2 on tbl1.k1
= tbl2.k1 group by tbl1.k1 order by tbl1.k1;
+mysql> explain graph select tbl1.k1, sum(tbl1.k2) from tbl1 join tbl2 on
tbl1.k1 = tbl2.k1 group by tbl1.k1 order by tbl1.k1;
+---------------------------------------------------------------------------------------------------------------------------------+
| Explain String
|
+---------------------------------------------------------------------------------------------------------------------------------+
@@ -287,6 +288,177 @@ mysql> explain select tbl1.k1, sum(tbl1.k2) from tbl1
join tbl2 on tbl1.k1 = tbl
+----------------------------------------------------------------------------------+
```
+The third command `explain verbose select ...;` gives you more details than
the second command.
+
+```sql
+mysql> explain verbose select tbl1.k1, sum(tbl1.k2) from tbl1 join tbl2 on
tbl1.k1 = tbl2.k1 group by tbl1.k1 order by tbl1.k1;
++---------------------------------------------------------------------------------------------------------------------------------------------------------+
+| Explain String
|
++---------------------------------------------------------------------------------------------------------------------------------------------------------+
+| PLAN FRAGMENT 0
|
+| OUTPUT EXPRS:<slot 5> <slot 3> `tbl1`.`k1` | <slot 6> <slot 4>
sum(`tbl1`.`k2`)
|
+| PARTITION: UNPARTITIONED
|
+|
|
+| VRESULT SINK
|
+|
|
+| 6:VMERGING-EXCHANGE
|
+| limit: 65535
|
+| tuple ids: 3
|
+|
|
+| PLAN FRAGMENT 1
|
+|
|
+| PARTITION: HASH_PARTITIONED: `default_cluster:test`.`tbl1`.`k2`
|
+|
|
+| STREAM DATA SINK
|
+| EXCHANGE ID: 06
|
+| UNPARTITIONED
|
+|
|
+| 4:VTOP-N
|
+| | order by: <slot 5> <slot 3> `tbl1`.`k1` ASC
|
+| | offset: 0
|
+| | limit: 65535
|
+| | tuple ids: 3
|
+| |
|
+| 3:VAGGREGATE (update finalize)
|
+| | output: sum(<slot 8>)
|
+| | group by: <slot 7>
|
+| | cardinality=-1
|
+| | tuple ids: 2
|
+| |
|
+| 2:VHASH JOIN
|
+| | join op: INNER JOIN(BROADCAST)[Tables are not in the same group]
|
+| | equal join conjunct: CAST(`tbl1`.`k1` AS DATETIME) = `tbl2`.`k1`
|
+| | runtime filters: RF000[in_or_bloom] <- `tbl2`.`k1`
|
+| | cardinality=0
|
+| | vec output tuple id: 4 | tuple ids: 0 1
|
+| |
|
+| |----5:VEXCHANGE
|
+| | tuple ids: 1
|
+| |
|
+| 0:VOlapScanNode
|
+| TABLE: tbl1(null), PREAGGREGATION: OFF. Reason: the type of agg on
StorageEngine's Key column should only be MAX or MIN.agg expr: sum(`tbl1`.`k2`)
|
+| runtime filters: RF000[in_or_bloom] -> CAST(`tbl1`.`k1` AS DATETIME)
|
+| partitions=0/1, tablets=0/0, tabletList=
|
+| cardinality=0, avgRowSize=20.0, numNodes=1
|
+| tuple ids: 0
|
+|
|
+| PLAN FRAGMENT 2
|
+|
|
+| PARTITION: HASH_PARTITIONED: `default_cluster:test`.`tbl2`.`k2`
|
+|
|
+| STREAM DATA SINK
|
+| EXCHANGE ID: 05
|
+| UNPARTITIONED
|
+|
|
+| 1:VOlapScanNode
|
+| TABLE: tbl2(null), PREAGGREGATION: OFF. Reason: null
|
+| partitions=0/1, tablets=0/0, tabletList=
|
+| cardinality=0, avgRowSize=16.0, numNodes=1
|
+| tuple ids: 1
|
+|
|
+| Tuples:
|
+| TupleDescriptor{id=0, tbl=tbl1, byteSize=32, materialized=true}
|
+| SlotDescriptor{id=0, col=k1, type=DATE}
|
+| parent=0
|
+| materialized=true
|
+| byteSize=16
|
+| byteOffset=16
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=1
|
+|
|
+| SlotDescriptor{id=2, col=k2, type=INT}
|
+| parent=0
|
+| materialized=true
|
+| byteSize=4
|
+| byteOffset=0
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=0
|
+|
|
+|
|
+| TupleDescriptor{id=1, tbl=tbl2, byteSize=16, materialized=true}
|
+| SlotDescriptor{id=1, col=k1, type=DATETIME}
|
+| parent=1
|
+| materialized=true
|
+| byteSize=16
|
+| byteOffset=0
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=0
|
+|
|
+|
|
+| TupleDescriptor{id=2, tbl=null, byteSize=32, materialized=true}
|
+| SlotDescriptor{id=3, col=null, type=DATE}
|
+| parent=2
|
+| materialized=true
|
+| byteSize=16
|
+| byteOffset=16
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=1
|
+|
|
+| SlotDescriptor{id=4, col=null, type=BIGINT}
|
+| parent=2
|
+| materialized=true
|
+| byteSize=8
|
+| byteOffset=0
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=0
|
+|
|
+|
|
+| TupleDescriptor{id=3, tbl=null, byteSize=32, materialized=true}
|
+| SlotDescriptor{id=5, col=null, type=DATE}
|
+| parent=3
|
+| materialized=true
|
+| byteSize=16
|
+| byteOffset=16
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=1
|
+|
|
+| SlotDescriptor{id=6, col=null, type=BIGINT}
|
+| parent=3
|
+| materialized=true
|
+| byteSize=8
|
+| byteOffset=0
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=0
|
+|
|
+|
|
+| TupleDescriptor{id=4, tbl=null, byteSize=48, materialized=true}
|
+| SlotDescriptor{id=7, col=k1, type=DATE}
|
+| parent=4
|
+| materialized=true
|
+| byteSize=16
|
+| byteOffset=16
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=1
|
+|
|
+| SlotDescriptor{id=8, col=k2, type=INT}
|
+| parent=4
|
+| materialized=true
|
+| byteSize=4
|
+| byteOffset=0
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=0
|
+|
|
+| SlotDescriptor{id=9, col=k1, type=DATETIME}
|
+| parent=4
|
+| materialized=true
|
+| byteSize=16
|
+| byteOffset=32
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=2
|
++---------------------------------------------------------------------------------------------------------------------------------------------------------+
+160 rows in set (0.00 sec)
+```
+
> The information displayed in the query plan is still being standardized and
> improved, and we will introduce it in detail in subsequent articles.
## View query Profile
diff --git a/docs/zh-CN/docs/advanced/best-practice/query-analysis.md
b/docs/zh-CN/docs/advanced/best-practice/query-analysis.md
index 4febae2e40..4ddcf37eba 100644
--- a/docs/zh-CN/docs/advanced/best-practice/query-analysis.md
+++ b/docs/zh-CN/docs/advanced/best-practice/query-analysis.md
@@ -94,15 +94,16 @@ Doris 的查询规划过程是先将一个 SQL 语句转换成一个单机执行
## 查看查询计划
-可以通过以下两种命令查看一个 SQL 的执行计划。
+可以通过以下三种命令查看一个 SQL 的执行计划。
-- `EXPLAIN GRAPH select ...;`
+- `EXPLAIN GRAPH select ...;` 或者 `DESC GRAPH select ...;`
- `EXPLAIN select ...;`
+- `EXPLAIN VERBOSE select ...;`
其中第一个命令以图形化的方式展示一个查询计划，这个命令可以比较直观的展示查询计划的树形结构，以及 Fragment 的划分情况：
```sql
-mysql> desc graph select tbl1.k1, sum(tbl1.k2) from tbl1 join tbl2 on tbl1.k1
= tbl2.k1 group by tbl1.k1 order by tbl1.k1;
+mysql> explain graph select tbl1.k1, sum(tbl1.k2) from tbl1 join tbl2 on
tbl1.k1 = tbl2.k1 group by tbl1.k1 order by tbl1.k1;
+---------------------------------------------------------------------------------------------------------------------------------+
| Explain String
|
+---------------------------------------------------------------------------------------------------------------------------------+
@@ -286,6 +287,178 @@ mysql> explain select tbl1.k1, sum(tbl1.k2) from tbl1
join tbl2 on tbl1.k1 = tbl
+----------------------------------------------------------------------------------+
```
+第三个命令`EXPLAIN VERBOSE select ...;`相比第二个命令可以查看更详细的执行计划信息。
+
+```sql
+mysql> explain verbose select tbl1.k1, sum(tbl1.k2) from tbl1 join tbl2 on
tbl1.k1 = tbl2.k1 group by tbl1.k1 order by tbl1.k1;
++---------------------------------------------------------------------------------------------------------------------------------------------------------+
+| Explain String
|
++---------------------------------------------------------------------------------------------------------------------------------------------------------+
+| PLAN FRAGMENT 0
|
+| OUTPUT EXPRS:<slot 5> <slot 3> `tbl1`.`k1` | <slot 6> <slot 4>
sum(`tbl1`.`k2`)
|
+| PARTITION: UNPARTITIONED
|
+|
|
+| VRESULT SINK
|
+|
|
+| 6:VMERGING-EXCHANGE
|
+| limit: 65535
|
+| tuple ids: 3
|
+|
|
+| PLAN FRAGMENT 1
|
+|
|
+| PARTITION: HASH_PARTITIONED: `default_cluster:test`.`tbl1`.`k2`
|
+|
|
+| STREAM DATA SINK
|
+| EXCHANGE ID: 06
|
+| UNPARTITIONED
|
+|
|
+| 4:VTOP-N
|
+| | order by: <slot 5> <slot 3> `tbl1`.`k1` ASC
|
+| | offset: 0
|
+| | limit: 65535
|
+| | tuple ids: 3
|
+| |
|
+| 3:VAGGREGATE (update finalize)
|
+| | output: sum(<slot 8>)
|
+| | group by: <slot 7>
|
+| | cardinality=-1
|
+| | tuple ids: 2
|
+| |
|
+| 2:VHASH JOIN
|
+| | join op: INNER JOIN(BROADCAST)[Tables are not in the same group]
|
+| | equal join conjunct: CAST(`tbl1`.`k1` AS DATETIME) = `tbl2`.`k1`
|
+| | runtime filters: RF000[in_or_bloom] <- `tbl2`.`k1`
|
+| | cardinality=0
|
+| | vec output tuple id: 4 | tuple ids: 0 1
|
+| |
|
+| |----5:VEXCHANGE
|
+| | tuple ids: 1
|
+| |
|
+| 0:VOlapScanNode
|
+| TABLE: tbl1(null), PREAGGREGATION: OFF. Reason: the type of agg on
StorageEngine's Key column should only be MAX or MIN.agg expr: sum(`tbl1`.`k2`)
|
+| runtime filters: RF000[in_or_bloom] -> CAST(`tbl1`.`k1` AS DATETIME)
|
+| partitions=0/1, tablets=0/0, tabletList=
|
+| cardinality=0, avgRowSize=20.0, numNodes=1
|
+| tuple ids: 0
|
+|
|
+| PLAN FRAGMENT 2
|
+|
|
+| PARTITION: HASH_PARTITIONED: `default_cluster:test`.`tbl2`.`k2`
|
+|
|
+| STREAM DATA SINK
|
+| EXCHANGE ID: 05
|
+| UNPARTITIONED
|
+|
|
+| 1:VOlapScanNode
|
+| TABLE: tbl2(null), PREAGGREGATION: OFF. Reason: null
|
+| partitions=0/1, tablets=0/0, tabletList=
|
+| cardinality=0, avgRowSize=16.0, numNodes=1
|
+| tuple ids: 1
|
+|
|
+| Tuples:
|
+| TupleDescriptor{id=0, tbl=tbl1, byteSize=32, materialized=true}
|
+| SlotDescriptor{id=0, col=k1, type=DATE}
|
+| parent=0
|
+| materialized=true
|
+| byteSize=16
|
+| byteOffset=16
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=1
|
+|
|
+| SlotDescriptor{id=2, col=k2, type=INT}
|
+| parent=0
|
+| materialized=true
|
+| byteSize=4
|
+| byteOffset=0
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=0
|
+|
|
+|
|
+| TupleDescriptor{id=1, tbl=tbl2, byteSize=16, materialized=true}
|
+| SlotDescriptor{id=1, col=k1, type=DATETIME}
|
+| parent=1
|
+| materialized=true
|
+| byteSize=16
|
+| byteOffset=0
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=0
|
+|
|
+|
|
+| TupleDescriptor{id=2, tbl=null, byteSize=32, materialized=true}
|
+| SlotDescriptor{id=3, col=null, type=DATE}
|
+| parent=2
|
+| materialized=true
|
+| byteSize=16
|
+| byteOffset=16
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=1
|
+|
|
+| SlotDescriptor{id=4, col=null, type=BIGINT}
|
+| parent=2
|
+| materialized=true
|
+| byteSize=8
|
+| byteOffset=0
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=0
|
+|
|
+|
|
+| TupleDescriptor{id=3, tbl=null, byteSize=32, materialized=true}
|
+| SlotDescriptor{id=5, col=null, type=DATE}
|
+| parent=3
|
+| materialized=true
|
+| byteSize=16
|
+| byteOffset=16
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=1
|
+|
|
+| SlotDescriptor{id=6, col=null, type=BIGINT}
|
+| parent=3
|
+| materialized=true
|
+| byteSize=8
|
+| byteOffset=0
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=0
|
+|
|
+|
|
+| TupleDescriptor{id=4, tbl=null, byteSize=48, materialized=true}
|
+| SlotDescriptor{id=7, col=k1, type=DATE}
|
+| parent=4
|
+| materialized=true
|
+| byteSize=16
|
+| byteOffset=16
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=1
|
+|
|
+| SlotDescriptor{id=8, col=k2, type=INT}
|
+| parent=4
|
+| materialized=true
|
+| byteSize=4
|
+| byteOffset=0
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=0
|
+|
|
+| SlotDescriptor{id=9, col=k1, type=DATETIME}
|
+| parent=4
|
+| materialized=true
|
+| byteSize=16
|
+| byteOffset=32
|
+| nullIndicatorByte=0
|
+| nullIndicatorBit=-1
|
+| slotIdx=2
|
++---------------------------------------------------------------------------------------------------------------------------------------------------------+
+160 rows in set (0.00 sec)
+```
+
+
> 查询计划中显示的信息还在不断规范和完善中，我们将在后续的文章中详细介绍。
## 查看查询 Profile
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
