Skip to content

Commit b8c74a7

Browse files
authored
Docs explaining how to view query plans (apache#373)
* Docs explaining how to view query plans * diagram
1 parent e16af72 commit b8c74a7

6 files changed

Lines changed: 289 additions & 3 deletions

File tree

‎docs/mdbook/README.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,7 @@ Open the book locally by running `open book/index.html`.
2626

2727
## Install mdBook
2828

29-
Download the `mdbook` binary.
29+
Download the `mdbook` binary or run `cargo install mdbook`.
3030

3131
Then manually open it, so you have permissions to run it on your Mac.
3232

‎docs/mdbook/src/SUMMARY.md‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,4 +21,5 @@
2121
- [Quickstart](./quickstart.md)
2222
- [Usage](./usage/index.md)
2323
- [Create a table](./usage/create-table.md)
24-
- [Query a table](./usage/query-table.md)
24+
- [Query a table](./usage/query-table.md)
25+
- [Viewing Query Plans](./usage/query-plans.md)

‎docs/mdbook/src/images/plan.svg‎

Lines changed: 111 additions & 0 deletions
Loading
Lines changed: 170 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,170 @@
1+
<!---
2+
Licensed to the Apache Software Foundation (ASF) under one
3+
or more contributor license agreements. See the NOTICE file
4+
distributed with this work for additional information
5+
regarding copyright ownership. The ASF licenses this file
6+
to you under the Apache License, Version 2.0 (the
7+
"License"); you may not use this file except in compliance
8+
with the License. You may obtain a copy of the License at
9+
http://www.apache.org/licenses/LICENSE-2.0
10+
Unless required by applicable law or agreed to in writing,
11+
software distributed under the License is distributed on an
12+
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
13+
KIND, either express or implied. See the License for the
14+
specific language governing permissions and limitations
15+
under the License.
16+
-->
17+
18+
# DataFusion Query Plans
19+
20+
DataFusion's `DataFrame` is a wrapper around a query plan. In this chapter we will learn how to view
21+
logical and physical query plans for DataFrames.
22+
23+
## Sample Data
24+
25+
Let's go ahead and create a simple DataFrame. You can do this in the Python shell or in a notebook.
26+
27+
```python
28+
from datafusion import SessionContext
29+
30+
ctx = SessionContext()
31+
32+
df = ctx.from_pydict({"a": [1, 2, 3, 1], "b": [4, 5, 6, 7]}, name="my_table")
33+
```
34+
35+
## Logical Plan
36+
37+
Next, let's look at the logical plan for this dataframe.
38+
39+
```python
40+
>>> df.logical_plan()
41+
TableScan: my_table
42+
```
43+
44+
The logical plan here consists of a single `TableScan` operator. Let's make a more interesting plan by creating a new
45+
`DataFrame` representing an aggregate query with a filter.
46+
47+
```python
48+
>>> df = ctx.sql("SELECT a, sum(b) FROM my_table WHERE a < 3 GROUP BY a")
49+
```
50+
51+
When we view the plan for this `DataFrame` we can see that there are now four operators in the plan, each
52+
representing a logical transformation of the data. We start with a `TableScan` to read the data, followed by
53+
a `Filter` to filter out rows that do not match the filter expression, then an `Aggregate` is performed. Finally,
54+
a `Projection` is applied to ensure that the order of the final columns matches the `SELECT` part of the SQL query.
55+
56+
```python
57+
>>> df.logical_plan()
58+
Projection: my_table.a, SUM(my_table.b)
59+
Aggregate: groupBy=[[my_table.a]], aggr=[[SUM(my_table.b)]]
60+
Filter: my_table.a < Int64(3)
61+
TableScan: my_table
62+
```
63+
64+
## Optimized Logical Plan
65+
66+
DataFusion has a powerful query optimizer which will rewrite query plans to make them more efficient before they are
67+
executed. We can view the output of the optimized by viewint the optimized logical plan.
68+
69+
```python
70+
>>> df.optimized_logical_plan()
71+
Aggregate: groupBy=[[my_table.a]], aggr=[[SUM(my_table.b)]]
72+
Filter: my_table.a < Int64(3)
73+
TableScan: my_table projection=[a, b]
74+
```
75+
76+
We can see that there are two key differences compared to the unoptimized logical plan:
77+
78+
- The `Projection` has been removed because it was redundant in this case (the output of the `Aggregatge` plan
79+
already had the columns in the correct order).
80+
- The `TableScan` now has a projection pushed down so that it only reads the columns required to be able to execute
81+
the query. In this case the table only has two columns and we referenced them both in the query, but this optimization
82+
can be very effective in real-world queries against large tables.
83+
84+
## Physical Plan
85+
86+
Logical plans provide a representation of "what" the query should do it. Physical plans explain "how" the query
87+
should be executed.
88+
89+
We can view the physical plan (also known as an execution plan) using the `execution_plan` method.
90+
91+
```python
92+
>>> df.execution_plan()
93+
AggregateExec: mode=FinalPartitioned, gby=[a@0 as a], aggr=[SUM(my_table.b)]
94+
CoalesceBatchesExec: target_batch_size=8192
95+
RepartitionExec: partitioning=Hash([Column { name: "a", index: 0 }], 48), input_partitions=48
96+
AggregateExec: mode=Partial, gby=[a@0 as a], aggr=[SUM(my_table.b)]
97+
CoalesceBatchesExec: target_batch_size=8192
98+
FilterExec: a@0 < 3
99+
RepartitionExec: partitioning=RoundRobinBatch(48), input_partitions=1
100+
MemoryExec: partitions=1, partition_sizes=[1]
101+
```
102+
103+
The `TableScan` has now been replaced by a more specific `MemoryExec` for scanning the in-memory data. If we were
104+
querying a CSV file on disk then we would expect to see a `CsvExec` instead.
105+
106+
This plan has additional operators that were not in the logical plan:
107+
108+
- `RepartionExec` has been added so that the data can be split into partitions and processed in parallel using
109+
multiple cores.
110+
- `CoalesceBatchesExec` will combine small batches into larger batches to ensure that processing remains efficient.
111+
112+
The `Aggregate` operator now appears twice. This is because aggregates are performed in a two step process. Data is
113+
aggregated within each partition in parallel and then those results (which could contain duplicate grouping keys) are
114+
combined and the aggregate operations is applied again.
115+
116+
## Creating Query Plan Diagrams
117+
118+
DataFusion supports generating query plan diagrams in [DOT format](<https://en.wikipedia.org/wiki/DOT_(graph_description_language)>).
119+
120+
DOT is a language for describing graphs and there are open source tools such as GraphViz that can render diagrams
121+
from DOT files.
122+
123+
We can use the following code to generate a DOT file for a logical query plan.
124+
125+
```python
126+
>>> diagram = df.logical_plan().display_graphviz()
127+
>>> with open('plan.dot', 'w') as f:
128+
>>> f.write(diagram)
129+
```
130+
131+
If we view the view, we will see the following content.
132+
133+
```
134+
// Begin DataFusion GraphViz Plan (see https://graphviz.org)
135+
digraph {
136+
subgraph cluster_1
137+
{
138+
graph[label="LogicalPlan"]
139+
2[shape=box label="Projection: my_table.a, SUM(my_table.b)"]
140+
3[shape=box label="Aggregate: groupBy=[[my_table.a]], aggr=[[SUM(my_table.b)]]"]
141+
2 -> 3 [arrowhead=none, arrowtail=normal, dir=back]
142+
4[shape=box label="Filter: my_table.a < Int64(3)"]
143+
3 -> 4 [arrowhead=none, arrowtail=normal, dir=back]
144+
5[shape=box label="TableScan: my_table"]
145+
4 -> 5 [arrowhead=none, arrowtail=normal, dir=back]
146+
}
147+
subgraph cluster_6
148+
{
149+
graph[label="Detailed LogicalPlan"]
150+
7[shape=box label="Projection: my_table.a, SUM(my_table.b)\nSchema: [a:Int64;N, SUM(my_table.b):Int64;N]"]
151+
8[shape=box label="Aggregate: groupBy=[[my_table.a]], aggr=[[SUM(my_table.b)]]\nSchema: [a:Int64;N, SUM(my_table.b):Int64;N]"]
152+
7 -> 8 [arrowhead=none, arrowtail=normal, dir=back]
153+
9[shape=box label="Filter: my_table.a < Int64(3)\nSchema: [a:Int64;N, b:Int64;N]"]
154+
8 -> 9 [arrowhead=none, arrowtail=normal, dir=back]
155+
10[shape=box label="TableScan: my_table\nSchema: [a:Int64;N, b:Int64;N]"]
156+
9 -> 10 [arrowhead=none, arrowtail=normal, dir=back]
157+
}
158+
}
159+
// End DataFusion GraphViz Plan
160+
```
161+
162+
We can use GraphViz from the command-line to convert this DOT file into an image.
163+
164+
```bash
165+
dot -Tsvg plan.dot > plan.svg
166+
```
167+
168+
This generates the following diagram:
169+
170+
![Query Plan Diagram](../images/plan.svg)

‎src/physical_plan.rs‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -54,6 +54,10 @@ impl PyExecutionPlan {
5454
format!("{}", d.indent())
5555
}
5656

57+
fn __repr__(&self) -> String {
58+
self.display_indent()
59+
}
60+
5761
#[getter]
5862
pub fn partition_count(&self) -> usize {
5963
self.plan.output_partitioning().partition_count()

‎src/sql/logical.rs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -100,7 +100,7 @@ impl PyLogicalPlan {
100100
}
101101

102102
fn display_graphviz(&self) -> String {
103-
format!("{}", self.plan.display_indent_schema())
103+
format!("{}", self.plan.display_graphviz())
104104
}
105105
}
106106

0 commit comments

Comments
 (0)