-
Notifications
You must be signed in to change notification settings - Fork 1
/
partitioned3wayJoinWithAndWithoutStats
138 lines (132 loc) · 10.6 KB
/
partitioned3wayJoinWithAndWithoutStats
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
[gromit.lurie.biz:21000] > explain select a.myweekday, count(*) from partitionparquet a, partitionparquet b, partitionparquet c where a.myrowid=b.myrowid and b.myrowid=c.myrowid and a.myonein10k=500 group by a.myweekday;
Query: explain select a.myweekday, count(*) from partitionparquet a, partitionparquet b, partitionparquet c where a.myrowid=b.myrowid and b.myrowid=c.myrowid and a.myonein10k=500 group by a.myweekday
+------------------------------------------------------------------------------------+
| Explain String |
+------------------------------------------------------------------------------------+
| Max Per-Host Resource Reservation: Memory=74.94MB |
| Per-Host Resource Estimates: Memory=4.08GB |
| WARNING: The following tables are missing relevant table and/or column statistics. |
| default.partitionparquet |
| |
| PLAN-ROOT SINK |
| | |
| 10:EXCHANGE [UNPARTITIONED] |
| | |
| 09:AGGREGATE [FINALIZE] |
| | output: count:merge(*) |
| | group by: a.myweekday |
| | |
| 08:EXCHANGE [HASH(a.myweekday)] |
| | |
| 05:AGGREGATE [STREAMING] |
| | output: count(*) |
| | group by: a.myweekday |
| | |
| 04:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash predicates: b.myrowid = c.myrowid |
| | runtime filters: RF000 <- c.myrowid |
| | |
| |--07:EXCHANGE [BROADCAST] |
| | | |
| | 02:SCAN HDFS [default.partitionparquet c] |
| | partitions=84/84 files=84 size=8.17MB |
| | |
| 03:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash predicates: a.myrowid = b.myrowid |
| | runtime filters: RF002 <- b.myrowid |
| | |
| |--06:EXCHANGE [BROADCAST] |
| | | |
| | 01:SCAN HDFS [default.partitionparquet b] |
| | partitions=84/84 files=84 size=8.17MB |
| | runtime filters: RF000 -> b.myrowid |
| | |
| 00:SCAN HDFS [default.partitionparquet a] |
| partitions=84/84 files=84 size=8.17MB |
| predicates: a.myonein10k = 500 |
| runtime filters: RF000 -> a.myrowid, RF002 -> a.myrowid |
+------------------------------------------------------------------------------------+
Fetched 42 row(s) in 0.03s
[gromit.lurie.biz:21000] > compute stats partitionparquet;
Query: compute stats partitionparquet
+------------------------------------------+
| summary |
+------------------------------------------+
| Updated 84 partition(s) and 4 column(s). |
+------------------------------------------+
Fetched 1 row(s) in 1.63s
[gromit.lurie.biz:21000] > explain select a.myweekday, count(*) from partitionparquet a, partitionparquet b, partitionparquet c where a.myrowid=b.myrowid and b.myrowid=c.myrowid and a.myonein10k=500 group by a.myweekday;
Query: explain select a.myweekday, count(*) from partitionparquet a, partitionparquet b, partitionparquet c where a.myrowid=b.myrowid and b.myrowid=c.myrowid and a.myonein10k=500 group by a.myweekday
+--------------------------------------------------+
| Explain String |
+--------------------------------------------------+
| Max Per-Host Resource Reservation: Memory=9.81MB |
| Per-Host Resource Estimates: Memory=89.88MB |
| |
| PLAN-ROOT SINK |
| | |
| 10:EXCHANGE [UNPARTITIONED] |
| | |
| 09:AGGREGATE [FINALIZE] |
| | output: count:merge(*) |
| | group by: a.myweekday |
| | |
| 08:EXCHANGE [HASH(a.myweekday)] |
| | |
| 05:AGGREGATE [STREAMING] |
| | output: count(*) |
| | group by: a.myweekday |
| | |
| 04:HASH JOIN [INNER JOIN, BROADCAST] |
| | hash predicates: c.myrowid = b.myrowid |
| | runtime filters: RF000 <- b.myrowid |
| | |
| |--07:EXCHANGE [BROADCAST] |
| | | |
| | 03:HASH JOIN [INNER JOIN, BROADCAST] |
| | | hash predicates: b.myrowid = a.myrowid |
| | | runtime filters: RF002 <- a.myrowid |
| | | |
| | |--06:EXCHANGE [BROADCAST] |
| | | | |
| | | 00:SCAN HDFS [default.partitionparquet a] |
| | | partitions=84/84 files=84 size=8.17MB |
| | | predicates: a.myonein10k = 500 |
| | | |
| | 01:SCAN HDFS [default.partitionparquet b] |
| | partitions=84/84 files=84 size=8.17MB |
| | runtime filters: RF002 -> b.myrowid |
| | |
| 02:SCAN HDFS [default.partitionparquet c] |
| partitions=84/84 files=84 size=8.17MB |
| runtime filters: RF000 -> c.myrowid |
+--------------------------------------------------+
Fetched 40 row(s) in 0.06s
NO STATS:
Operator #Hosts Avg Time Max Time #Rows Est. #Rows Peak Mem Est. Peak Mem Detail
------------------------------------------------------------------------------------------------------------------------
10:EXCHANGE 1 20.771us 20.771us 7 7 64.00 KB 0 UNPARTITIONED
09:AGGREGATE 1 915.407us 915.407us 7 7 1.96 MB 10.00 MB FINALIZE
08:EXCHANGE 1 19.044us 19.044us 7 7 64.00 KB 0 HASH(a.myweekday)
05:AGGREGATE 1 80.683us 80.683us 7 7 2.66 MB 10.00 MB STREAMING
04:HASH JOIN 1 566.198us 566.198us 100 -1 64.66 MB 2.00 GB INNER JOIN, BROADCAST
|--07:EXCHANGE 1 10.662ms 10.662ms 1.00M -1 12.72 MB 0 BROADCAST
| 02:SCAN HDFS 1 26.823ms 26.823ms 1.00M -1 1.45 MB 16.00 MB default.partitionparquet c
03:HASH JOIN 1 115.352ms 115.352ms 100 -1 64.04 MB 2.00 GB INNER JOIN, BROADCAST
|--06:EXCHANGE 1 15.626ms 15.626ms 1.00M -1 480.00 KB 0 BROADCAST
| 01:SCAN HDFS 1 527.684ms 527.684ms 1.00M -1 1.66 MB 16.00 MB default.partitionparquet b
00:SCAN HDFS 1 20.612ms 20.612ms 100 -1 1.63 MB 32.00 MB default.partitionparquet a
WITH STATS - broadcast number of rows, number of rows in the hash-join pipeline is now 100 instead of 1 million
Operator #Hosts Avg Time Max Time #Rows Est. #Rows Peak Mem Est. Peak Mem Detail
---------------------------------------------------------------------------------------------------------------------------
10:EXCHANGE 1 16.042us 16.042us 7 7 64.00 KB 0 UNPARTITIONED
09:AGGREGATE 1 589.659us 589.659us 7 7 1.96 MB 10.00 MB FINALIZE
08:EXCHANGE 1 12.484us 12.484us 7 7 64.00 KB 0 HASH(a.myweekday)
05:AGGREGATE 1 0.000ns 0.000ns 7 7 2.53 MB 10.00 MB STREAMING
04:HASH JOIN 1 1.168ms 1.168ms 100 97 1.99 MB 1.94 MB INNER JOIN, BROADCAST
|--07:EXCHANGE 1 6.460us 6.460us 100 97 16.00 KB 0 BROADCAST
| 03:HASH JOIN 1 1.426ms 1.426ms 100 97 1.98 MB 1.94 MB INNER JOIN, BROADCAST
| |--06:EXCHANGE 1 46.063us 46.063us 100 97 336.00 KB 0 BROADCAST
| | 00:SCAN HDFS 1 11.849ms 11.849ms 100 97 862.28 KB 32.00 MB default.partitionparquet a
| 01:SCAN HDFS 1 19.002ms 19.002ms 100 1.00M 1.06 MB 16.00 MB default.partitionparquet b
02:SCAN HDFS 1 13.094ms 13.094ms 100 1.00M 1.45 MB 16.00 MB default.partitionparquet c