-
Notifications
You must be signed in to change notification settings - Fork 16
/
Copy pathexplain-analyze.html
164 lines (148 loc) · 7.54 KB
/
explain-analyze.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>8.19. EXPLAIN ANALYZE — Presto 0.186 Documentation</title>
<link rel="stylesheet" href="../_static/presto.css" type="text/css" />
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '0.186',
COLLAPSE_INDEX: false,
FILE_SUFFIX: '.html',
HAS_SOURCE: true
};
</script>
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<link rel="index" title="Index" href="../genindex.html" />
<link rel="search" title="Search" href="../search.html" />
<link rel="top" title="Presto 0.186 Documentation" href="../index.html" />
<link rel="up" title="8. SQL Statement Syntax" href="../sql.html" />
<link rel="next" title="8.20. GRANT" href="grant.html" />
<link rel="prev" title="8.18. EXPLAIN" href="explain.html" />
</head>
<body role="document">
<div class="header">
<h1 class="heading"><a href="../index.html">
<span>Presto 0.186 Documentation</span></a></h1>
<h2 class="heading"><span>8.19. EXPLAIN ANALYZE</span></h2>
</div>
<div class="topnav">
<p class="nav">
<span class="left">
« <a href="explain.html">8.18. EXPLAIN</a>
</span>
<span class="right">
<a href="grant.html">8.20. GRANT</a> »
</span>
</p>
</div>
<div class="content">
<div class="section" id="explain-analyze">
<h1>8.19. EXPLAIN ANALYZE</h1>
<div class="section" id="synopsis">
<h2>Synopsis</h2>
<div class="highlight-none"><div class="highlight"><pre><span></span>EXPLAIN ANALYZE [VERBOSE] statement
</pre></div>
</div>
</div>
<div class="section" id="description">
<h2>Description</h2>
<p>Execute the statement and show the distributed execution plan of the statement
along with the cost of each operation.</p>
<p>The <code class="docutils literal"><span class="pre">VERBOSE</span></code> option will give more detailed information and low-level statistics;
understanding these may require knowledge of Presto internals and implementation details.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">The stats may not be entirely accurate, especially for queries that complete quickly.</p>
</div>
</div>
<div class="section" id="examples">
<h2>Examples</h2>
<p>In the example below, you can see the CPU time spent in each stage, as well as the relative
cost of each plan node in the stage. Note that the relative cost of the plan nodes is based on
wall time, which may or may not be correlated to CPU time. For each plan node you can see
some additional statistics (e.g: average input per node instance, average number of hash collisions for
relevant plan nodes). Such statistics are useful when one wants to detect data anomalies for a query
(skewness, abnormal hash collisions).</p>
<div class="highlight-none"><div class="highlight"><pre><span></span>presto:sf1> EXPLAIN ANALYZE SELECT count(*), clerk FROM orders WHERE orderdate > date '1995-01-01' GROUP BY clerk;
Query Plan
-----------------------------------------------------------------------------------------------
Fragment 1 [HASH]
Cost: CPU 88.57ms, Input: 4000 rows (148.44kB), Output: 1000 rows (28.32kB)
Output layout: [count, clerk]
Output partitioning: SINGLE []
- Project[] => [count:bigint, clerk:varchar(15)]
Cost: 26.24%, Input: 1000 rows (37.11kB), Output: 1000 rows (28.32kB), Filtered: 0.00%
Input avg.: 62.50 lines, Input std.dev.: 14.77%
- Aggregate(FINAL)[clerk][$hashvalue] => [clerk:varchar(15), $hashvalue:bigint, count:bigint]
Cost: 16.83%, Output: 1000 rows (37.11kB)
Input avg.: 250.00 lines, Input std.dev.: 14.77%
count := "count"("count_8")
- LocalExchange[HASH][$hashvalue] ("clerk") => clerk:varchar(15), count_8:bigint, $hashvalue:bigint
Cost: 47.28%, Output: 4000 rows (148.44kB)
Input avg.: 4000.00 lines, Input std.dev.: 0.00%
- RemoteSource[2] => [clerk:varchar(15), count_8:bigint, $hashvalue_9:bigint]
Cost: 9.65%, Output: 4000 rows (148.44kB)
Input avg.: 4000.00 lines, Input std.dev.: 0.00%
Fragment 2 [tpch:orders:1500000]
Cost: CPU 14.00s, Input: 818058 rows (22.62MB), Output: 4000 rows (148.44kB)
Output layout: [clerk, count_8, $hashvalue_10]
Output partitioning: HASH [clerk][$hashvalue_10]
- Aggregate(PARTIAL)[clerk][$hashvalue_10] => [clerk:varchar(15), $hashvalue_10:bigint, count_8:bigint]
Cost: 4.47%, Output: 4000 rows (148.44kB)
Input avg.: 204514.50 lines, Input std.dev.: 0.05%
Collisions avg.: 5701.28 (17569.93% est.), Collisions std.dev.: 1.12%
count_8 := "count"(*)
- ScanFilterProject[table = tpch:tpch:orders:sf1.0, originalConstraint = ("orderdate" > "$literal$date"(BIGINT '9131')), filterPredicate = ("orderdate" > "$literal$date"(BIGINT '9131'))] => [cler
Cost: 95.53%, Input: 1500000 rows (0B), Output: 818058 rows (22.62MB), Filtered: 45.46%
Input avg.: 375000.00 lines, Input std.dev.: 0.00%
$hashvalue_10 := "combine_hash"(BIGINT '0', COALESCE("$operator$hash_code"("clerk"), 0))
orderdate := tpch:orderdate
clerk := tpch:clerk
</pre></div>
</div>
<p>When the <code class="docutils literal"><span class="pre">VERBOSE</span></code> option is used, some operators may report additional information.
For example, the window function operator will output the following:</p>
<div class="highlight-none"><div class="highlight"><pre><span></span>EXPLAIN ANALYZE VERBOSE SELECT count(clerk) OVER() FROM orders WHERE orderdate > date '1995-01-01';
Query Plan
-----------------------------------------------------------------------------------------------
...
- Window[] => [clerk:varchar(15), count:bigint]
Cost: {rows: ?, bytes: ?}
CPU fraction: 75.93%, Output: 8130 rows (230.24kB)
Input avg.: 8130.00 lines, Input std.dev.: 0.00%
Active Drivers: [ 1 / 1 ]
Index size: std.dev.: 0.00 bytes , 0.00 rows
Index count per driver: std.dev.: 0.00
Rows per driver: std.dev.: 0.00
Size of partition: std.dev.: 0.00
count := count("clerk")
...
</pre></div>
</div>
</div>
<div class="section" id="see-also">
<h2>See Also</h2>
<p><a class="reference internal" href="explain.html"><span class="doc">EXPLAIN</span></a></p>
</div>
</div>
</div>
<div class="bottomnav">
<p class="nav">
<span class="left">
« <a href="explain.html">8.18. EXPLAIN</a>
</span>
<span class="right">
<a href="grant.html">8.20. GRANT</a> »
</span>
</p>
</div>
<div class="footer" role="contentinfo">
</div>
</body>
</html>