| Hi, We are using HAWQ 2.0.0 in one of our product evaluation and we are considerably new with the technology. The HAWQ uses gporca 1.627 to come up with the execution plan. We tried analysing Query 21 of TPC-H benchmarking downloaded from here. We used TPC-H scale factor 1 and run it against a 5 node docker cluster. In Query 21, the table lineitem is referred three times. We expect gporca to apply the Common Subexpression elimination as mentioned in this video. But it did not apply. We manually modified the query to use CTE and found that it executes faster than the original one. I have attached both the queries and the execution plan generated for them.
I will be very glad if someone can clarify why gporca behaves like this. I hope it is the correct forum to raise the question. If it is not, please direct me where to raise the question. Thanks in advance. Best Regards, Saravana Technical Lead Axiomatics AB |
modified_query.sql
Description: Binary data
original_query.sql
Description: Binary data
Gather Motion 18:1 (slice6; segments: 18) (cost=0.00..3421.09 rows=400
width=37)
Merge Key: numwait, supplier.s_name
Rows out: Avg 411.0 rows x 1 workers at destination.
Max/Last(seg-1:hawq/seg-1:hawq) 411/411 rows with 8116/8116 ms to first row,
8117/8117 ms to end, start offset by 911/911 ms.
-> Sort (cost=0.00..3421.05 rows=23 width=37)
Sort Key: numwait, supplier.s_name
Rows out: Avg 22.8 rows x 18 workers.
Max/Last(seg13:slave3/seg2:slave1) 31/12 rows with 7744/8035 ms to end, start
offset by 1273/985 ms.
Executor memory: 58K bytes avg, 58K bytes max (seg17:slave3).
Work_mem used: 58K bytes avg, 58K bytes max (seg17:slave3). Workfile:
(0 spilling, 0 reused)
-> Sequence (cost=0.00..3421.03 rows=23 width=37)
Rows out: Avg 22.8 rows x 18 workers.
Max/Last(seg13:slave3/seg2:slave1) 31/12 rows with 7724/8028 ms to first row,
7730/8034 ms to end, start offset by 1286/986 ms.
-> Shared Scan (share slice:id 6:0) (cost=0.00..534.34
rows=333401 width=1)
Rows out: Avg 333400.8 rows x 18 workers.
Max/Last(seg8:slave2/seg2:slave1) 335120/333470 rows with 4202/6574 ms to first
row, 4471/6795 ms to end, start offset by 1316/986 ms.
-> Materialize (cost=0.00..534.34 rows=333401 width=1)
Rows out: Avg 0.0 rows x 0 workers.
Max/Last(seg17:slave3/seg2:slave1) 0/0 rows with 6574/6574 ms to end.
Work_mem used: 14976K bytes avg, 14976K bytes max
(seg17:slave3). Workfile: (0 spilling, 0 reused)
Work_mem wanted: 51343K bytes avg, 51616K bytes max
(seg8:slave3) to lessen workfile I/O affecting 18 workers.
-> Table Scan on lineitem (cost=0.00..456.49
rows=333401 width=125)
Rows out: Avg 333400.8 rows x 18 workers.
Max/Last(seg8:slave2/seg5:slave1) 335120/333720 rows with 1231/1795 ms to first
row, 2463/3870 ms to end, start offset by 1340/1244 ms.
-> HashAggregate (cost=0.00..2886.70 rows=23 width=37)
Group By: supplier.s_name
Rows out: Avg 22.8 rows x 18 workers.
Max/Last(seg13:slave3/seg17:slave3) 31/16 rows with 3886/4480 ms to first row,
3892/4482 ms to end, start offset by 5125/4542 ms.
Executor memory: 4185K bytes avg, 4185K bytes max
(seg17:slave3).
-> Redistribute Motion 18:18 (slice5; segments: 18)
(cost=0.00..2886.69 rows=23 width=37)
Hash Key: supplier.s_name
Rows out: Avg 176.3 rows x 18 workers at
destination. Max/Last(seg13:slave3/seg17:slave3) 263/119 rows with 3761/4367
ms to first row, 3845/4461 ms to end, start offset by 5161/4559 ms.
-> Result (cost=0.00..2886.69 rows=23 width=37)
Rows out: Avg 176.3 rows x 18 workers.
Max/Last(seg11:slave2/seg1:slave1) 188/182 rows with 7669/7961 ms to first row,
7673/7964 ms to end, start offset by 1262/987 ms.
-> HashAggregate (cost=0.00..2886.69 rows=23
width=37)
Group By: supplier.s_name
Rows out: Avg 176.3 rows x 18 workers.
Max/Last(seg11:slave2/seg1:slave1) 188/182 rows with 7669/7961 ms to first row,
7673/7964 ms to end, start offset by 1262/987 ms.
Executor memory: 4185K bytes avg, 4185K
bytes max (seg17:slave2).
-> Hash Join (cost=0.00..2886.65
rows=319 width=29)
Hash Cond: orders.o_orderkey =
"inner".l_orderkey::bigint AND orders.o_orderkey = "inner".l_orderkey::bigint
Rows out: Avg 230.1 rows x 18
workers. Max/Last(seg14:slave3/seg1:slave1) 257/223 rows with 7520/7748 ms to
first row, 7638/7847 ms to end, start offset by 1336/1100 ms.
Executor memory: 2113K bytes avg,
2113K bytes max (seg17:slave3).
Work_mem used: 26K bytes avg, 28K
bytes max (seg9:slave3). Workfile: (0 spilling, 0 reused)
(seg9) Hash chain length 1.0 avg,
2 max, using 497 of 131111 buckets.
(seg14) Hash chain length 1.0 avg,
2 max, using 482 of 131111 buckets.
-> Redistribute Motion 18:18
(slice1; segments: 18) (cost=0.00..441.52 rows=40736 width=8)
Hash Key: orders.o_orderkey
Rows out: Avg 40522.9 rows x
18 workers at destination. Max/Last(seg5:slave1/seg5:slave1) 40736/40736 rows
with 0.216/0.216 ms to first row, 106/106 ms to end, start offset by 8848/8848
ms.
-> Table Scan on orders
(cost=0.00..440.50 rows=40736 width=8)
Filter: o_orderstatus =
'F'::bpchar
Rows out: Avg 40522.9
rows x 18 workers. Max/Last(seg13:slave3/seg8:slave2) 40795/40413 rows with
819/1789 ms to first row, 1183/2134 ms to end, start offset by 1186/1338 ms.
-> Hash (cost=2437.39..2437.39
rows=499 width=33)
Rows in: Avg 464.3 rows x 18
workers. Max/Last(seg9:slave2/seg1:slave1) 498/443 rows with 7350/7722 ms to
end, start offset by 1498/1126 ms.
-> Redistribute Motion 18:18
(slice4; segments: 18) (cost=0.00..2437.39 rows=499 width=33)
Hash Key:
l_orderkey::bigint
Rows out: Avg 464.3
rows x 18 workers at destination. Max/Last(seg9:slave2/seg1:slave1) 498/443
rows with 7085/6955 ms to first row, 7350/7721 ms to end, start offset by
1498/1126 ms.
-> Hash EXISTS Join
(cost=0.00..2437.34 rows=499 width=33)
Hash Cond:
"outer".l_orderkey = "inner".l_orderkey
Join Filter:
"inner".l_suppkey <> "outer".l_suppkey
Rows out: Avg
464.3 rows x 18 workers. Max/Last(seg9:slave2/seg3:slave1) 498/475 rows with
856/2469 ms to first row, 1226/3294 ms to end, start offset by 7531/4889 ms.
Executor memory:
69633K bytes avg, 69633K bytes max (seg17:slave2).
Work_mem used:
39975K bytes avg, 39975K bytes max (seg13:slave2). Workfile: (18 spilling, 0
reused)
Work_mem wanted:
55078K bytes avg, 55358K bytes max (seg8:slave2) to lessen workfile I/O
affecting 18 workers.
(seg8) Initial
batch 0:
(seg8) Wrote
25776K bytes to inner workfile.
(seg8) Wrote
16K bytes to outer workfile.
(seg8) Overflow
batch 1:
(seg8) Read
51555K bytes from inner workfile.
(seg8) Read
34K bytes from outer workfile.
(seg8) Hash
chain length 4.2 avg, 20 max, using 78935 of 524302 buckets.
(seg9) Initial
batch 0:
(seg9) Wrote
25488K bytes to inner workfile.
(seg9) Wrote
16K bytes to outer workfile.
(seg9) Overflow
batch 1:
(seg9) Read
50978K bytes from inner workfile.
(seg9) Read
37K bytes from outer workfile.
(seg9) Hash
chain length 4.2 avg, 20 max, using 78620 of 524302 buckets.
-> Hash Left
Anti Semi Join (cost=0.00..1871.84 rows=2130 width=37)
Hash Cond:
"outer".l_orderkey = "inner".l_orderkey
Join
Filter: "inner".l_suppkey <> "outer".l_suppkey
Rows out:
Avg 769.9 rows x 18 workers. Max/Last(seg9:slave2/seg1:slave1) 817/767 rows
with 327/1125 ms to first row, 587/1960 ms to end, start offset by 8057/6213 ms.
Executor
memory: 20481K bytes avg, 20481K bytes max (seg17:slave2).
Work_mem
used: 6586K bytes avg, 6613K bytes max (seg6:slave2). Workfile: (0 spilling, 0
reused)
(seg9)
Hash chain length 3.1 avg, 16 max, using 68633 of 262151 buckets.
-> Hash
Join (cost=0.00..1352.91 rows=5324 width=37)
Hash
Cond: "outer".l_suppkey = supplier.s_suppkey
Rows
out: Avg 8707.7 rows x 18 workers. Max/Last(seg10:slave2/seg17:slave3)
8855/8748 rows with 1.391/35 ms to first row, 250/1049 ms to end, start offset
by 8000/5366 ms.
Executor memory: 1089K bytes avg, 1089K bytes max (seg17:slave2).
Work_mem used: 23K bytes avg, 23K bytes max (seg17:slave2). Workfile: (0
spilling, 0 reused)
(seg10) Hash chain length 1.0 avg, 1 max, using 411 of 65539 buckets.
->
Result (cost=0.00..465.79 rows=133361 width=8)
Filter: l_receiptdate > l_commitdate
Rows out: Avg 210738.7 rows x 18 workers. Max/Last(seg6:slave2/seg17:slave3)
211597/210433 rows with 0.204/6.360 ms to first row, 387/788 ms to end, start
offset by 6557/5395 ms.
-> Shared Scan (share slice:id 4:0) (cost=0.00..443.86 rows=333401 width=16)
Rows out: Avg 333400.8 rows x 18 workers.
Max/Last(seg8:slave2/seg17:slave3) 335120/332409 rows with 0.340/6.347 ms to
first row, 218/325 ms to end, start offset by 6918/5395 ms.
->
Hash (cost=862.26..862.26 rows=400 width=33)
Rows in: Avg 411.0 rows x 18 workers. Max/Last(seg17:slave3/seg7:slave2)
411/411 rows with 1.038/1.073 ms to end, start offset by 5394/6708 ms.
-> Broadcast Motion 18:18 (slice3; segments: 18) (cost=0.00..862.26 rows=400
width=33)
Rows out: Avg 411.0 rows x 18 workers at destination.
Max/Last(seg17:slave3/seg17:slave3) 411/411 rows with 0.173/0.173 ms to first
row, 0.576/0.576 ms to end, start offset by 5394/5394 ms.
-> Hash Join (cost=0.00..862.20 rows=23 width=33)
Hash Cond: supplier.s_nationkey = nation.n_nationkey
Rows out: Avg 22.8 rows x 18 workers.
Max/Last(seg5:slave1/seg17:slave3) 31/23 rows with 2546/2701 ms to first row,
2547/2702 ms to end, start offset by 1163/1126 ms.
Executor memory: 2065K bytes avg, 2065K bytes max (seg17:slave1).
Work_mem used: 1K bytes avg, 1K bytes max (seg17:slave1).
Workfile: (0 spilling, 0 reused)
(seg5) Hash chain length 1.0 avg, 1 max, using 1 of 131111
buckets.
-> Table Scan on supplier (cost=0.00..431.05 rows=556 width=37)
Rows out: Avg 555.6 rows x 18 workers.
Max/Last(seg3:slave1/seg3:slave1) 592/592 rows with 934/934 ms to first row,
935/935 ms to end, start offset by 2937/2937 ms.
-> Hash (cost=431.00..431.00 rows=1 width=4)
Rows in: Avg 1.0 rows x 18 workers.
Max/Last(seg17:slave3/seg10:slave2) 1/1 rows with 1793/1953 ms to end, start
offset by 1155/1035 ms.
-> Broadcast Motion 18:18 (slice2; segments: 18)
(cost=0.00..431.00 rows=1 width=4)
Rows out: Avg 1.0 rows x 18 workers at destination.
Max/Last(seg17:slave3/seg10:slave2) 1/1 rows with 1338/1457 ms to first row,
1793/1953 ms to end, start offset by 1155/1035 ms.
-> Table Scan on nation (cost=0.00..431.00 rows=1
width=4)
Filter: n_name = 'SAUDI ARABIA'::bpchar
Rows out: Avg 1.0 rows x 1 workers.
Max/Last(seg13:slave3/seg9:slave2) 1/0 rows with 1157/1584 ms to end, start
offset by 1333/1352 ms.
-> Hash
(cost=465.79..465.79 rows=133361 width=8)
Rows
in: Avg 210738.7 rows x 18 workers. Max/Last(seg6:slave2/seg3:slave1)
211597/210425 rows with 759/1107 ms to end, start offset by 5797/6219 ms.
->
Result (cost=0.00..465.79 rows=133361 width=8)
Filter: l_receiptdate > l_commitdate
Rows out: Avg 210738.7 rows x 18 workers. Max/Last(seg6:slave2/seg1:slave1)
211597/210178 rows with 0.565/0.279 ms to first row, 487/639 ms to end, start
offset by 5797/6231 ms.
-> Shared Scan (share slice:id 4:0) (cost=0.00..443.86 rows=333401 width=16)
Rows out: Avg 333400.8 rows x 18 workers.
Max/Last(seg8:slave2/seg1:slave1) 335120/332862 rows with 0.253/0.258 ms to
first row, 140/300 ms to end, start offset by 6319/6231 ms.
-> Hash
(cost=437.43..437.43 rows=333401 width=8)
Rows in:
Avg 166795.2 rows x 18 workers. Max/Last(seg7:slave2/seg10:slave2)
168209/167397 rows with 695/1813 ms to end, start offset by 5634/5469 ms.
-> Shared
Scan (share slice:id 4:0) (cost=0.00..437.43 rows=333401 width=8)
Rows
out: Avg 333400.8 rows x 18 workers. Max/Last(seg8:slave2/seg10:slave2)
335120/334310 rows with 13/0.485 ms to first row, 132/366 ms to end, start
offset by 5529/5469 ms.
Slice statistics:
(slice0) Executor memory: 766K bytes.
(slice1) Executor memory: 2265K bytes avg x 18 workers, 2267K bytes max
(seg7:slave2).
(slice2) Executor memory: 686K bytes avg x 18 workers, 769K bytes max
(seg13:slave3).
(slice3) Executor memory: 2873K bytes avg x 18 workers, 2874K bytes max
(seg3:slave1). Work_mem: 1K bytes max.
(slice4) * Executor memory: 92006K bytes avg x 18 workers, 92006K bytes max
(seg17:slave3). Work_mem: 39975K bytes max, 55358K bytes wanted.
(slice5) Executor memory: 6938K bytes avg x 18 workers, 6938K bytes max
(seg17:slave3). Work_mem: 28K bytes max.
(slice6) * Executor memory: 12412K bytes avg x 18 workers, 12415K bytes max
(seg0:slave1). Work_mem: 14976K bytes max, 51616K bytes wanted.
Statement statistics:
Memory used: 262144K bytes
Memory wanted: 743137K bytes
Settings: default_hash_table_bucket_number=18
Optimizer status: PQO version 1.627
Dispatcher statistics:
executors used(total/cached/new connection): (108/0/108); dispatcher
time(total/connection/dispatch data): (935.780 ms/902.604 ms/31.910 ms).
dispatch data time(max/min/avg): (6.210 ms/0.045 ms/0.243 ms); consume
executor data time(max/min/avg): (0.066 ms/0.008 ms/0.028 ms); free executor
time(max/min/avg): (0.000 ms/0.000 ms/0.000 ms).
Data locality statistics:
data locality ratio: 1.000; virtual segment number: 18; different host
number: 3; virtual segment number per host(avg/min/max): (6/6/6); segment
size(avg/min/max): (21012431.667 B/20917791 B/21111680 B); segment size with
penalty(avg/min/max): (0.000 B/0 B/0 B); continuity(avg/min/max):
(1.000/1.000/1.000); DFS metadatacache: 2.763 ms; resource allocation: 3.692
ms; datalocality calculation: 0.888 ms.
Total runtime: 6824.472 msGather Motion 18:1 (slice6; segments: 18) (cost=0.00..2948.02 rows=10000
width=37)
Merge Key: numwait, supplier.s_name
Rows out: Avg 411.0 rows x 1 workers at destination.
Max/Last(seg-1:hawq/seg-1:hawq) 411/411 rows with 8637/8637 ms to end, start
offset by 2.383/2.383 ms.
-> Sort (cost=0.00..2947.11 rows=556 width=37)
Sort Key: numwait, supplier.s_name
Rows out: Avg 22.8 rows x 18 workers.
Max/Last(seg13:slave3/seg12:slave3) 31/21 rows with 8568/8628 ms to end, start
offset by 68/10 ms.
Executor memory: 58K bytes avg, 58K bytes max (seg17:slave3).
Work_mem used: 58K bytes avg, 58K bytes max (seg17:slave3). Workfile:
(0 spilling, 0 reused)
-> HashAggregate (cost=0.00..2946.05 rows=556 width=37)
Group By: supplier.s_name
Rows out: Avg 22.8 rows x 18 workers.
Max/Last(seg13:slave3/seg12:slave3) 31/21 rows with 8560/8614 ms to first row,
8568/8628 ms to end, start offset by 68/11 ms.
Executor memory: 4185K bytes avg, 4185K bytes max (seg17:slave3).
-> Redistribute Motion 18:18 (slice5; segments: 18)
(cost=0.00..2945.97 rows=556 width=37)
Hash Key: supplier.s_name
Rows out: Avg 176.3 rows x 18 workers at destination.
Max/Last(seg13:slave3/seg12:slave3) 263/172 rows with 8453/8523 ms to first
row, 8538/8607 ms to end, start offset by 88/15 ms.
-> Result (cost=0.00..2945.91 rows=556 width=37)
Rows out: Avg 176.3 rows x 18 workers.
Max/Last(seg11:slave2/seg9:slave2) 188/185 rows with 8377/8576 ms to first row,
8378/8577 ms to end, start offset by 234/42 ms.
-> HashAggregate (cost=0.00..2945.91 rows=556
width=37)
Group By: supplier.s_name
Rows out: Avg 176.3 rows x 18 workers.
Max/Last(seg11:slave2/seg9:slave2) 188/185 rows with 8377/8576 ms to first row,
8378/8577 ms to end, start offset by 234/42 ms.
Executor memory: 4185K bytes avg, 4185K bytes
max (seg17:slave2).
-> Hash Join (cost=0.00..2945.72 rows=1360
width=29)
Hash Cond: orders.o_orderkey =
dbo.lineitem.l_orderkey::bigint AND orders.o_orderkey =
dbo.lineitem.l_orderkey::bigint
Rows out: Avg 230.1 rows x 18 workers.
Max/Last(seg14:slave3/seg9:slave2) 257/237 rows with 8328/8453 ms to first row,
8415/8571 ms to end, start offset by 172/46 ms.
Executor memory: 2113K bytes avg, 2113K
bytes max (seg17:slave3).
Work_mem used: 26K bytes avg, 28K bytes
max (seg9:slave3). Workfile: (0 spilling, 0 reused)
(seg9) Hash chain length 1.0 avg, 2
max, using 497 of 131111 buckets.
(seg14) Hash chain length 1.0 avg, 2
max, using 482 of 131111 buckets.
-> Redistribute Motion 18:18 (slice1;
segments: 18) (cost=0.00..441.52 rows=40736 width=8)
Hash Key: orders.o_orderkey
Rows out: Avg 40522.9 rows x 18
workers at destination. Max/Last(seg5:slave1/seg6:slave2) 40736/40678 rows
with 0.166/0.168 ms to first row, 64/77 ms to end, start offset by 8504/8507 ms.
-> Table Scan on orders
(cost=0.00..440.50 rows=40736 width=8)
Filter: o_orderstatus =
'F'::bpchar
Rows out: Avg 40522.9 rows x
18 workers. Max/Last(seg13:slave3/seg9:slave2) 40795/40696 rows with 1022/1439
ms to first row, 1361/1850 ms to end, start offset by 68/46 ms.
-> Hash (cost=2494.97..2494.97
rows=2130 width=33)
Rows in: Avg 464.3 rows x 18
workers. Max/Last(seg9:slave2/seg10:slave2) 498/497 rows with 8451/8454 ms to
end, start offset by 49/56 ms.
-> Redistribute Motion 18:18
(slice4; segments: 18) (cost=0.00..2494.97 rows=2130 width=33)
Hash Key:
dbo.lineitem.l_orderkey::bigint
Rows out: Avg 464.3 rows x
18 workers at destination. Max/Last(seg9:slave2/seg10:slave2) 498/497 rows
with 6876/7289 ms to first row, 8450/8454 ms to end, start offset by 49/56 ms.
-> Hash EXISTS Join
(cost=0.00..2494.75 rows=2130 width=33)
Hash Cond:
dbo.lineitem.l_orderkey = dbo.lineitem.l_orderkey
Join Filter:
dbo.lineitem.l_suppkey <> dbo.lineitem.l_suppkey
Rows out: Avg 464.3
rows x 18 workers. Max/Last(seg9:slave2/seg12:slave3) 498/456 rows with
6012/6758 ms to first row, 8129/8316 ms to end, start offset by 145/113 ms.
Executor memory:
20481K bytes avg, 20481K bytes max (seg17:slave2).
Work_mem used: 10419K
bytes avg, 10473K bytes max (seg8:slave2). Workfile: (0 spilling, 0 reused)
(seg9) Hash chain
length 4.5 avg, 20 max, using 74114 of 262151 buckets.
-> Hash Left Anti Semi
Join (cost=0.00..1905.04 rows=2130 width=37)
Hash Cond:
dbo.lineitem.l_orderkey = dbo.lineitem.l_orderkey
Join Filter:
dbo.lineitem.l_suppkey <> dbo.lineitem.l_suppkey
Rows out: Avg
769.9 rows x 18 workers. Max/Last(seg9:slave2/seg8:slave2) 817/769 rows with
3122/3052 ms to first row, 5198/5352 ms to end, start offset by 3032/2951 ms.
Executor memory:
20481K bytes avg, 20481K bytes max (seg17:slave2).
Work_mem used:
6586K bytes avg, 6613K bytes max (seg6:slave2). Workfile: (0 spilling, 0 reused)
(seg9) Hash
chain length 3.1 avg, 16 max, using 68633 of 262151 buckets.
-> Hash Join
(cost=0.00..1369.51 rows=5324 width=37)
Hash Cond:
dbo.lineitem.l_suppkey = supplier.s_suppkey
Rows out:
Avg 8707.7 rows x 18 workers. Max/Last(seg10:slave2/seg4:slave1) 8855/8666
rows with 479/475 ms to first row, 2303/2631 ms to end, start offset by
6086/5659 ms.
Executor
memory: 2113K bytes avg, 2113K bytes max (seg17:slave2).
Work_mem
used: 23K bytes avg, 23K bytes max (seg17:slave2). Workfile: (0 spilling, 0
reused)
(seg10)
Hash chain length 1.0 avg, 1 max, using 411 of 131111 buckets.
-> Table
Scan on lineitem (cost=0.00..482.40 rows=133361 width=8)
Filter: l_receiptdate > l_commitdate
Rows
out: Avg 210738.7 rows x 18 workers. Max/Last(seg6:slave2/seg2:slave1)
211597/211087 rows with 334/586 ms to first row, 2235/2538 ms to end, start
offset by 5826/5605 ms.
-> Hash
(cost=862.26..862.26 rows=400 width=33)
Rows
in: Avg 411.0 rows x 18 workers. Max/Last(seg17:slave3/seg8:slave2) 411/411
rows with 0.373/0.420 ms to end, start offset by 5451/5682 ms.
->
Broadcast Motion 18:18 (slice3; segments: 18) (cost=0.00..862.26 rows=400
width=33)
Rows out: Avg 411.0 rows x 18 workers at destination.
Max/Last(seg17:slave3/seg2:slave1) 411/411 rows with 0.027/0.023 ms to first
row, 0.254/0.299 ms to end, start offset by 5451/5604 ms.
-> Hash Join (cost=0.00..862.20 rows=23 width=33)
Hash Cond: supplier.s_nationkey = nation.n_nationkey
Rows out: Avg 22.8 rows x 18 workers. Max/Last(seg5:slave1/seg7:slave2)
31/24 rows with 1753/2117 ms to first row, 1754/2119 ms to end, start offset by
246/21 ms.
Executor memory: 4113K bytes avg, 4113K bytes max (seg17:slave1).
Work_mem used: 1K bytes avg, 1K bytes max (seg17:slave1). Workfile: (0
spilling, 0 reused)
(seg5) Hash chain length 1.0 avg, 1 max, using 1 of 262151 buckets.
-> Table Scan on supplier (cost=0.00..431.05 rows=556 width=37)
Rows out: Avg 555.6 rows x 18 workers.
Max/Last(seg3:slave1/seg13:slave3) 592/587 rows with 560/857 ms to first row,
561/858 ms to end, start offset by 1292/1285 ms.
-> Hash (cost=431.00..431.00 rows=1 width=4)
Rows in: Avg 1.0 rows x 18 workers.
Max/Last(seg17:slave3/seg12:slave3) 1/1 rows with 1095/1262 ms to end, start
offset by 193/24 ms.
-> Broadcast Motion 18:18 (slice2; segments: 18)
(cost=0.00..431.00 rows=1 width=4)
Rows out: Avg 1.0 rows x 18 workers at destination.
Max/Last(seg17:slave3/seg12:slave3) 1/1 rows with 1095/1262 ms to end, start
offset by 193/24 ms.
-> Table Scan on nation (cost=0.00..431.00 rows=1 width=4)
Filter: n_name = 'SAUDI ARABIA'::bpchar
Rows out: Avg 1.0 rows x 1 workers.
Max/Last(seg13:slave3/seg13:slave3) 1/1 rows with 1218/1218 ms to end, start
offset by 66/66 ms.
-> Hash
(cost=482.40..482.40 rows=133361 width=8)
Rows in:
Avg 210738.7 rows x 18 workers. Max/Last(seg6:slave2/seg12:slave3)
211597/209751 rows with 2747/3194 ms to end, start offset by 3077/3171 ms.
-> Table
Scan on lineitem (cost=0.00..482.40 rows=133361 width=8)
Filter: l_receiptdate > l_commitdate
Rows
out: Avg 210738.7 rows x 18 workers. Max/Last(seg6:slave2/seg12:slave3)
211597/209751 rows with 375/516 ms to first row, 2504/2885 ms to end, start
offset by 3077/3171 ms.
-> Hash
(cost=456.49..456.49 rows=333401 width=8)
Rows in: Avg
333400.8 rows x 18 workers. Max/Last(seg8:slave2/seg3:slave1) 335120/333946
rows with 2858/3426 ms to end, start offset by 93/269 ms.
-> Table Scan on
lineitem (cost=0.00..456.49 rows=333401 width=8)
Rows out:
Avg 333400.8 rows x 18 workers. Max/Last(seg8:slave2/seg5:slave1)
335120/333720 rows with 1022/1346 ms to first row, 2442/3039 ms to end, start
offset by 93/353 ms.
Slice statistics:
(slice0) Executor memory: 759K bytes.
(slice1) Executor memory: 2009K bytes avg x 18 workers, 2011K bytes max
(seg7:slave2).
(slice2) Executor memory: 523K bytes avg x 18 workers, 523K bytes max
(seg17:slave3).
(slice3) Executor memory: 4664K bytes avg x 18 workers, 4666K bytes max
(seg3:slave1). Work_mem: 1K bytes max.
(slice4) Executor memory: 45288K bytes avg x 18 workers, 45289K bytes max
(seg0:slave1). Work_mem: 10473K bytes max.
(slice5) Executor memory: 6938K bytes avg x 18 workers, 6938K bytes max
(seg17:slave3). Work_mem: 28K bytes max.
(slice6) Executor memory: 4599K bytes avg x 18 workers, 4599K bytes max
(seg17:slave3). Work_mem: 58K bytes max.
Statement statistics:
Memory used: 262144K bytes
Settings: default_hash_table_bucket_number=18
Optimizer status: PQO version 1.627
Dispatcher statistics:
executors used(total/cached/new connection): (108/108/0); dispatcher
time(total/connection/dispatch data): (2.685 ms/0.000 ms/1.983 ms).
dispatch data time(max/min/avg): (0.103 ms/0.015 ms/0.026 ms); consume
executor data time(max/min/avg): (0.041 ms/0.008 ms/0.022 ms); free executor
time(max/min/avg): (0.000 ms/0.000 ms/0.000 ms).
Data locality statistics:
data locality ratio: 1.000; virtual segment number: 18; different host
number: 3; virtual segment number per host(avg/min/max): (6/6/6); segment
size(avg/min/max): (21012431.667 B/20917791 B/21111680 B); segment size with
penalty(avg/min/max): (0.000 B/0 B/0 B); continuity(avg/min/max):
(1.000/1.000/1.000); DFS metadatacache: 0.713 ms; resource allocation: 0.796
ms; datalocality calculation: 0.246 ms.
Total runtime: 8641.214 ms