Google Git
Sign in
apache / impala / ad43c873fa5d823a56ba93315a8e76e7cd6a0685 / . / testdata / workloads / functional-planner / queries / PlannerTest / tpch-all.test
blob: ee15c381b0896b680a239d7a0e487c57a5f02cc4 [file] [log] [blame]
# TPCH-Q1
# Q1 - Pricing Summary Report Query
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= '1998-09-02'
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus
---- PLAN
Max Per-Host Resource Reservation: Memory=13.94MB Threads=2
Per-Host Resource Estimates: Memory=274MB
PLAN-ROOT SINK
|
02:SORT
| order by: l_returnflag ASC, l_linestatus ASC
| row-size=122B cardinality=6
|
01:AGGREGATE [FINALIZE]
| output: sum(l_quantity), sum(l_extendedprice), sum(l_extendedprice * (1 - l_discount)), sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)), avg(l_quantity), avg(l_extendedprice), avg(l_discount), count(*)
| group by: l_returnflag, l_linestatus
| row-size=122B cardinality=6
|
00:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate <= '1998-09-02'
row-size=80B cardinality=600.12K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=23.94MB Threads=4
Per-Host Resource Estimates: Memory=296MB
PLAN-ROOT SINK
|
05:MERGING-EXCHANGE [UNPARTITIONED]
| order by: l_returnflag ASC, l_linestatus ASC
|
02:SORT
| order by: l_returnflag ASC, l_linestatus ASC
| row-size=122B cardinality=6
|
04:AGGREGATE [FINALIZE]
| output: sum:merge(l_quantity), sum:merge(l_extendedprice), sum:merge(l_extendedprice * (1 - l_discount)), sum:merge(l_extendedprice * (1 - l_discount) * (1 + l_tax)), avg:merge(l_quantity), avg:merge(l_extendedprice), avg:merge(l_discount), count:merge(*)
| group by: l_returnflag, l_linestatus
| row-size=122B cardinality=6
|
03:EXCHANGE [HASH(l_returnflag,l_linestatus)]
|
01:AGGREGATE [STREAMING]
| output: sum(l_quantity), sum(l_extendedprice), sum(l_extendedprice * (1 - l_discount)), sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)), avg(l_quantity), avg(l_extendedprice), avg(l_discount), count(*)
| group by: l_returnflag, l_linestatus
| row-size=122B cardinality=6
|
00:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate <= '1998-09-02'
row-size=80B cardinality=600.12K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=47.88MB Threads=5
Per-Host Resource Estimates: Memory=240MB
PLAN-ROOT SINK
|
05:MERGING-EXCHANGE [UNPARTITIONED]
| order by: l_returnflag ASC, l_linestatus ASC
|
02:SORT
| order by: l_returnflag ASC, l_linestatus ASC
| row-size=122B cardinality=6
|
04:AGGREGATE [FINALIZE]
| output: sum:merge(l_quantity), sum:merge(l_extendedprice), sum:merge(l_extendedprice * (1 - l_discount)), sum:merge(l_extendedprice * (1 - l_discount) * (1 + l_tax)), avg:merge(l_quantity), avg:merge(l_extendedprice), avg:merge(l_discount), count:merge(*)
| group by: l_returnflag, l_linestatus
| row-size=122B cardinality=6
|
03:EXCHANGE [HASH(l_returnflag,l_linestatus)]
|
01:AGGREGATE [STREAMING]
| output: sum(l_quantity), sum(l_extendedprice), sum(l_extendedprice * (1 - l_discount)), sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)), avg(l_quantity), avg(l_extendedprice), avg(l_discount), count(*)
| group by: l_returnflag, l_linestatus
| row-size=122B cardinality=6
|
00:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate <= '1998-09-02'
row-size=80B cardinality=600.12K
====
# TPCH-Q2
# Q2 - Minimum Cost Supplier Query
select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
part,
supplier,
partsupp,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 15
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp,
supplier,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
limit 100
---- PLAN
Max Per-Host Resource Reservation: Memory=50.39MB Threads=10
Per-Host Resource Estimates: Memory=628MB
PLAN-ROOT SINK
|
18:TOP-N [LIMIT=100]
| order by: s_acctbal DESC, n_name ASC, s_name ASC, p_partkey ASC
| row-size=230B cardinality=100
|
17:HASH JOIN [RIGHT SEMI JOIN]
| hash predicates: ps_partkey = p_partkey, min(ps_supplycost) = ps_supplycost
| runtime filters: RF000 <- p_partkey
| row-size=325B cardinality=1.01K
|
|--16:HASH JOIN [INNER JOIN]
| | hash predicates: n_regionkey = r_regionkey
| | runtime filters: RF010 <- r_regionkey
| | row-size=325B cardinality=1.01K
| |
| |--04:SCAN HDFS [tpch.region]
| | HDFS partitions=1/1 files=1 size=384B
| | predicates: r_name = 'EUROPE'
| | row-size=21B cardinality=1
| |
| 15:HASH JOIN [INNER JOIN]
| | hash predicates: s_nationkey = n_nationkey
| | runtime filters: RF012 <- n_nationkey
| | row-size=304B cardinality=5.05K
| |
| |--03:SCAN HDFS [tpch.nation]
| | HDFS partitions=1/1 files=1 size=2.15KB
| | runtime filters: RF010 -> n_regionkey
| | row-size=23B cardinality=25
| |
| 14:HASH JOIN [INNER JOIN]
| | hash predicates: s_suppkey = ps_suppkey
| | runtime filters: RF014 <- ps_suppkey
| | row-size=281B cardinality=5.05K
| |
| |--13:HASH JOIN [INNER JOIN]
| | | hash predicates: ps_partkey = p_partkey
| | | runtime filters: RF016 <- p_partkey
| | | row-size=95B cardinality=5.05K
| | |
| | |--00:SCAN HDFS [tpch.part]
| | | HDFS partitions=1/1 files=1 size=22.83MB
| | | predicates: p_size = 15, p_type LIKE '%BRASS'
| | | row-size=71B cardinality=1.26K
| | |
| | 02:SCAN HDFS [tpch.partsupp]
| | HDFS partitions=1/1 files=1 size=112.71MB
| | runtime filters: RF016 -> ps_partkey
| | row-size=24B cardinality=800.00K
| |
| 01:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF012 -> s_nationkey, RF014 -> s_suppkey
| row-size=187B cardinality=10.00K
|
12:AGGREGATE [FINALIZE]
| output: min(ps_supplycost)
| group by: ps_partkey
| row-size=16B cardinality=160.00K
|
11:HASH JOIN [INNER JOIN]
| hash predicates: n_regionkey = r_regionkey
| runtime filters: RF004 <- r_regionkey
| row-size=59B cardinality=160.00K
|
|--08:SCAN HDFS [tpch.region]
| HDFS partitions=1/1 files=1 size=384B
| predicates: r_name = 'EUROPE'
| row-size=21B cardinality=1
|
10:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
| runtime filters: RF006 <- n_nationkey
| row-size=38B cardinality=800.00K
|
|--07:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| runtime filters: RF004 -> n_regionkey
| row-size=4B cardinality=25
|
09:HASH JOIN [INNER JOIN]
| hash predicates: ps_suppkey = s_suppkey
| runtime filters: RF008 <- s_suppkey
| row-size=34B cardinality=800.00K
|
|--06:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF006 -> s_nationkey
| row-size=10B cardinality=10.00K
|
05:SCAN HDFS [tpch.partsupp]
HDFS partitions=1/1 files=1 size=112.71MB
runtime filters: RF000 -> tpch.partsupp.ps_partkey, RF008 -> ps_suppkey
row-size=24B cardinality=800.00K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=65.28MB Threads=21
Per-Host Resource Estimates: Memory=662MB
PLAN-ROOT SINK
|
30:MERGING-EXCHANGE [UNPARTITIONED]
| order by: s_acctbal DESC, n_name ASC, s_name ASC, p_partkey ASC
| limit: 100
|
18:TOP-N [LIMIT=100]
| order by: s_acctbal DESC, n_name ASC, s_name ASC, p_partkey ASC
| row-size=230B cardinality=100
|
17:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED]
| hash predicates: ps_partkey = p_partkey, min(ps_supplycost) = ps_supplycost
| runtime filters: RF000 <- p_partkey
| row-size=325B cardinality=1.01K
|
|--29:EXCHANGE [HASH(p_partkey,ps_supplycost)]
| |
| 16:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: n_regionkey = r_regionkey
| | runtime filters: RF010 <- r_regionkey
| | row-size=325B cardinality=1.01K
| |
| |--27:EXCHANGE [BROADCAST]
| | |
| | 04:SCAN HDFS [tpch.region]
| | HDFS partitions=1/1 files=1 size=384B
| | predicates: r_name = 'EUROPE'
| | row-size=21B cardinality=1
| |
| 15:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: s_nationkey = n_nationkey
| | runtime filters: RF012 <- n_nationkey
| | row-size=304B cardinality=5.05K
| |
| |--26:EXCHANGE [BROADCAST]
| | |
| | 03:SCAN HDFS [tpch.nation]
| | HDFS partitions=1/1 files=1 size=2.15KB
| | runtime filters: RF010 -> n_regionkey
| | row-size=23B cardinality=25
| |
| 14:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: s_suppkey = ps_suppkey
| | runtime filters: RF014 <- ps_suppkey
| | row-size=281B cardinality=5.05K
| |
| |--25:EXCHANGE [BROADCAST]
| | |
| | 13:HASH JOIN [INNER JOIN, BROADCAST]
| | | hash predicates: ps_partkey = p_partkey
| | | runtime filters: RF016 <- p_partkey
| | | row-size=95B cardinality=5.05K
| | |
| | |--24:EXCHANGE [BROADCAST]
| | | |
| | | 00:SCAN HDFS [tpch.part]
| | | HDFS partitions=1/1 files=1 size=22.83MB
| | | predicates: p_size = 15, p_type LIKE '%BRASS'
| | | row-size=71B cardinality=1.26K
| | |
| | 02:SCAN HDFS [tpch.partsupp]
| | HDFS partitions=1/1 files=1 size=112.71MB
| | runtime filters: RF016 -> ps_partkey
| | row-size=24B cardinality=800.00K
| |
| 01:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF012 -> s_nationkey, RF014 -> s_suppkey
| row-size=187B cardinality=10.00K
|
28:EXCHANGE [HASH(ps_partkey,min(ps_supplycost))]
|
23:AGGREGATE [FINALIZE]
| output: min:merge(ps_supplycost)
| group by: ps_partkey
| row-size=16B cardinality=160.00K
|
22:EXCHANGE [HASH(ps_partkey)]
|
12:AGGREGATE [STREAMING]
| output: min(ps_supplycost)
| group by: ps_partkey
| row-size=16B cardinality=160.00K
|
11:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n_regionkey = r_regionkey
| runtime filters: RF004 <- r_regionkey
| row-size=59B cardinality=160.00K
|
|--21:EXCHANGE [BROADCAST]
| |
| 08:SCAN HDFS [tpch.region]
| HDFS partitions=1/1 files=1 size=384B
| predicates: r_name = 'EUROPE'
| row-size=21B cardinality=1
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
| runtime filters: RF006 <- n_nationkey
| row-size=38B cardinality=800.00K
|
|--20:EXCHANGE [BROADCAST]
| |
| 07:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| runtime filters: RF004 -> n_regionkey
| row-size=4B cardinality=25
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ps_suppkey = s_suppkey
| runtime filters: RF008 <- s_suppkey
| row-size=34B cardinality=800.00K
|
|--19:EXCHANGE [BROADCAST]
| |
| 06:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF006 -> s_nationkey
| row-size=10B cardinality=10.00K
|
05:SCAN HDFS [tpch.partsupp]
HDFS partitions=1/1 files=1 size=112.71MB
runtime filters: RF000 -> tpch.partsupp.ps_partkey, RF008 -> ps_suppkey
row-size=24B cardinality=800.00K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=122.81MB Threads=23
Per-Host Resource Estimates: Memory=713MB
PLAN-ROOT SINK
|
30:MERGING-EXCHANGE [UNPARTITIONED]
| order by: s_acctbal DESC, n_name ASC, s_name ASC, p_partkey ASC
| limit: 100
|
18:TOP-N [LIMIT=100]
| order by: s_acctbal DESC, n_name ASC, s_name ASC, p_partkey ASC
| row-size=230B cardinality=100
|
17:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED]
| hash predicates: ps_partkey = p_partkey, min(ps_supplycost) = ps_supplycost
| runtime filters: RF000 <- p_partkey
| row-size=325B cardinality=1.01K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: p_partkey, ps_supplycost
| |
| 29:EXCHANGE [HASH(p_partkey,ps_supplycost)]
| |
| 16:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: n_regionkey = r_regionkey
| | runtime filters: RF010 <- r_regionkey
| | row-size=325B cardinality=1.01K
| |
| |--JOIN BUILD
| | | join-table-id=01 plan-id=02 cohort-id=02
| | | build expressions: r_regionkey
| | |
| | 27:EXCHANGE [BROADCAST]
| | |
| | 04:SCAN HDFS [tpch.region]
| | HDFS partitions=1/1 files=1 size=384B
| | predicates: r_name = 'EUROPE'
| | row-size=21B cardinality=1
| |
| 15:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: s_nationkey = n_nationkey
| | runtime filters: RF012 <- n_nationkey
| | row-size=304B cardinality=5.05K
| |
| |--JOIN BUILD
| | | join-table-id=02 plan-id=03 cohort-id=02
| | | build expressions: n_nationkey
| | |
| | 26:EXCHANGE [BROADCAST]
| | |
| | 03:SCAN HDFS [tpch.nation]
| | HDFS partitions=1/1 files=1 size=2.15KB
| | runtime filters: RF010 -> n_regionkey
| | row-size=23B cardinality=25
| |
| 14:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: s_suppkey = ps_suppkey
| | runtime filters: RF014 <- ps_suppkey
| | row-size=281B cardinality=5.05K
| |
| |--JOIN BUILD
| | | join-table-id=03 plan-id=04 cohort-id=02
| | | build expressions: ps_suppkey
| | |
| | 25:EXCHANGE [BROADCAST]
| | |
| | 13:HASH JOIN [INNER JOIN, BROADCAST]
| | | hash predicates: ps_partkey = p_partkey
| | | runtime filters: RF016 <- p_partkey
| | | row-size=95B cardinality=5.05K
| | |
| | |--JOIN BUILD
| | | | join-table-id=04 plan-id=05 cohort-id=03
| | | | build expressions: p_partkey
| | | |
| | | 24:EXCHANGE [BROADCAST]
| | | |
| | | 00:SCAN HDFS [tpch.part]
| | | HDFS partitions=1/1 files=1 size=22.83MB
| | | predicates: p_size = 15, p_type LIKE '%BRASS'
| | | row-size=71B cardinality=1.26K
| | |
| | 02:SCAN HDFS [tpch.partsupp]
| | HDFS partitions=1/1 files=1 size=112.71MB
| | runtime filters: RF016 -> ps_partkey
| | row-size=24B cardinality=800.00K
| |
| 01:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF012 -> s_nationkey, RF014 -> s_suppkey
| row-size=187B cardinality=10.00K
|
28:EXCHANGE [HASH(ps_partkey,min(ps_supplycost))]
|
23:AGGREGATE [FINALIZE]
| output: min:merge(ps_supplycost)
| group by: ps_partkey
| row-size=16B cardinality=160.00K
|
22:EXCHANGE [HASH(ps_partkey)]
|
12:AGGREGATE [STREAMING]
| output: min(ps_supplycost)
| group by: ps_partkey
| row-size=16B cardinality=160.00K
|
11:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n_regionkey = r_regionkey
| runtime filters: RF004 <- r_regionkey
| row-size=59B cardinality=160.00K
|
|--JOIN BUILD
| | join-table-id=05 plan-id=06 cohort-id=01
| | build expressions: r_regionkey
| |
| 21:EXCHANGE [BROADCAST]
| |
| 08:SCAN HDFS [tpch.region]
| HDFS partitions=1/1 files=1 size=384B
| predicates: r_name = 'EUROPE'
| row-size=21B cardinality=1
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
| runtime filters: RF006 <- n_nationkey
| row-size=38B cardinality=800.00K
|
|--JOIN BUILD
| | join-table-id=06 plan-id=07 cohort-id=01
| | build expressions: n_nationkey
| |
| 20:EXCHANGE [BROADCAST]
| |
| 07:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| runtime filters: RF004 -> n_regionkey
| row-size=4B cardinality=25
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ps_suppkey = s_suppkey
| runtime filters: RF008 <- s_suppkey
| row-size=34B cardinality=800.00K
|
|--JOIN BUILD
| | join-table-id=07 plan-id=08 cohort-id=01
| | build expressions: s_suppkey
| |
| 19:EXCHANGE [BROADCAST]
| |
| 06:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF006 -> s_nationkey
| row-size=10B cardinality=10.00K
|
05:SCAN HDFS [tpch.partsupp]
HDFS partitions=1/1 files=1 size=112.71MB
runtime filters: RF000 -> tpch.partsupp.ps_partkey, RF008 -> ps_suppkey
row-size=24B cardinality=800.00K
====
# TPCH-Q3
# Q3 - Shipping Priority Query
select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders,
lineitem
where
c_mktsegment = 'BUILDING'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < '1995-03-15'
and l_shipdate > '1995-03-15'
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
limit 10
---- PLAN
Max Per-Host Resource Reservation: Memory=36.44MB Threads=4
Per-Host Resource Estimates: Memory=516MB
PLAN-ROOT SINK
|
06:TOP-N [LIMIT=10]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC, o_orderdate ASC
| row-size=50B cardinality=10
|
05:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: l_orderkey, o_orderdate, o_shippriority
| row-size=50B cardinality=17.56K
|
04:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF000 <- c_custkey
| row-size=117B cardinality=17.56K
|
|--00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| predicates: c_mktsegment = 'BUILDING'
| row-size=29B cardinality=30.00K
|
03:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF002 <- o_orderkey
| row-size=88B cardinality=57.58K
|
|--01:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate < '1995-03-15'
| runtime filters: RF000 -> o_custkey
| row-size=42B cardinality=150.00K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate > '1995-03-15'
runtime filters: RF002 -> l_orderkey
row-size=46B cardinality=600.12K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=41.38MB Threads=8
Per-Host Resource Estimates: Memory=545MB
PLAN-ROOT SINK
|
11:MERGING-EXCHANGE [UNPARTITIONED]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC, o_orderdate ASC
| limit: 10
|
06:TOP-N [LIMIT=10]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC, o_orderdate ASC
| row-size=50B cardinality=10
|
10:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: l_orderkey, o_orderdate, o_shippriority
| row-size=50B cardinality=17.56K
|
09:EXCHANGE [HASH(l_orderkey,o_orderdate,o_shippriority)]
|
05:AGGREGATE [STREAMING]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: l_orderkey, o_orderdate, o_shippriority
| row-size=50B cardinality=17.56K
|
04:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF000 <- c_custkey
| row-size=117B cardinality=17.56K
|
|--08:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| predicates: c_mktsegment = 'BUILDING'
| row-size=29B cardinality=30.00K
|
03:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF002 <- o_orderkey
| row-size=88B cardinality=57.58K
|
|--07:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate < '1995-03-15'
| runtime filters: RF000 -> o_custkey
| row-size=42B cardinality=150.00K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate > '1995-03-15'
runtime filters: RF002 -> l_orderkey
row-size=46B cardinality=600.12K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=73.50MB Threads=13
Per-Host Resource Estimates: Memory=497MB
PLAN-ROOT SINK
|
13:MERGING-EXCHANGE [UNPARTITIONED]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC, o_orderdate ASC
| limit: 10
|
06:TOP-N [LIMIT=10]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC, o_orderdate ASC
| row-size=50B cardinality=10
|
12:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: l_orderkey, o_orderdate, o_shippriority
| row-size=50B cardinality=17.56K
|
11:EXCHANGE [HASH(l_orderkey,o_orderdate,o_shippriority)]
|
05:AGGREGATE [STREAMING]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: l_orderkey, o_orderdate, o_shippriority
| row-size=50B cardinality=17.56K
|
04:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF000 <- c_custkey
| row-size=117B cardinality=17.56K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: c_custkey
| |
| 10:EXCHANGE [HASH(c_custkey)]
| |
| 00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| predicates: c_mktsegment = 'BUILDING'
| row-size=29B cardinality=30.00K
|
09:EXCHANGE [HASH(o_custkey)]
|
03:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF002 <- o_orderkey
| row-size=88B cardinality=57.58K
|
|--JOIN BUILD
| | join-table-id=01 plan-id=02 cohort-id=01
| | build expressions: o_orderkey
| |
| 08:EXCHANGE [HASH(o_orderkey)]
| |
| 01:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate < '1995-03-15'
| runtime filters: RF000 -> o_custkey
| row-size=42B cardinality=150.00K
|
07:EXCHANGE [HASH(l_orderkey)]
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate > '1995-03-15'
runtime filters: RF002 -> l_orderkey
row-size=46B cardinality=600.12K
====
# TPCH-Q4
# Q4 - Order Priority Checking Query
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= '1993-07-01'
and o_orderdate < '1993-10-01'
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority
---- PLAN
Max Per-Host Resource Reservation: Memory=25.50MB Threads=3
Per-Host Resource Estimates: Memory=450MB
PLAN-ROOT SINK
|
04:SORT
| order by: o_orderpriority ASC
| row-size=28B cardinality=5
|
03:AGGREGATE [FINALIZE]
| output: count(*)
| group by: o_orderpriority
| row-size=28B cardinality=5
|
02:HASH JOIN [RIGHT SEMI JOIN]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF000 <- o_orderkey
| row-size=50B cardinality=150.00K
|
|--00:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate < '1993-10-01', o_orderdate >= '1993-07-01'
| row-size=50B cardinality=150.00K
|
01:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_commitdate < l_receiptdate
runtime filters: RF000 -> l_orderkey
row-size=52B cardinality=600.12K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=38.69MB Threads=7
Per-Host Resource Estimates: Memory=493MB
PLAN-ROOT SINK
|
09:MERGING-EXCHANGE [UNPARTITIONED]
| order by: o_orderpriority ASC
|
04:SORT
| order by: o_orderpriority ASC
| row-size=28B cardinality=5
|
08:AGGREGATE [FINALIZE]
| output: count:merge(*)
| group by: o_orderpriority
| row-size=28B cardinality=5
|
07:EXCHANGE [HASH(o_orderpriority)]
|
03:AGGREGATE [STREAMING]
| output: count(*)
| group by: o_orderpriority
| row-size=28B cardinality=5
|
02:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF000 <- o_orderkey
| row-size=50B cardinality=150.00K
|
|--06:EXCHANGE [HASH(o_orderkey)]
| |
| 00:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate < '1993-10-01', o_orderdate >= '1993-07-01'
| row-size=50B cardinality=150.00K
|
05:EXCHANGE [HASH(l_orderkey)]
|
01:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_commitdate < l_receiptdate
runtime filters: RF000 -> l_orderkey
row-size=52B cardinality=600.12K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=73.62MB Threads=9
Per-Host Resource Estimates: Memory=446MB
PLAN-ROOT SINK
|
09:MERGING-EXCHANGE [UNPARTITIONED]
| order by: o_orderpriority ASC
|
04:SORT
| order by: o_orderpriority ASC
| row-size=28B cardinality=5
|
08:AGGREGATE [FINALIZE]
| output: count:merge(*)
| group by: o_orderpriority
| row-size=28B cardinality=5
|
07:EXCHANGE [HASH(o_orderpriority)]
|
03:AGGREGATE [STREAMING]
| output: count(*)
| group by: o_orderpriority
| row-size=28B cardinality=5
|
02:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF000 <- o_orderkey
| row-size=50B cardinality=150.00K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: o_orderkey
| |
| 06:EXCHANGE [HASH(o_orderkey)]
| |
| 00:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate < '1993-10-01', o_orderdate >= '1993-07-01'
| row-size=50B cardinality=150.00K
|
05:EXCHANGE [HASH(l_orderkey)]
|
01:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_commitdate < l_receiptdate
runtime filters: RF000 -> l_orderkey
row-size=52B cardinality=600.12K
====
# TPCH-Q5
# Q5 - Local Supplier Volume Query
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
customer,
orders,
lineitem,
supplier,
nation,
region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= '1994-01-01'
and o_orderdate < '1995-01-01'
group by
n_name
order by
revenue desc
---- PLAN
Max Per-Host Resource Reservation: Memory=49.20MB Threads=7
Per-Host Resource Estimates: Memory=623MB
PLAN-ROOT SINK
|
12:SORT
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
| row-size=35B cardinality=25
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
| row-size=35B cardinality=25
|
10:HASH JOIN [INNER JOIN]
| hash predicates: n_regionkey = r_regionkey
| runtime filters: RF000 <- r_regionkey
| row-size=134B cardinality=115.16K
|
|--05:SCAN HDFS [tpch.region]
| HDFS partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
| row-size=21B cardinality=1
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
| runtime filters: RF002 <- n_nationkey
| row-size=113B cardinality=575.77K
|
|--04:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| runtime filters: RF000 -> n_regionkey
| row-size=23B cardinality=25
|
08:HASH JOIN [INNER JOIN]
| hash predicates: c_nationkey = s_nationkey, l_suppkey = s_suppkey
| runtime filters: RF004 <- s_nationkey, RF005 <- s_suppkey
| row-size=90B cardinality=575.77K
|
|--03:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF002 -> s_nationkey
| row-size=10B cardinality=10.00K
|
07:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF008 <- c_custkey
| row-size=80B cardinality=575.77K
|
|--00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| runtime filters: RF002 -> tpch.customer.c_nationkey, RF004 -> c_nationkey
| row-size=10B cardinality=150.00K
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF010 <- o_orderkey
| row-size=70B cardinality=575.77K
|
|--01:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate < '1995-01-01', o_orderdate >= '1994-01-01'
| runtime filters: RF008 -> o_custkey
| row-size=38B cardinality=150.00K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
runtime filters: RF005 -> l_suppkey, RF010 -> l_orderkey
row-size=32B cardinality=6.00M
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=70.14MB Threads=14
Per-Host Resource Estimates: Memory=667MB
PLAN-ROOT SINK
|
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
|
12:SORT
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
| row-size=35B cardinality=25
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: n_name
| row-size=35B cardinality=25
|
18:EXCHANGE [HASH(n_name)]
|
11:AGGREGATE [STREAMING]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
| row-size=35B cardinality=25
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n_regionkey = r_regionkey
| runtime filters: RF000 <- r_regionkey
| row-size=134B cardinality=115.16K
|
|--17:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.region]
| HDFS partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
| row-size=21B cardinality=1
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
| runtime filters: RF002 <- n_nationkey
| row-size=113B cardinality=575.77K
|
|--16:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| runtime filters: RF000 -> n_regionkey
| row-size=23B cardinality=25
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = s_nationkey, l_suppkey = s_suppkey
| runtime filters: RF004 <- s_nationkey, RF005 <- s_suppkey
| row-size=90B cardinality=575.77K
|
|--15:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF002 -> s_nationkey
| row-size=10B cardinality=10.00K
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF008 <- c_custkey
| row-size=80B cardinality=575.77K
|
|--14:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| runtime filters: RF002 -> tpch.customer.c_nationkey, RF004 -> c_nationkey
| row-size=10B cardinality=150.00K
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF010 <- o_orderkey
| row-size=70B cardinality=575.77K
|
|--13:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate < '1995-01-01', o_orderdate >= '1994-01-01'
| runtime filters: RF008 -> o_custkey
| row-size=38B cardinality=150.00K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
runtime filters: RF005 -> l_suppkey, RF010 -> l_orderkey
row-size=32B cardinality=6.00M
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=140.28MB Threads=15
Per-Host Resource Estimates: Memory=632MB
PLAN-ROOT SINK
|
20:MERGING-EXCHANGE [UNPARTITIONED]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
|
12:SORT
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
| row-size=35B cardinality=25
|
19:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: n_name
| row-size=35B cardinality=25
|
18:EXCHANGE [HASH(n_name)]
|
11:AGGREGATE [STREAMING]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n_name
| row-size=35B cardinality=25
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n_regionkey = r_regionkey
| runtime filters: RF000 <- r_regionkey
| row-size=134B cardinality=115.16K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: r_regionkey
| |
| 17:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.region]
| HDFS partitions=1/1 files=1 size=384B
| predicates: r_name = 'ASIA'
| row-size=21B cardinality=1
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
| runtime filters: RF002 <- n_nationkey
| row-size=113B cardinality=575.77K
|
|--JOIN BUILD
| | join-table-id=01 plan-id=02 cohort-id=01
| | build expressions: n_nationkey
| |
| 16:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| runtime filters: RF000 -> n_regionkey
| row-size=23B cardinality=25
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = s_nationkey, l_suppkey = s_suppkey
| runtime filters: RF004 <- s_nationkey, RF005 <- s_suppkey
| row-size=90B cardinality=575.77K
|
|--JOIN BUILD
| | join-table-id=02 plan-id=03 cohort-id=01
| | build expressions: s_nationkey, s_suppkey
| |
| 15:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF002 -> s_nationkey
| row-size=10B cardinality=10.00K
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF008 <- c_custkey
| row-size=80B cardinality=575.77K
|
|--JOIN BUILD
| | join-table-id=03 plan-id=04 cohort-id=01
| | build expressions: c_custkey
| |
| 14:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| runtime filters: RF002 -> tpch.customer.c_nationkey, RF004 -> c_nationkey
| row-size=10B cardinality=150.00K
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF010 <- o_orderkey
| row-size=70B cardinality=575.77K
|
|--JOIN BUILD
| | join-table-id=04 plan-id=05 cohort-id=01
| | build expressions: o_orderkey
| |
| 13:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate < '1995-01-01', o_orderdate >= '1994-01-01'
| runtime filters: RF008 -> o_custkey
| row-size=38B cardinality=150.00K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
runtime filters: RF005 -> l_suppkey, RF010 -> l_orderkey
row-size=32B cardinality=6.00M
====
# TPCH-Q6
# Q6 - Forecasting Revenue Change Query
select
sum(l_extendedprice * l_discount) as revenue
from
lineitem
where
l_shipdate >= '1994-01-01'
and l_shipdate < '1995-01-01'
and l_discount between 0.05 and 0.07
and l_quantity < 24
---- PLAN
Max Per-Host Resource Reservation: Memory=8.00MB Threads=2
Per-Host Resource Estimates: Memory=274MB
PLAN-ROOT SINK
|
01:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * l_discount)
| row-size=16B cardinality=1
|
00:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_discount <= 0.07, l_discount >= 0.05, l_quantity < 24, l_shipdate < '1995-01-01', l_shipdate >= '1994-01-01'
row-size=46B cardinality=600.12K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=8.00MB Threads=3
Per-Host Resource Estimates: Memory=284MB
PLAN-ROOT SINK
|
03:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * l_discount)
| row-size=16B cardinality=1
|
02:EXCHANGE [UNPARTITIONED]
|
01:AGGREGATE
| output: sum(l_extendedprice * l_discount)
| row-size=16B cardinality=1
|
00:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_discount <= 0.07, l_discount >= 0.05, l_quantity < 24, l_shipdate < '1995-01-01', l_shipdate >= '1994-01-01'
row-size=46B cardinality=600.12K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=16.00MB Threads=3
Per-Host Resource Estimates: Memory=206MB
PLAN-ROOT SINK
|
03:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * l_discount)
| row-size=16B cardinality=1
|
02:EXCHANGE [UNPARTITIONED]
|
01:AGGREGATE
| output: sum(l_extendedprice * l_discount)
| row-size=16B cardinality=1
|
00:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_discount <= 0.07, l_discount >= 0.05, l_quantity < 24, l_shipdate < '1995-01-01', l_shipdate >= '1994-01-01'
row-size=46B cardinality=600.12K
====
# TPCH-Q7
# Q7 - Volume Shipping Query
select
supp_nation,
cust_nation,
l_year,
sum(volume) as revenue
from (
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
year(l_shipdate) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
)
and l_shipdate between '1995-01-01' and '1996-12-31'
) as shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year
---- PLAN
Max Per-Host Resource Reservation: Memory=89.69MB Threads=7
Per-Host Resource Estimates: Memory=648MB
PLAN-ROOT SINK
|
12:SORT
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
| row-size=58B cardinality=575.77K
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n1.n_name, n2.n_name, year(l_shipdate)
| row-size=58B cardinality=575.77K
|
10:HASH JOIN [INNER JOIN]
| hash predicates: c_nationkey = n2.n_nationkey
| other predicates: ((n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY') OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'))
| runtime filters: RF000 <- n2.n_nationkey
| row-size=132B cardinality=575.77K
|
|--05:SCAN HDFS [tpch.nation n2]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
09:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n1.n_nationkey
| runtime filters: RF002 <- n1.n_nationkey
| row-size=111B cardinality=575.77K
|
|--04:SCAN HDFS [tpch.nation n1]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
08:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF004 <- c_custkey
| row-size=90B cardinality=575.77K
|
|--03:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| runtime filters: RF000 -> c_nationkey
| row-size=10B cardinality=150.00K
|
07:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey
| runtime filters: RF006 <- s_suppkey
| row-size=80B cardinality=575.77K
|
|--00:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF002 -> s_nationkey
| row-size=10B cardinality=10.00K
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF008 <- o_orderkey
| row-size=70B cardinality=575.77K
|
|--02:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| runtime filters: RF004 -> o_custkey
| row-size=16B cardinality=1.50M
|
01:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate <= '1996-12-31', l_shipdate >= '1995-01-01'
runtime filters: RF006 -> l_suppkey, RF008 -> l_orderkey
row-size=54B cardinality=600.12K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=86.95MB Threads=15
Per-Host Resource Estimates: Memory=699MB
PLAN-ROOT SINK
|
21:MERGING-EXCHANGE [UNPARTITIONED]
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
|
12:SORT
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
| row-size=58B cardinality=575.77K
|
20:AGGREGATE [FINALIZE]
| output: sum:merge(volume)
| group by: supp_nation, cust_nation, l_year
| row-size=58B cardinality=575.77K
|
19:EXCHANGE [HASH(supp_nation,cust_nation,l_year)]
|
11:AGGREGATE [STREAMING]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n1.n_name, n2.n_name, year(l_shipdate)
| row-size=58B cardinality=575.77K
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = n2.n_nationkey
| other predicates: ((n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY') OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'))
| runtime filters: RF000 <- n2.n_nationkey
| row-size=132B cardinality=575.77K
|
|--18:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation n2]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n1.n_nationkey
| runtime filters: RF002 <- n1.n_nationkey
| row-size=111B cardinality=575.77K
|
|--17:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation n1]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF004 <- c_custkey
| row-size=90B cardinality=575.77K
|
|--16:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| runtime filters: RF000 -> c_nationkey
| row-size=10B cardinality=150.00K
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
| runtime filters: RF006 <- s_suppkey
| row-size=80B cardinality=575.77K
|
|--15:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF002 -> s_nationkey
| row-size=10B cardinality=10.00K
|
06:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF008 <- o_orderkey
| row-size=70B cardinality=575.77K
|
|--14:EXCHANGE [HASH(o_orderkey)]
| |
| 02:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| runtime filters: RF004 -> o_custkey
| row-size=16B cardinality=1.50M
|
13:EXCHANGE [HASH(l_orderkey)]
|
01:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate <= '1996-12-31', l_shipdate >= '1995-01-01'
runtime filters: RF006 -> l_suppkey, RF008 -> l_orderkey
row-size=54B cardinality=600.12K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=144.78MB Threads=17
Per-Host Resource Estimates: Memory=655MB
PLAN-ROOT SINK
|
21:MERGING-EXCHANGE [UNPARTITIONED]
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
|
12:SORT
| order by: supp_nation ASC, cust_nation ASC, l_year ASC
| row-size=58B cardinality=575.77K
|
20:AGGREGATE [FINALIZE]
| output: sum:merge(volume)
| group by: supp_nation, cust_nation, l_year
| row-size=58B cardinality=575.77K
|
19:EXCHANGE [HASH(supp_nation,cust_nation,l_year)]
|
11:AGGREGATE [STREAMING]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: n1.n_name, n2.n_name, year(l_shipdate)
| row-size=58B cardinality=575.77K
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = n2.n_nationkey
| other predicates: ((n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY') OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'))
| runtime filters: RF000 <- n2.n_nationkey
| row-size=132B cardinality=575.77K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: n2.n_nationkey
| |
| 18:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation n2]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n1.n_nationkey
| runtime filters: RF002 <- n1.n_nationkey
| row-size=111B cardinality=575.77K
|
|--JOIN BUILD
| | join-table-id=01 plan-id=02 cohort-id=01
| | build expressions: n1.n_nationkey
| |
| 17:EXCHANGE [BROADCAST]
| |
| 04:SCAN HDFS [tpch.nation n1]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF004 <- c_custkey
| row-size=90B cardinality=575.77K
|
|--JOIN BUILD
| | join-table-id=02 plan-id=03 cohort-id=01
| | build expressions: c_custkey
| |
| 16:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| runtime filters: RF000 -> c_nationkey
| row-size=10B cardinality=150.00K
|
07:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
| runtime filters: RF006 <- s_suppkey
| row-size=80B cardinality=575.77K
|
|--JOIN BUILD
| | join-table-id=03 plan-id=04 cohort-id=01
| | build expressions: s_suppkey
| |
| 15:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF002 -> s_nationkey
| row-size=10B cardinality=10.00K
|
06:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF008 <- o_orderkey
| row-size=70B cardinality=575.77K
|
|--JOIN BUILD
| | join-table-id=04 plan-id=05 cohort-id=01
| | build expressions: o_orderkey
| |
| 14:EXCHANGE [HASH(o_orderkey)]
| |
| 02:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| runtime filters: RF004 -> o_custkey
| row-size=16B cardinality=1.50M
|
13:EXCHANGE [HASH(l_orderkey)]
|
01:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate <= '1996-12-31', l_shipdate >= '1995-01-01'
runtime filters: RF006 -> l_suppkey, RF008 -> l_orderkey
row-size=54B cardinality=600.12K
====
# TPCH-Q8
# Q8 - National Market Share Query
select
o_year,
sum(case
when nation = 'BRAZIL'
then volume
else 0
end) / sum(volume) as mkt_share
from (
select
year(o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
part,
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2,
region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'AMERICA'
and s_nationkey = n2.n_nationkey
and o_orderdate between '1995-01-01' and '1996-12-31'
and p_type = 'ECONOMY ANODIZED STEEL'
) as all_nations
group by
o_year
order by
o_year
---- PLAN
Max Per-Host Resource Reservation: Memory=50.71MB Threads=9
Per-Host Resource Estimates: Memory=713MB
PLAN-ROOT SINK
|
16:SORT
| order by: o_year ASC
| row-size=36B cardinality=761
|
15:AGGREGATE [FINALIZE]
| output: sum(CASE WHEN n2.n_name = 'BRAZIL' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum(l_extendedprice * (1 - l_discount))
| group by: year(o_orderdate)
| row-size=36B cardinality=761
|
14:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n2.n_nationkey
| runtime filters: RF000 <- n2.n_nationkey
| row-size=184B cardinality=761
|
|--06:SCAN HDFS [tpch.nation n2]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
13:HASH JOIN [INNER JOIN]
| hash predicates: n1.n_regionkey = r_regionkey
| runtime filters: RF002 <- r_regionkey
| row-size=163B cardinality=761
|
|--07:SCAN HDFS [tpch.region]
| HDFS partitions=1/1 files=1 size=384B
| predicates: r_name = 'AMERICA'
| row-size=21B cardinality=1
|
12:HASH JOIN [INNER JOIN]
| hash predicates: c_nationkey = n1.n_nationkey
| runtime filters: RF004 <- n1.n_nationkey
| row-size=143B cardinality=3.81K
|
|--05:SCAN HDFS [tpch.nation n1]
| HDFS partitions=1/1 files=1 size=2.15KB
| runtime filters: RF002 -> n1.n_regionkey
| row-size=4B cardinality=25
|
11:HASH JOIN [INNER JOIN]
| hash predicates: c_custkey = o_custkey
| runtime filters: RF006 <- o_custkey
| row-size=139B cardinality=3.81K
|
|--10:HASH JOIN [INNER JOIN]
| | hash predicates: l_suppkey = s_suppkey
| | runtime filters: RF008 <- s_suppkey
| | row-size=129B cardinality=3.81K
| |
| |--01:SCAN HDFS [tpch.supplier]
| | HDFS partitions=1/1 files=1 size=1.33MB
| | runtime filters: RF000 -> s_nationkey
| | row-size=10B cardinality=10.00K
| |
| 09:HASH JOIN [INNER JOIN]
| | hash predicates: o_orderkey = l_orderkey
| | runtime filters: RF010 <- l_orderkey
| | row-size=119B cardinality=3.81K
| |
| |--08:HASH JOIN [INNER JOIN]
| | | hash predicates: l_partkey = p_partkey
| | | runtime filters: RF012 <- p_partkey
| | | row-size=81B cardinality=39.66K
| | |
| | |--00:SCAN HDFS [tpch.part]
| | | HDFS partitions=1/1 files=1 size=22.83MB
| | | predicates: p_type = 'ECONOMY ANODIZED STEEL'
| | | row-size=41B cardinality=1.32K
| | |
| | 02:SCAN HDFS [tpch.lineitem]
| | HDFS partitions=1/1 files=1 size=718.94MB
| | runtime filters: RF008 -> l_suppkey, RF012 -> l_partkey
| | row-size=40B cardinality=6.00M
| |
| 03:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate <= '1996-12-31', o_orderdate >= '1995-01-01'
| runtime filters: RF010 -> o_orderkey
| row-size=38B cardinality=150.00K
|
04:SCAN HDFS [tpch.customer]
HDFS partitions=1/1 files=1 size=23.08MB
runtime filters: RF004 -> c_nationkey, RF006 -> c_custkey
row-size=10B cardinality=150.00K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=71.46MB Threads=21
Per-Host Resource Estimates: Memory=756MB
PLAN-ROOT SINK
|
29:MERGING-EXCHANGE [UNPARTITIONED]
| order by: o_year ASC
|
16:SORT
| order by: o_year ASC
| row-size=36B cardinality=761
|
28:AGGREGATE [FINALIZE]
| output: sum:merge(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END), sum:merge(volume)
| group by: o_year
| row-size=36B cardinality=761
|
27:EXCHANGE [HASH(o_year)]
|
15:AGGREGATE [STREAMING]
| output: sum(CASE WHEN n2.n_name = 'BRAZIL' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum(l_extendedprice * (1 - l_discount))
| group by: year(o_orderdate)
| row-size=36B cardinality=761
|
14:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n2.n_nationkey
| runtime filters: RF000 <- n2.n_nationkey
| row-size=184B cardinality=761
|
|--26:EXCHANGE [BROADCAST]
| |
| 06:SCAN HDFS [tpch.nation n2]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
13:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n1.n_regionkey = r_regionkey
| runtime filters: RF002 <- r_regionkey
| row-size=163B cardinality=761
|
|--25:EXCHANGE [BROADCAST]
| |
| 07:SCAN HDFS [tpch.region]
| HDFS partitions=1/1 files=1 size=384B
| predicates: r_name = 'AMERICA'
| row-size=21B cardinality=1
|
12:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = n1.n_nationkey
| runtime filters: RF004 <- n1.n_nationkey
| row-size=143B cardinality=3.81K
|
|--24:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation n1]
| HDFS partitions=1/1 files=1 size=2.15KB
| runtime filters: RF002 -> n1.n_regionkey
| row-size=4B cardinality=25
|
11:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF006 <- c_custkey
| row-size=139B cardinality=3.81K
|
|--23:EXCHANGE [HASH(c_custkey)]
| |
| 04:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| runtime filters: RF004 -> c_nationkey
| row-size=10B cardinality=150.00K
|
22:EXCHANGE [HASH(o_custkey)]
|
10:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_suppkey = s_suppkey
| runtime filters: RF008 <- s_suppkey
| row-size=129B cardinality=3.81K
|
|--21:EXCHANGE [HASH(s_suppkey)]
| |
| 01:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF000 -> s_nationkey
| row-size=10B cardinality=10.00K
|
20:EXCHANGE [HASH(l_suppkey)]
|
09:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF010 <- o_orderkey
| row-size=119B cardinality=3.81K
|
|--19:EXCHANGE [HASH(o_orderkey)]
| |
| 03:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate <= '1996-12-31', o_orderdate >= '1995-01-01'
| runtime filters: RF006 -> o_custkey
| row-size=38B cardinality=150.00K
|
18:EXCHANGE [HASH(l_orderkey)]
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = p_partkey
| runtime filters: RF012 <- p_partkey
| row-size=81B cardinality=39.66K
|
|--17:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.part]
| HDFS partitions=1/1 files=1 size=22.83MB
| predicates: p_type = 'ECONOMY ANODIZED STEEL'
| row-size=41B cardinality=1.32K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
runtime filters: RF008 -> l_suppkey, RF010 -> l_orderkey, RF012 -> l_partkey
row-size=40B cardinality=6.00M
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=141.05MB Threads=25
Per-Host Resource Estimates: Memory=724MB
PLAN-ROOT SINK
|
29:MERGING-EXCHANGE [UNPARTITIONED]
| order by: o_year ASC
|
16:SORT
| order by: o_year ASC
| row-size=36B cardinality=761
|
28:AGGREGATE [FINALIZE]
| output: sum:merge(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END), sum:merge(volume)
| group by: o_year
| row-size=36B cardinality=761
|
27:EXCHANGE [HASH(o_year)]
|
15:AGGREGATE [STREAMING]
| output: sum(CASE WHEN n2.n_name = 'BRAZIL' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum(l_extendedprice * (1 - l_discount))
| group by: year(o_orderdate)
| row-size=36B cardinality=761
|
14:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n2.n_nationkey
| runtime filters: RF000 <- n2.n_nationkey
| row-size=184B cardinality=761
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: n2.n_nationkey
| |
| 26:EXCHANGE [BROADCAST]
| |
| 06:SCAN HDFS [tpch.nation n2]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
13:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: n1.n_regionkey = r_regionkey
| runtime filters: RF002 <- r_regionkey
| row-size=163B cardinality=761
|
|--JOIN BUILD
| | join-table-id=01 plan-id=02 cohort-id=01
| | build expressions: r_regionkey
| |
| 25:EXCHANGE [BROADCAST]
| |
| 07:SCAN HDFS [tpch.region]
| HDFS partitions=1/1 files=1 size=384B
| predicates: r_name = 'AMERICA'
| row-size=21B cardinality=1
|
12:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = n1.n_nationkey
| runtime filters: RF004 <- n1.n_nationkey
| row-size=143B cardinality=3.81K
|
|--JOIN BUILD
| | join-table-id=02 plan-id=03 cohort-id=01
| | build expressions: n1.n_nationkey
| |
| 24:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation n1]
| HDFS partitions=1/1 files=1 size=2.15KB
| runtime filters: RF002 -> n1.n_regionkey
| row-size=4B cardinality=25
|
11:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF006 <- c_custkey
| row-size=139B cardinality=3.81K
|
|--JOIN BUILD
| | join-table-id=03 plan-id=04 cohort-id=01
| | build expressions: c_custkey
| |
| 23:EXCHANGE [HASH(c_custkey)]
| |
| 04:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| runtime filters: RF004 -> c_nationkey
| row-size=10B cardinality=150.00K
|
22:EXCHANGE [HASH(o_custkey)]
|
10:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_suppkey = s_suppkey
| runtime filters: RF008 <- s_suppkey
| row-size=129B cardinality=3.81K
|
|--JOIN BUILD
| | join-table-id=04 plan-id=05 cohort-id=01
| | build expressions: s_suppkey
| |
| 21:EXCHANGE [HASH(s_suppkey)]
| |
| 01:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF000 -> s_nationkey
| row-size=10B cardinality=10.00K
|
20:EXCHANGE [HASH(l_suppkey)]
|
09:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF010 <- o_orderkey
| row-size=119B cardinality=3.81K
|
|--JOIN BUILD
| | join-table-id=05 plan-id=06 cohort-id=01
| | build expressions: o_orderkey
| |
| 19:EXCHANGE [HASH(o_orderkey)]
| |
| 03:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate <= '1996-12-31', o_orderdate >= '1995-01-01'
| runtime filters: RF006 -> o_custkey
| row-size=38B cardinality=150.00K
|
18:EXCHANGE [HASH(l_orderkey)]
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = p_partkey
| runtime filters: RF012 <- p_partkey
| row-size=81B cardinality=39.66K
|
|--JOIN BUILD
| | join-table-id=06 plan-id=07 cohort-id=01
| | build expressions: p_partkey
| |
| 17:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.part]
| HDFS partitions=1/1 files=1 size=22.83MB
| predicates: p_type = 'ECONOMY ANODIZED STEEL'
| row-size=41B cardinality=1.32K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
runtime filters: RF008 -> l_suppkey, RF010 -> l_orderkey, RF012 -> l_partkey
row-size=40B cardinality=6.00M
====
# TPCH-Q9
# Q9 - Product Type Measure Query
select
nation,
o_year,
sum(amount) as sum_profit
from(
select
n_name as nation,
year(o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
part,
supplier,
lineitem,
partsupp,
orders,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%green%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc
---- PLAN
Max Per-Host Resource Reservation: Memory=114.76MB Threads=7
Per-Host Resource Estimates: Memory=838MB
PLAN-ROOT SINK
|
12:SORT
| order by: nation ASC, o_year DESC
| row-size=39B cardinality=61.70K
|
11:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity)
| group by: n_name, year(o_orderdate)
| row-size=39B cardinality=61.70K
|
10:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
| runtime filters: RF000 <- n_nationkey
| row-size=186B cardinality=574.29K
|
|--05:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
09:HASH JOIN [INNER JOIN]
| hash predicates: l_partkey = ps_partkey, l_suppkey = ps_suppkey
| runtime filters: RF002 <- ps_partkey, RF003 <- ps_suppkey
| row-size=165B cardinality=574.29K
|
|--03:SCAN HDFS [tpch.partsupp]
| HDFS partitions=1/1 files=1 size=112.71MB
| row-size=24B cardinality=800.00K
|
08:HASH JOIN [INNER JOIN]
| hash predicates: l_suppkey = s_suppkey
| runtime filters: RF006 <- s_suppkey
| row-size=141B cardinality=574.29K
|
|--01:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF000 -> s_nationkey, RF003 -> tpch.supplier.s_suppkey
| row-size=10B cardinality=10.00K
|
07:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF008 <- o_orderkey
| row-size=131B cardinality=574.29K
|
|--04:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| row-size=30B cardinality=1.50M
|
06:HASH JOIN [INNER JOIN]
| hash predicates: l_partkey = p_partkey
| runtime filters: RF010 <- p_partkey
| row-size=101B cardinality=598.58K
|
|--00:SCAN HDFS [tpch.part]
| HDFS partitions=1/1 files=1 size=22.83MB
| predicates: p_name LIKE '%green%'
| runtime filters: RF002 -> tpch.part.p_partkey
| row-size=53B cardinality=20.00K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
runtime filters: RF002 -> l_partkey, RF003 -> l_suppkey, RF006 -> l_suppkey, RF008 -> l_orderkey, RF010 -> l_partkey
row-size=48B cardinality=6.00M
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=123.70MB Threads=15
Per-Host Resource Estimates: Memory=879MB
PLAN-ROOT SINK
|
21:MERGING-EXCHANGE [UNPARTITIONED]
| order by: nation ASC, o_year DESC
|
12:SORT
| order by: nation ASC, o_year DESC
| row-size=39B cardinality=61.70K
|
20:AGGREGATE [FINALIZE]
| output: sum:merge(amount)
| group by: nation, o_year
| row-size=39B cardinality=61.70K
|
19:EXCHANGE [HASH(nation,o_year)]
|
11:AGGREGATE [STREAMING]
| output: sum(l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity)
| group by: n_name, year(o_orderdate)
| row-size=39B cardinality=61.70K
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
| runtime filters: RF000 <- n_nationkey
| row-size=186B cardinality=574.29K
|
|--18:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
09:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = ps_partkey, l_suppkey = ps_suppkey
| runtime filters: RF002 <- ps_partkey, RF003 <- ps_suppkey
| row-size=165B cardinality=574.29K
|
|--17:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.partsupp]
| HDFS partitions=1/1 files=1 size=112.71MB
| row-size=24B cardinality=800.00K
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
| runtime filters: RF006 <- s_suppkey
| row-size=141B cardinality=574.29K
|
|--16:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF000 -> s_nationkey, RF003 -> tpch.supplier.s_suppkey
| row-size=10B cardinality=10.00K
|
07:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF008 <- o_orderkey
| row-size=131B cardinality=574.29K
|
|--15:EXCHANGE [HASH(o_orderkey)]
| |
| 04:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| row-size=30B cardinality=1.50M
|
14:EXCHANGE [HASH(l_orderkey)]
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = p_partkey
| runtime filters: RF010 <- p_partkey
| row-size=101B cardinality=598.58K
|
|--13:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.part]
| HDFS partitions=1/1 files=1 size=22.83MB
| predicates: p_name LIKE '%green%'
| runtime filters: RF002 -> tpch.part.p_partkey
| row-size=53B cardinality=20.00K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
runtime filters: RF002 -> l_partkey, RF003 -> l_suppkey, RF006 -> l_suppkey, RF008 -> l_orderkey, RF010 -> l_partkey
row-size=48B cardinality=6.00M
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=171.89MB Threads=19
Per-Host Resource Estimates: Memory=830MB
PLAN-ROOT SINK
|
22:MERGING-EXCHANGE [UNPARTITIONED]
| order by: nation ASC, o_year DESC
|
12:SORT
| order by: nation ASC, o_year DESC
| row-size=39B cardinality=61.70K
|
21:AGGREGATE [FINALIZE]
| output: sum:merge(amount)
| group by: nation, o_year
| row-size=39B cardinality=61.70K
|
20:EXCHANGE [HASH(nation,o_year)]
|
11:AGGREGATE [STREAMING]
| output: sum(l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity)
| group by: n_name, year(o_orderdate)
| row-size=39B cardinality=61.70K
|
10:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
| runtime filters: RF000 <- n_nationkey
| row-size=186B cardinality=574.29K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: n_nationkey
| |
| 19:EXCHANGE [BROADCAST]
| |
| 05:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
09:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_partkey = ps_partkey, l_suppkey = ps_suppkey
| runtime filters: RF002 <- ps_partkey, RF003 <- ps_suppkey
| row-size=165B cardinality=574.29K
|
|--JOIN BUILD
| | join-table-id=01 plan-id=02 cohort-id=01
| | build expressions: ps_partkey, ps_suppkey
| |
| 18:EXCHANGE [HASH(ps_partkey,ps_suppkey)]
| |
| 03:SCAN HDFS [tpch.partsupp]
| HDFS partitions=1/1 files=1 size=112.71MB
| row-size=24B cardinality=800.00K
|
17:EXCHANGE [HASH(l_partkey,l_suppkey)]
|
08:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_suppkey = s_suppkey
| runtime filters: RF006 <- s_suppkey
| row-size=141B cardinality=574.29K
|
|--JOIN BUILD
| | join-table-id=02 plan-id=03 cohort-id=01
| | build expressions: s_suppkey
| |
| 16:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF000 -> s_nationkey, RF003 -> tpch.supplier.s_suppkey
| row-size=10B cardinality=10.00K
|
07:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF008 <- o_orderkey
| row-size=131B cardinality=574.29K
|
|--JOIN BUILD
| | join-table-id=03 plan-id=04 cohort-id=01
| | build expressions: o_orderkey
| |
| 15:EXCHANGE [HASH(o_orderkey)]
| |
| 04:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| row-size=30B cardinality=1.50M
|
14:EXCHANGE [HASH(l_orderkey)]
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = p_partkey
| runtime filters: RF010 <- p_partkey
| row-size=101B cardinality=598.58K
|
|--JOIN BUILD
| | join-table-id=04 plan-id=05 cohort-id=01
| | build expressions: p_partkey
| |
| 13:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.part]
| HDFS partitions=1/1 files=1 size=22.83MB
| predicates: p_name LIKE '%green%'
| runtime filters: RF002 -> tpch.part.p_partkey
| row-size=53B cardinality=20.00K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
runtime filters: RF002 -> l_partkey, RF003 -> l_suppkey, RF006 -> l_suppkey, RF008 -> l_orderkey, RF010 -> l_partkey
row-size=48B cardinality=6.00M
====
# TPCH-Q10
# Q10 - Returned Item Reporting Query
# Converted select from multiple tables to joins
select
c_custkey,
c_name,
sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal,
n_name,
c_address,
c_phone,
c_comment
from
customer,
orders,
lineitem,
nation
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate >= '1993-10-01'
and o_orderdate < '1994-01-01'
and l_returnflag = 'R'
and c_nationkey = n_nationkey
group by
c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
order by
revenue desc
limit 20
---- PLAN
Max Per-Host Resource Reservation: Memory=63.94MB Threads=5
Per-Host Resource Estimates: Memory=549MB
PLAN-ROOT SINK
|
08:TOP-N [LIMIT=20]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
| row-size=230B cardinality=20
|
07:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
| row-size=230B cardinality=191.92K
|
06:HASH JOIN [INNER JOIN]
| hash predicates: c_nationkey = n_nationkey
| runtime filters: RF000 <- n_nationkey
| row-size=293B cardinality=191.92K
|
|--03:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
05:HASH JOIN [INNER JOIN]
| hash predicates: c_custkey = o_custkey
| runtime filters: RF002 <- o_custkey
| row-size=272B cardinality=191.92K
|
|--04:HASH JOIN [INNER JOIN]
| | hash predicates: l_orderkey = o_orderkey
| | runtime filters: RF004 <- o_orderkey
| | row-size=75B cardinality=191.92K
| |
| |--01:SCAN HDFS [tpch.orders]
| | HDFS partitions=1/1 files=1 size=162.56MB
| | predicates: o_orderdate < '1994-01-01', o_orderdate >= '1993-10-01'
| | row-size=38B cardinality=150.00K
| |
| 02:SCAN HDFS [tpch.lineitem]
| HDFS partitions=1/1 files=1 size=718.94MB
| predicates: l_returnflag = 'R'
| runtime filters: RF004 -> l_orderkey
| row-size=37B cardinality=2.00M
|
00:SCAN HDFS [tpch.customer]
HDFS partitions=1/1 files=1 size=23.08MB
runtime filters: RF000 -> c_nationkey, RF002 -> c_custkey
row-size=197B cardinality=150.00K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=124.45MB Threads=11
Per-Host Resource Estimates: Memory=668MB
PLAN-ROOT SINK
|
15:MERGING-EXCHANGE [UNPARTITIONED]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
| limit: 20
|
08:TOP-N [LIMIT=20]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
| row-size=230B cardinality=20
|
14:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
| row-size=230B cardinality=191.92K
|
13:EXCHANGE [HASH(c_custkey,c_name,c_acctbal,c_phone,n_name,c_address,c_comment)]
|
07:AGGREGATE [STREAMING]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
| row-size=230B cardinality=191.92K
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = n_nationkey
| runtime filters: RF000 <- n_nationkey
| row-size=293B cardinality=191.92K
|
|--12:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
05:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF002 <- c_custkey
| row-size=272B cardinality=191.92K
|
|--11:EXCHANGE [HASH(c_custkey)]
| |
| 00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| runtime filters: RF000 -> c_nationkey
| row-size=197B cardinality=150.00K
|
10:EXCHANGE [HASH(o_custkey)]
|
04:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF004 <- o_orderkey
| row-size=75B cardinality=191.92K
|
|--09:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate < '1994-01-01', o_orderdate >= '1993-10-01'
| runtime filters: RF002 -> o_custkey
| row-size=38B cardinality=150.00K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_returnflag = 'R'
runtime filters: RF004 -> l_orderkey
row-size=37B cardinality=2.00M
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=163.89MB Threads=13
Per-Host Resource Estimates: Memory=607MB
PLAN-ROOT SINK
|
15:MERGING-EXCHANGE [UNPARTITIONED]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
| limit: 20
|
08:TOP-N [LIMIT=20]
| order by: sum(l_extendedprice * (1 - l_discount)) DESC
| row-size=230B cardinality=20
|
14:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
| row-size=230B cardinality=191.92K
|
13:EXCHANGE [HASH(c_custkey,c_name,c_acctbal,c_phone,n_name,c_address,c_comment)]
|
07:AGGREGATE [STREAMING]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
| row-size=230B cardinality=191.92K
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: c_nationkey = n_nationkey
| runtime filters: RF000 <- n_nationkey
| row-size=293B cardinality=191.92K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: n_nationkey
| |
| 12:EXCHANGE [BROADCAST]
| |
| 03:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| row-size=21B cardinality=25
|
05:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF002 <- c_custkey
| row-size=272B cardinality=191.92K
|
|--JOIN BUILD
| | join-table-id=01 plan-id=02 cohort-id=01
| | build expressions: c_custkey
| |
| 11:EXCHANGE [HASH(c_custkey)]
| |
| 00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| runtime filters: RF000 -> c_nationkey
| row-size=197B cardinality=150.00K
|
10:EXCHANGE [HASH(o_custkey)]
|
04:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF004 <- o_orderkey
| row-size=75B cardinality=191.92K
|
|--JOIN BUILD
| | join-table-id=02 plan-id=03 cohort-id=01
| | build expressions: o_orderkey
| |
| 09:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| predicates: o_orderdate < '1994-01-01', o_orderdate >= '1993-10-01'
| runtime filters: RF002 -> o_custkey
| row-size=38B cardinality=150.00K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_returnflag = 'R'
runtime filters: RF004 -> l_orderkey
row-size=37B cardinality=2.00M
====
# TPCH-Q11
# Q11 - Important Stock Identification
# Modifications: query was rewritten to not have a subquery in the having clause
select
*
from (
select
ps_partkey,
sum(ps_supplycost * ps_availqty) as value
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'GERMANY'
group by
ps_partkey
) as inner_query
where
value > (
select
sum(ps_supplycost * ps_availqty) * 0.0001
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'GERMANY'
)
order by
value desc
---- PLAN
Max Per-Host Resource Reservation: Memory=31.77MB Threads=7
Per-Host Resource Estimates: Memory=492MB
PLAN-ROOT SINK
|
13:SORT
| order by: value DESC
| row-size=24B cardinality=32.00K
|
12:NESTED LOOP JOIN [INNER JOIN]
| predicates: sum(ps_supplycost * ps_availqty) > sum(ps_supplycost * ps_availqty) * 0.0001
| row-size=40B cardinality=32.00K
|
|--11:AGGREGATE [FINALIZE]
| | output: sum(ps_supplycost * ps_availqty)
| | row-size=16B cardinality=1
| |
| 10:HASH JOIN [INNER JOIN]
| | hash predicates: s_nationkey = n_nationkey
| | runtime filters: RF004 <- n_nationkey
| | row-size=51B cardinality=32.00K
| |
| |--08:SCAN HDFS [tpch.nation]
| | HDFS partitions=1/1 files=1 size=2.15KB
| | predicates: n_name = 'GERMANY'
| | row-size=21B cardinality=1
| |
| 09:HASH JOIN [INNER JOIN]
| | hash predicates: ps_suppkey = s_suppkey
| | runtime filters: RF006 <- s_suppkey
| | row-size=30B cardinality=800.00K
| |
| |--07:SCAN HDFS [tpch.supplier]
| | HDFS partitions=1/1 files=1 size=1.33MB
| | runtime filters: RF004 -> s_nationkey
| | row-size=10B cardinality=10.00K
| |
| 06:SCAN HDFS [tpch.partsupp]
| HDFS partitions=1/1 files=1 size=112.71MB
| runtime filters: RF006 -> ps_suppkey
| row-size=20B cardinality=800.00K
|
05:AGGREGATE [FINALIZE]
| output: sum(ps_supplycost * ps_availqty)
| group by: ps_partkey
| row-size=24B cardinality=32.00K
|
04:HASH JOIN [INNER JOIN]
| hash predicates: s_nationkey = n_nationkey
| runtime filters: RF000 <- n_nationkey
| row-size=59B cardinality=32.00K
|
|--02:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| predicates: n_name = 'GERMANY'
| row-size=21B cardinality=1
|
03:HASH JOIN [INNER JOIN]
| hash predicates: ps_suppkey = s_suppkey
| runtime filters: RF002 <- s_suppkey
| row-size=38B cardinality=800.00K
|
|--01:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF000 -> s_nationkey
| row-size=10B cardinality=10.00K
|
00:SCAN HDFS [tpch.partsupp]
HDFS partitions=1/1 files=1 size=112.71MB
runtime filters: RF002 -> ps_suppkey
row-size=28B cardinality=800.00K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=43.70MB Threads=15
Per-Host Resource Estimates: Memory=541MB
PLAN-ROOT SINK
|
23:MERGING-EXCHANGE [UNPARTITIONED]
| order by: value DESC
|
13:SORT
| order by: value DESC
| row-size=24B cardinality=32.00K
|
12:NESTED LOOP JOIN [INNER JOIN, BROADCAST]
| predicates: sum(ps_supplycost * ps_availqty) > sum(ps_supplycost * ps_availqty) * 0.0001
| row-size=40B cardinality=32.00K
|
|--22:EXCHANGE [BROADCAST]
| |
| 21:AGGREGATE [FINALIZE]
| | output: sum:merge(ps_supplycost * ps_availqty)
| | row-size=16B cardinality=1
| |
| 20:EXCHANGE [UNPARTITIONED]
| |
| 11:AGGREGATE
| | output: sum(ps_supplycost * ps_availqty)
| | row-size=16B cardinality=1
| |
| 10:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: s_nationkey = n_nationkey
| | runtime filters: RF004 <- n_nationkey
| | row-size=51B cardinality=32.00K
| |
| |--19:EXCHANGE [BROADCAST]
| | |
| | 08:SCAN HDFS [tpch.nation]
| | HDFS partitions=1/1 files=1 size=2.15KB
| | predicates: n_name = 'GERMANY'
| | row-size=21B cardinality=1
| |
| 09:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: ps_suppkey = s_suppkey
| | runtime filters: RF006 <- s_suppkey
| | row-size=30B cardinality=800.00K
| |
| |--18:EXCHANGE [BROADCAST]
| | |
| | 07:SCAN HDFS [tpch.supplier]
| | HDFS partitions=1/1 files=1 size=1.33MB
| | runtime filters: RF004 -> s_nationkey
| | row-size=10B cardinality=10.00K
| |
| 06:SCAN HDFS [tpch.partsupp]
| HDFS partitions=1/1 files=1 size=112.71MB
| runtime filters: RF006 -> ps_suppkey
| row-size=20B cardinality=800.00K
|
17:AGGREGATE [FINALIZE]
| output: sum:merge(ps_supplycost * ps_availqty)
| group by: ps_partkey
| row-size=24B cardinality=32.00K
|
16:EXCHANGE [HASH(ps_partkey)]
|
05:AGGREGATE [STREAMING]
| output: sum(ps_supplycost * ps_availqty)
| group by: ps_partkey
| row-size=24B cardinality=32.00K
|
04:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
| runtime filters: RF000 <- n_nationkey
| row-size=59B cardinality=32.00K
|
|--15:EXCHANGE [BROADCAST]
| |
| 02:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| predicates: n_name = 'GERMANY'
| row-size=21B cardinality=1
|
03:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ps_suppkey = s_suppkey
| runtime filters: RF002 <- s_suppkey
| row-size=38B cardinality=800.00K
|
|--14:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF000 -> s_nationkey
| row-size=10B cardinality=10.00K
|
00:SCAN HDFS [tpch.partsupp]
HDFS partitions=1/1 files=1 size=112.71MB
runtime filters: RF002 -> ps_suppkey
row-size=28B cardinality=800.00K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=87.41MB Threads=16
Per-Host Resource Estimates: Memory=590MB
PLAN-ROOT SINK
|
23:MERGING-EXCHANGE [UNPARTITIONED]
| order by: value DESC
|
13:SORT
| order by: value DESC
| row-size=24B cardinality=32.00K
|
12:NESTED LOOP JOIN [INNER JOIN, BROADCAST]
| join table id: 00
| predicates: sum(ps_supplycost * ps_availqty) > sum(ps_supplycost * ps_availqty) * 0.0001
| row-size=40B cardinality=32.00K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| |
| 22:EXCHANGE [BROADCAST]
| |
| 21:AGGREGATE [FINALIZE]
| | output: sum:merge(ps_supplycost * ps_availqty)
| | row-size=16B cardinality=1
| |
| 20:EXCHANGE [UNPARTITIONED]
| |
| 11:AGGREGATE
| | output: sum(ps_supplycost * ps_availqty)
| | row-size=16B cardinality=1
| |
| 10:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: s_nationkey = n_nationkey
| | runtime filters: RF004 <- n_nationkey
| | row-size=51B cardinality=32.00K
| |
| |--JOIN BUILD
| | | join-table-id=01 plan-id=02 cohort-id=02
| | | build expressions: n_nationkey
| | |
| | 19:EXCHANGE [BROADCAST]
| | |
| | 08:SCAN HDFS [tpch.nation]
| | HDFS partitions=1/1 files=1 size=2.15KB
| | predicates: n_name = 'GERMANY'
| | row-size=21B cardinality=1
| |
| 09:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: ps_suppkey = s_suppkey
| | runtime filters: RF006 <- s_suppkey
| | row-size=30B cardinality=800.00K
| |
| |--JOIN BUILD
| | | join-table-id=02 plan-id=03 cohort-id=02
| | | build expressions: s_suppkey
| | |
| | 18:EXCHANGE [BROADCAST]
| | |
| | 07:SCAN HDFS [tpch.supplier]
| | HDFS partitions=1/1 files=1 size=1.33MB
| | runtime filters: RF004 -> s_nationkey
| | row-size=10B cardinality=10.00K
| |
| 06:SCAN HDFS [tpch.partsupp]
| HDFS partitions=1/1 files=1 size=112.71MB
| runtime filters: RF006 -> ps_suppkey
| row-size=20B cardinality=800.00K
|
17:AGGREGATE [FINALIZE]
| output: sum:merge(ps_supplycost * ps_availqty)
| group by: ps_partkey
| row-size=24B cardinality=32.00K
|
16:EXCHANGE [HASH(ps_partkey)]
|
05:AGGREGATE [STREAMING]
| output: sum(ps_supplycost * ps_availqty)
| group by: ps_partkey
| row-size=24B cardinality=32.00K
|
04:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: s_nationkey = n_nationkey
| runtime filters: RF000 <- n_nationkey
| row-size=59B cardinality=32.00K
|
|--JOIN BUILD
| | join-table-id=03 plan-id=04 cohort-id=01
| | build expressions: n_nationkey
| |
| 15:EXCHANGE [BROADCAST]
| |
| 02:SCAN HDFS [tpch.nation]
| HDFS partitions=1/1 files=1 size=2.15KB
| predicates: n_name = 'GERMANY'
| row-size=21B cardinality=1
|
03:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ps_suppkey = s_suppkey
| runtime filters: RF002 <- s_suppkey
| row-size=38B cardinality=800.00K
|
|--JOIN BUILD
| | join-table-id=04 plan-id=05 cohort-id=01
| | build expressions: s_suppkey
| |
| 14:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF000 -> s_nationkey
| row-size=10B cardinality=10.00K
|
00:SCAN HDFS [tpch.partsupp]
HDFS partitions=1/1 files=1 size=112.71MB
runtime filters: RF002 -> ps_suppkey
row-size=28B cardinality=800.00K
====
# TPCH-Q12
# Q12 - Shipping Mode and Order Priority Query
select
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
or o_orderpriority = '2-HIGH'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT'
and o_orderpriority <> '2-HIGH'
then 1
else 0
end) as low_line_count
from
orders,
lineitem
where
o_orderkey = l_orderkey
and l_shipmode in ('MAIL', 'SHIP')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= '1994-01-01'
and l_receiptdate < '1995-01-01'
group by
l_shipmode
order by
l_shipmode
---- PLAN
Max Per-Host Resource Reservation: Memory=51.00MB Threads=3
Per-Host Resource Estimates: Memory=475MB
PLAN-ROOT SINK
|
04:SORT
| order by: l_shipmode ASC
| row-size=32B cardinality=7
|
03:AGGREGATE [FINALIZE]
| output: sum(CASE WHEN o_orderpriority IN ('1-URGENT', '2-HIGH') THEN 1 ELSE 0 END), sum(CASE WHEN o_orderpriority != '1-URGENT' AND o_orderpriority != '2-HIGH' THEN 1 ELSE 0 END)
| group by: l_shipmode
| row-size=32B cardinality=7
|
02:HASH JOIN [INNER JOIN]
| hash predicates: o_orderkey = l_orderkey
| runtime filters: RF000 <- l_orderkey
| row-size=119B cardinality=320.78K
|
|--01:SCAN HDFS [tpch.lineitem]
| HDFS partitions=1/1 files=1 size=718.94MB
| predicates: l_shipmode IN ('MAIL', 'SHIP'), l_commitdate < l_receiptdate, l_receiptdate < '1995-01-01', l_receiptdate >= '1994-01-01', l_shipdate < l_commitdate
| row-size=90B cardinality=320.78K
|
00:SCAN HDFS [tpch.orders]
HDFS partitions=1/1 files=1 size=162.56MB
runtime filters: RF000 -> o_orderkey
row-size=28B cardinality=1.50M
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=67.94MB Threads=7
Per-Host Resource Estimates: Memory=528MB
PLAN-ROOT SINK
|
09:MERGING-EXCHANGE [UNPARTITIONED]
| order by: l_shipmode ASC
|
04:SORT
| order by: l_shipmode ASC
| row-size=32B cardinality=7
|
08:AGGREGATE [FINALIZE]
| output: sum:merge(CASE WHEN o_orderpriority IN ('1-URGENT', '2-HIGH') THEN 1 ELSE 0 END), sum:merge(CASE WHEN o_orderpriority != '1-URGENT' AND o_orderpriority != '2-HIGH' THEN 1 ELSE 0 END)
| group by: l_shipmode
| row-size=32B cardinality=7
|
07:EXCHANGE [HASH(l_shipmode)]
|
03:AGGREGATE [STREAMING]
| output: sum(CASE WHEN o_orderpriority IN ('1-URGENT', '2-HIGH') THEN 1 ELSE 0 END), sum(CASE WHEN o_orderpriority != '1-URGENT' AND o_orderpriority != '2-HIGH' THEN 1 ELSE 0 END)
| group by: l_shipmode
| row-size=32B cardinality=7
|
02:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF000 <- o_orderkey
| row-size=119B cardinality=320.78K
|
|--06:EXCHANGE [HASH(o_orderkey)]
| |
| 00:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| row-size=28B cardinality=1.50M
|
05:EXCHANGE [HASH(l_orderkey)]
|
01:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipmode IN ('MAIL', 'SHIP'), l_commitdate < l_receiptdate, l_receiptdate < '1995-01-01', l_receiptdate >= '1994-01-01', l_shipdate < l_commitdate
runtime filters: RF000 -> l_orderkey
row-size=90B cardinality=320.78K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=101.88MB Threads=9
Per-Host Resource Estimates: Memory=474MB
PLAN-ROOT SINK
|
09:MERGING-EXCHANGE [UNPARTITIONED]
| order by: l_shipmode ASC
|
04:SORT
| order by: l_shipmode ASC
| row-size=32B cardinality=7
|
08:AGGREGATE [FINALIZE]
| output: sum:merge(CASE WHEN o_orderpriority IN ('1-URGENT', '2-HIGH') THEN 1 ELSE 0 END), sum:merge(CASE WHEN o_orderpriority != '1-URGENT' AND o_orderpriority != '2-HIGH' THEN 1 ELSE 0 END)
| group by: l_shipmode
| row-size=32B cardinality=7
|
07:EXCHANGE [HASH(l_shipmode)]
|
03:AGGREGATE [STREAMING]
| output: sum(CASE WHEN o_orderpriority IN ('1-URGENT', '2-HIGH') THEN 1 ELSE 0 END), sum(CASE WHEN o_orderpriority != '1-URGENT' AND o_orderpriority != '2-HIGH' THEN 1 ELSE 0 END)
| group by: l_shipmode
| row-size=32B cardinality=7
|
02:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF000 <- o_orderkey
| row-size=119B cardinality=320.78K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: o_orderkey
| |
| 06:EXCHANGE [HASH(o_orderkey)]
| |
| 00:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| row-size=28B cardinality=1.50M
|
05:EXCHANGE [HASH(l_orderkey)]
|
01:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipmode IN ('MAIL', 'SHIP'), l_commitdate < l_receiptdate, l_receiptdate < '1995-01-01', l_receiptdate >= '1994-01-01', l_shipdate < l_commitdate
runtime filters: RF000 -> l_orderkey
row-size=90B cardinality=320.78K
====
# TPCH-Q13
# Q13 - Customer Distribution Query
select
c_count,
count(*) as custdist
from (
select
c_custkey,
count(o_orderkey) as c_count
from
customer left outer join orders on (
c_custkey = o_custkey
and o_comment not like '%special%requests%'
)
group by
c_custkey
) as c_orders
group by
c_count
order by
custdist desc,
c_count desc
---- PLAN
Max Per-Host Resource Reservation: Memory=19.88MB Threads=3
Per-Host Resource Estimates: Memory=244MB
PLAN-ROOT SINK
|
05:SORT
| order by: count(*) DESC, c_count DESC
| row-size=16B cardinality=150.00K
|
04:AGGREGATE [FINALIZE]
| output: count(*)
| group by: count(o_orderkey)
| row-size=16B cardinality=150.00K
|
03:AGGREGATE [FINALIZE]
| output: count(o_orderkey)
| group by: c_custkey
| row-size=16B cardinality=150.00K
|
02:HASH JOIN [RIGHT OUTER JOIN]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF000 <- c_custkey
| row-size=85B cardinality=150.00K
|
|--00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| row-size=8B cardinality=150.00K
|
01:SCAN HDFS [tpch.orders]
HDFS partitions=1/1 files=1 size=162.56MB
predicates: NOT o_comment LIKE '%special%requests%'
runtime filters: RF000 -> o_custkey
row-size=77B cardinality=150.00K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=41.38MB Threads=7
Per-Host Resource Estimates: Memory=289MB
PLAN-ROOT SINK
|
10:MERGING-EXCHANGE [UNPARTITIONED]
| order by: count(*) DESC, c_count DESC
|
05:SORT
| order by: count(*) DESC, c_count DESC
| row-size=16B cardinality=150.00K
|
09:AGGREGATE [FINALIZE]
| output: count:merge(*)
| group by: c_count
| row-size=16B cardinality=150.00K
|
08:EXCHANGE [HASH(c_count)]
|
04:AGGREGATE [STREAMING]
| output: count(*)
| group by: count(o_orderkey)
| row-size=16B cardinality=150.00K
|
03:AGGREGATE [FINALIZE]
| output: count(o_orderkey)
| group by: c_custkey
| row-size=16B cardinality=150.00K
|
02:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF000 <- c_custkey
| row-size=85B cardinality=150.00K
|
|--07:EXCHANGE [HASH(c_custkey)]
| |
| 00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| row-size=8B cardinality=150.00K
|
06:EXCHANGE [HASH(o_custkey)]
|
01:SCAN HDFS [tpch.orders]
HDFS partitions=1/1 files=1 size=162.56MB
predicates: NOT o_comment LIKE '%special%requests%'
runtime filters: RF000 -> o_custkey
row-size=77B cardinality=150.00K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=69.38MB Threads=9
Per-Host Resource Estimates: Memory=334MB
PLAN-ROOT SINK
|
10:MERGING-EXCHANGE [UNPARTITIONED]
| order by: count(*) DESC, c_count DESC
|
05:SORT
| order by: count(*) DESC, c_count DESC
| row-size=16B cardinality=150.00K
|
09:AGGREGATE [FINALIZE]
| output: count:merge(*)
| group by: c_count
| row-size=16B cardinality=150.00K
|
08:EXCHANGE [HASH(c_count)]
|
04:AGGREGATE [STREAMING]
| output: count(*)
| group by: count(o_orderkey)
| row-size=16B cardinality=150.00K
|
03:AGGREGATE [FINALIZE]
| output: count(o_orderkey)
| group by: c_custkey
| row-size=16B cardinality=150.00K
|
02:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF000 <- c_custkey
| row-size=85B cardinality=150.00K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: c_custkey
| |
| 07:EXCHANGE [HASH(c_custkey)]
| |
| 00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| row-size=8B cardinality=150.00K
|
06:EXCHANGE [HASH(o_custkey)]
|
01:SCAN HDFS [tpch.orders]
HDFS partitions=1/1 files=1 size=162.56MB
predicates: NOT o_comment LIKE '%special%requests%'
runtime filters: RF000 -> o_custkey
row-size=77B cardinality=150.00K
====
# TPCH-Q14
# Q14 - Promotion Effect
select
100.00 * sum(case
when p_type like 'PROMO%'
then l_extendedprice * (1 - l_discount)
else 0.0
end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
lineitem,
part
where
l_partkey = p_partkey
and l_shipdate >= '1995-09-01'
and l_shipdate < '1995-10-01'
---- PLAN
Max Per-Host Resource Reservation: Memory=25.50MB Threads=3
Per-Host Resource Estimates: Memory=338MB
PLAN-ROOT SINK
|
03:AGGREGATE [FINALIZE]
| output: sum(CASE WHEN p_type LIKE 'PROMO%' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum(l_extendedprice * (1 - l_discount))
| row-size=32B cardinality=1
|
02:HASH JOIN [INNER JOIN]
| hash predicates: l_partkey = p_partkey
| runtime filters: RF000 <- p_partkey
| row-size=87B cardinality=598.58K
|
|--01:SCAN HDFS [tpch.part]
| HDFS partitions=1/1 files=1 size=22.83MB
| row-size=41B cardinality=200.00K
|
00:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate < '1995-10-01', l_shipdate >= '1995-09-01'
runtime filters: RF000 -> l_partkey
row-size=46B cardinality=600.12K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=22.75MB Threads=6
Per-Host Resource Estimates: Memory=364MB
PLAN-ROOT SINK
|
07:AGGREGATE [FINALIZE]
| output: sum:merge(CASE WHEN p_type LIKE 'PROMO%' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum:merge(l_extendedprice * (1 - l_discount))
| row-size=32B cardinality=1
|
06:EXCHANGE [UNPARTITIONED]
|
03:AGGREGATE
| output: sum(CASE WHEN p_type LIKE 'PROMO%' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum(l_extendedprice * (1 - l_discount))
| row-size=32B cardinality=1
|
02:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_partkey = p_partkey
| runtime filters: RF000 <- p_partkey
| row-size=87B cardinality=598.58K
|
|--05:EXCHANGE [HASH(p_partkey)]
| |
| 01:SCAN HDFS [tpch.part]
| HDFS partitions=1/1 files=1 size=22.83MB
| row-size=41B cardinality=200.00K
|
04:EXCHANGE [HASH(l_partkey)]
|
00:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate < '1995-10-01', l_shipdate >= '1995-09-01'
runtime filters: RF000 -> l_partkey
row-size=46B cardinality=600.12K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=41.75MB Threads=7
Per-Host Resource Estimates: Memory=298MB
PLAN-ROOT SINK
|
07:AGGREGATE [FINALIZE]
| output: sum:merge(CASE WHEN p_type LIKE 'PROMO%' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum:merge(l_extendedprice * (1 - l_discount))
| row-size=32B cardinality=1
|
06:EXCHANGE [UNPARTITIONED]
|
03:AGGREGATE
| output: sum(CASE WHEN p_type LIKE 'PROMO%' THEN l_extendedprice * (1 - l_discount) ELSE 0 END), sum(l_extendedprice * (1 - l_discount))
| row-size=32B cardinality=1
|
02:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_partkey = p_partkey
| runtime filters: RF000 <- p_partkey
| row-size=87B cardinality=598.58K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: p_partkey
| |
| 05:EXCHANGE [HASH(p_partkey)]
| |
| 01:SCAN HDFS [tpch.part]
| HDFS partitions=1/1 files=1 size=22.83MB
| row-size=41B cardinality=200.00K
|
04:EXCHANGE [HASH(l_partkey)]
|
00:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate < '1995-10-01', l_shipdate >= '1995-09-01'
runtime filters: RF000 -> l_partkey
row-size=46B cardinality=600.12K
====
# TPCH-Q15
# Q15 - Top Supplier Query
with revenue_view as (
select
l_suppkey as supplier_no,
sum(l_extendedprice * (1 - l_discount)) as total_revenue
from
lineitem
where
l_shipdate >= '1996-01-01'
and l_shipdate < '1996-04-01'
group by
l_suppkey)
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue_view
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue_view
)
order by
s_suppkey
---- PLAN
Max Per-Host Resource Reservation: Memory=22.88MB Threads=4
Per-Host Resource Estimates: Memory=581MB
PLAN-ROOT SINK
|
08:SORT
| order by: s_suppkey ASC
| row-size=118B cardinality=1
|
07:HASH JOIN [LEFT SEMI JOIN]
| hash predicates: sum(l_extendedprice * (1 - l_discount)) = max(total_revenue)
| row-size=126B cardinality=1
|
|--05:AGGREGATE [FINALIZE]
| | output: max(sum(l_extendedprice * (1 - l_discount)))
| | row-size=16B cardinality=1
| |
| 04:AGGREGATE [FINALIZE]
| | output: sum(l_extendedprice * (1 - l_discount))
| | group by: l_suppkey
| | row-size=24B cardinality=9.71K
| |
| 03:SCAN HDFS [tpch.lineitem]
| HDFS partitions=1/1 files=1 size=718.94MB
| predicates: l_shipdate < '1996-04-01', l_shipdate >= '1996-01-01'
| row-size=46B cardinality=600.12K
|
06:HASH JOIN [INNER JOIN]
| hash predicates: s_suppkey = l_suppkey
| runtime filters: RF000 <- l_suppkey
| row-size=126B cardinality=10.00K
|
|--02:AGGREGATE [FINALIZE]
| | output: sum(l_extendedprice * (1 - l_discount))
| | group by: l_suppkey
| | row-size=24B cardinality=9.71K
| |
| 01:SCAN HDFS [tpch.lineitem]
| HDFS partitions=1/1 files=1 size=718.94MB
| predicates: l_shipdate < '1996-04-01', l_shipdate >= '1996-01-01'
| row-size=46B cardinality=600.12K
|
00:SCAN HDFS [tpch.supplier]
HDFS partitions=1/1 files=1 size=1.33MB
runtime filters: RF000 -> s_suppkey
row-size=102B cardinality=10.00K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=43.75MB Threads=10
Per-Host Resource Estimates: Memory=638MB
PLAN-ROOT SINK
|
17:MERGING-EXCHANGE [UNPARTITIONED]
| order by: s_suppkey ASC
|
08:SORT
| order by: s_suppkey ASC
| row-size=118B cardinality=1
|
07:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
| hash predicates: sum(l_extendedprice * (1 - l_discount)) = max(total_revenue)
| row-size=126B cardinality=1
|
|--16:EXCHANGE [BROADCAST]
| |
| 15:AGGREGATE [FINALIZE]
| | output: max:merge(total_revenue)
| | row-size=16B cardinality=1
| |
| 14:EXCHANGE [UNPARTITIONED]
| |
| 05:AGGREGATE
| | output: max(sum(l_extendedprice * (1 - l_discount)))
| | row-size=16B cardinality=1
| |
| 13:AGGREGATE [FINALIZE]
| | output: sum:merge(l_extendedprice * (1 - l_discount))
| | group by: l_suppkey
| | row-size=24B cardinality=9.71K
| |
| 12:EXCHANGE [HASH(l_suppkey)]
| |
| 04:AGGREGATE [STREAMING]
| | output: sum(l_extendedprice * (1 - l_discount))
| | group by: l_suppkey
| | row-size=24B cardinality=9.71K
| |
| 03:SCAN HDFS [tpch.lineitem]
| HDFS partitions=1/1 files=1 size=718.94MB
| predicates: l_shipdate < '1996-04-01', l_shipdate >= '1996-01-01'
| row-size=46B cardinality=600.12K
|
06:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_suppkey = s_suppkey
| runtime filters: RF000 <- s_suppkey
| row-size=126B cardinality=10.00K
|
|--11:EXCHANGE [HASH(s_suppkey)]
| |
| 00:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| row-size=102B cardinality=10.00K
|
10:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: l_suppkey
| row-size=24B cardinality=9.71K
|
09:EXCHANGE [HASH(l_suppkey)]
|
02:AGGREGATE [STREAMING]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: l_suppkey
| row-size=24B cardinality=9.71K
|
01:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate < '1996-04-01', l_shipdate >= '1996-01-01'
runtime filters: RF000 -> tpch.lineitem.l_suppkey
row-size=46B cardinality=600.12K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=87.50MB Threads=12
Per-Host Resource Estimates: Memory=530MB
PLAN-ROOT SINK
|
17:MERGING-EXCHANGE [UNPARTITIONED]
| order by: s_suppkey ASC
|
08:SORT
| order by: s_suppkey ASC
| row-size=118B cardinality=1
|
07:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
| hash predicates: sum(l_extendedprice * (1 - l_discount)) = max(total_revenue)
| row-size=126B cardinality=1
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: max(total_revenue)
| |
| 16:EXCHANGE [BROADCAST]
| |
| 15:AGGREGATE [FINALIZE]
| | output: max:merge(total_revenue)
| | row-size=16B cardinality=1
| |
| 14:EXCHANGE [UNPARTITIONED]
| |
| 05:AGGREGATE
| | output: max(sum(l_extendedprice * (1 - l_discount)))
| | row-size=16B cardinality=1
| |
| 13:AGGREGATE [FINALIZE]
| | output: sum:merge(l_extendedprice * (1 - l_discount))
| | group by: l_suppkey
| | row-size=24B cardinality=9.71K
| |
| 12:EXCHANGE [HASH(l_suppkey)]
| |
| 04:AGGREGATE [STREAMING]
| | output: sum(l_extendedprice * (1 - l_discount))
| | group by: l_suppkey
| | row-size=24B cardinality=9.71K
| |
| 03:SCAN HDFS [tpch.lineitem]
| HDFS partitions=1/1 files=1 size=718.94MB
| predicates: l_shipdate < '1996-04-01', l_shipdate >= '1996-01-01'
| row-size=46B cardinality=600.12K
|
06:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_suppkey = s_suppkey
| runtime filters: RF000 <- s_suppkey
| row-size=126B cardinality=10.00K
|
|--JOIN BUILD
| | join-table-id=01 plan-id=02 cohort-id=01
| | build expressions: s_suppkey
| |
| 11:EXCHANGE [HASH(s_suppkey)]
| |
| 00:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| row-size=102B cardinality=10.00K
|
10:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| group by: l_suppkey
| row-size=24B cardinality=9.71K
|
09:EXCHANGE [HASH(l_suppkey)]
|
02:AGGREGATE [STREAMING]
| output: sum(l_extendedprice * (1 - l_discount))
| group by: l_suppkey
| row-size=24B cardinality=9.71K
|
01:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate < '1996-04-01', l_shipdate >= '1996-01-01'
runtime filters: RF000 -> tpch.lineitem.l_suppkey
row-size=46B cardinality=600.12K
====
# TPCH-Q16
# Q16 - Parts/Supplier Relation Query
select
p_brand,
p_type,
p_size,
count(distinct ps_suppkey) as supplier_cnt
from
partsupp,
part
where
p_partkey = ps_partkey
and p_brand <> 'Brand#45'
and p_type not like 'MEDIUM POLISHED%'
and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
and ps_suppkey not in (
select
s_suppkey
from
supplier
where
s_comment like '%Customer%Complaints%'
)
group by
p_brand,
p_type,
p_size
order by
supplier_cnt desc,
p_brand,
p_type,
p_size
---- PLAN
Max Per-Host Resource Reservation: Memory=23.06MB Threads=4
Per-Host Resource Estimates: Memory=277MB
PLAN-ROOT SINK
|
07:SORT
| order by: count(ps_suppkey) DESC, p_brand ASC, p_type ASC, p_size ASC
| row-size=65B cardinality=31.92K
|
06:AGGREGATE [FINALIZE]
| output: count(ps_suppkey)
| group by: p_brand, p_type, p_size
| row-size=65B cardinality=31.92K
|
05:AGGREGATE
| group by: p_brand, p_type, p_size, ps_suppkey
| row-size=65B cardinality=31.92K
|
04:HASH JOIN [NULL AWARE LEFT ANTI JOIN]
| hash predicates: ps_suppkey = s_suppkey
| row-size=81B cardinality=31.92K
|
|--02:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| predicates: s_comment LIKE '%Customer%Complaints%'
| row-size=83B cardinality=1.00K
|
03:HASH JOIN [INNER JOIN]
| hash predicates: ps_partkey = p_partkey
| runtime filters: RF000 <- p_partkey
| row-size=81B cardinality=31.92K
|
|--01:SCAN HDFS [tpch.part]
| HDFS partitions=1/1 files=1 size=22.83MB
| predicates: p_size IN (49, 14, 23, 45, 19, 3, 36, 9), p_brand != 'Brand#45', NOT p_type LIKE 'MEDIUM POLISHED%'
| row-size=65B cardinality=8.00K
|
00:SCAN HDFS [tpch.partsupp]
HDFS partitions=1/1 files=1 size=112.71MB
runtime filters: RF000 -> ps_partkey
row-size=16B cardinality=800.00K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=46.81MB Threads=9
Per-Host Resource Estimates: Memory=334MB
PLAN-ROOT SINK
|
14:MERGING-EXCHANGE [UNPARTITIONED]
| order by: count(ps_suppkey) DESC, p_brand ASC, p_type ASC, p_size ASC
|
07:SORT
| order by: count(ps_suppkey) DESC, p_brand ASC, p_type ASC, p_size ASC
| row-size=65B cardinality=31.92K
|
13:AGGREGATE [FINALIZE]
| output: count:merge(ps_suppkey)
| group by: p_brand, p_type, p_size
| row-size=65B cardinality=31.92K
|
12:EXCHANGE [HASH(p_brand,p_type,p_size)]
|
06:AGGREGATE [STREAMING]
| output: count(ps_suppkey)
| group by: p_brand, p_type, p_size
| row-size=65B cardinality=31.92K
|
11:AGGREGATE
| group by: p_brand, p_type, p_size, ps_suppkey
| row-size=65B cardinality=31.92K
|
10:EXCHANGE [HASH(p_brand,p_type,p_size,ps_suppkey)]
|
05:AGGREGATE [STREAMING]
| group by: p_brand, p_type, p_size, ps_suppkey
| row-size=65B cardinality=31.92K
|
04:HASH JOIN [NULL AWARE LEFT ANTI JOIN, BROADCAST]
| hash predicates: ps_suppkey = s_suppkey
| row-size=81B cardinality=31.92K
|
|--09:EXCHANGE [BROADCAST]
| |
| 02:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| predicates: s_comment LIKE '%Customer%Complaints%'
| row-size=83B cardinality=1.00K
|
03:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ps_partkey = p_partkey
| runtime filters: RF000 <- p_partkey
| row-size=81B cardinality=31.92K
|
|--08:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.part]
| HDFS partitions=1/1 files=1 size=22.83MB
| predicates: p_size IN (49, 14, 23, 45, 19, 3, 36, 9), p_brand != 'Brand#45', NOT p_type LIKE 'MEDIUM POLISHED%'
| row-size=65B cardinality=8.00K
|
00:SCAN HDFS [tpch.partsupp]
HDFS partitions=1/1 files=1 size=112.71MB
runtime filters: RF000 -> ps_partkey
row-size=16B cardinality=800.00K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=93.62MB Threads=11
Per-Host Resource Estimates: Memory=392MB
PLAN-ROOT SINK
|
14:MERGING-EXCHANGE [UNPARTITIONED]
| order by: count(ps_suppkey) DESC, p_brand ASC, p_type ASC, p_size ASC
|
07:SORT
| order by: count(ps_suppkey) DESC, p_brand ASC, p_type ASC, p_size ASC
| row-size=65B cardinality=31.92K
|
13:AGGREGATE [FINALIZE]
| output: count:merge(ps_suppkey)
| group by: p_brand, p_type, p_size
| row-size=65B cardinality=31.92K
|
12:EXCHANGE [HASH(p_brand,p_type,p_size)]
|
06:AGGREGATE [STREAMING]
| output: count(ps_suppkey)
| group by: p_brand, p_type, p_size
| row-size=65B cardinality=31.92K
|
11:AGGREGATE
| group by: p_brand, p_type, p_size, ps_suppkey
| row-size=65B cardinality=31.92K
|
10:EXCHANGE [HASH(p_brand,p_type,p_size,ps_suppkey)]
|
05:AGGREGATE [STREAMING]
| group by: p_brand, p_type, p_size, ps_suppkey
| row-size=65B cardinality=31.92K
|
04:HASH JOIN [NULL AWARE LEFT ANTI JOIN, BROADCAST]
| hash predicates: ps_suppkey = s_suppkey
| row-size=81B cardinality=31.92K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: s_suppkey
| |
| 09:EXCHANGE [BROADCAST]
| |
| 02:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| predicates: s_comment LIKE '%Customer%Complaints%'
| row-size=83B cardinality=1.00K
|
03:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: ps_partkey = p_partkey
| runtime filters: RF000 <- p_partkey
| row-size=81B cardinality=31.92K
|
|--JOIN BUILD
| | join-table-id=01 plan-id=02 cohort-id=01
| | build expressions: p_partkey
| |
| 08:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.part]
| HDFS partitions=1/1 files=1 size=22.83MB
| predicates: p_size IN (49, 14, 23, 45, 19, 3, 36, 9), p_brand != 'Brand#45', NOT p_type LIKE 'MEDIUM POLISHED%'
| row-size=65B cardinality=8.00K
|
00:SCAN HDFS [tpch.partsupp]
HDFS partitions=1/1 files=1 size=112.71MB
runtime filters: RF000 -> ps_partkey
row-size=16B cardinality=800.00K
====
# TPCH-Q17
# Q17 - Small-Quantity-Order Revenue Query
select
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,
part
where
p_partkey = l_partkey
and p_brand = 'Brand#23'
and p_container = 'MED BOX'
and l_quantity < (
select
0.2 * avg(l_quantity)
from
lineitem
where
l_partkey = p_partkey
)
---- PLAN
Max Per-Host Resource Reservation: Memory=32.69MB Threads=4
Per-Host Resource Estimates: Memory=606MB
PLAN-ROOT SINK
|
06:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice)
| row-size=16B cardinality=1
|
05:HASH JOIN [RIGHT SEMI JOIN]
| hash predicates: l_partkey = p_partkey
| other join predicates: l_quantity < 0.2 * avg(l_quantity)
| runtime filters: RF000 <- p_partkey
| row-size=72B cardinality=29.93K
|
|--04:HASH JOIN [INNER JOIN]
| | hash predicates: l_partkey = p_partkey
| | runtime filters: RF002 <- p_partkey
| | row-size=72B cardinality=29.93K
| |
| |--01:SCAN HDFS [tpch.part]
| | HDFS partitions=1/1 files=1 size=22.83MB
| | predicates: p_container = 'MED BOX', p_brand = 'Brand#23'
| | row-size=48B cardinality=1.00K
| |
| 00:SCAN HDFS [tpch.lineitem]
| HDFS partitions=1/1 files=1 size=718.94MB
| runtime filters: RF002 -> l_partkey
| row-size=24B cardinality=6.00M
|
03:AGGREGATE [FINALIZE]
| output: avg(l_quantity)
| group by: l_partkey
| row-size=16B cardinality=200.52K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
runtime filters: RF000 -> tpch.lineitem.l_partkey
row-size=16B cardinality=6.00M
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=38.75MB Threads=8
Per-Host Resource Estimates: Memory=639MB
PLAN-ROOT SINK
|
12:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice)
| row-size=16B cardinality=1
|
11:EXCHANGE [UNPARTITIONED]
|
06:AGGREGATE
| output: sum(l_extendedprice)
| row-size=16B cardinality=1
|
05:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED]
| hash predicates: l_partkey = p_partkey
| other join predicates: l_quantity < 0.2 * avg(l_quantity)
| runtime filters: RF000 <- p_partkey
| row-size=72B cardinality=29.93K
|
|--10:EXCHANGE [HASH(p_partkey)]
| |
| 04:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: l_partkey = p_partkey
| | runtime filters: RF002 <- p_partkey
| | row-size=72B cardinality=29.93K
| |
| |--09:EXCHANGE [BROADCAST]
| | |
| | 01:SCAN HDFS [tpch.part]
| | HDFS partitions=1/1 files=1 size=22.83MB
| | predicates: p_container = 'MED BOX', p_brand = 'Brand#23'
| | row-size=48B cardinality=1.00K
| |
| 00:SCAN HDFS [tpch.lineitem]
| HDFS partitions=1/1 files=1 size=718.94MB
| runtime filters: RF002 -> l_partkey
| row-size=24B cardinality=6.00M
|
08:AGGREGATE [FINALIZE]
| output: avg:merge(l_quantity)
| group by: l_partkey
| row-size=16B cardinality=200.52K
|
07:EXCHANGE [HASH(l_partkey)]
|
03:AGGREGATE [STREAMING]
| output: avg(l_quantity)
| group by: l_partkey
| row-size=16B cardinality=200.52K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
runtime filters: RF000 -> tpch.lineitem.l_partkey
row-size=16B cardinality=6.00M
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=75.62MB Threads=9
Per-Host Resource Estimates: Memory=500MB
PLAN-ROOT SINK
|
12:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice)
| row-size=16B cardinality=1
|
11:EXCHANGE [UNPARTITIONED]
|
06:AGGREGATE
| output: sum(l_extendedprice)
| row-size=16B cardinality=1
|
05:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED]
| hash predicates: l_partkey = p_partkey
| other join predicates: l_quantity < 0.2 * avg(l_quantity)
| runtime filters: RF000 <- p_partkey
| row-size=72B cardinality=29.93K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: p_partkey
| |
| 10:EXCHANGE [HASH(p_partkey)]
| |
| 04:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: l_partkey = p_partkey
| | runtime filters: RF002 <- p_partkey
| | row-size=72B cardinality=29.93K
| |
| |--JOIN BUILD
| | | join-table-id=01 plan-id=02 cohort-id=02
| | | build expressions: p_partkey
| | |
| | 09:EXCHANGE [BROADCAST]
| | |
| | 01:SCAN HDFS [tpch.part]
| | HDFS partitions=1/1 files=1 size=22.83MB
| | predicates: p_container = 'MED BOX', p_brand = 'Brand#23'
| | row-size=48B cardinality=1.00K
| |
| 00:SCAN HDFS [tpch.lineitem]
| HDFS partitions=1/1 files=1 size=718.94MB
| runtime filters: RF002 -> l_partkey
| row-size=24B cardinality=6.00M
|
08:AGGREGATE [FINALIZE]
| output: avg:merge(l_quantity)
| group by: l_partkey
| row-size=16B cardinality=200.52K
|
07:EXCHANGE [HASH(l_partkey)]
|
03:AGGREGATE [STREAMING]
| output: avg(l_quantity)
| group by: l_partkey
| row-size=16B cardinality=200.52K
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
runtime filters: RF000 -> tpch.lineitem.l_partkey
row-size=16B cardinality=6.00M
====
# TPCH-Q18
# Q18 - Large Value Customer Query
select
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice,
sum(l_quantity)
from
customer,
orders,
lineitem
where
o_orderkey in (
select
l_orderkey
from
lineitem
group by
l_orderkey
having
sum(l_quantity) > 300
)
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice
order by
o_totalprice desc,
o_orderdate
limit 100
---- PLAN
Max Per-Host Resource Reservation: Memory=111.50MB Threads=5
Per-Host Resource Estimates: Memory=891MB
PLAN-ROOT SINK
|
09:TOP-N [LIMIT=100]
| order by: o_totalprice DESC, o_orderdate ASC
| row-size=92B cardinality=100
|
08:AGGREGATE [FINALIZE]
| output: sum(l_quantity)
| group by: c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
| row-size=92B cardinality=600.12K
|
07:HASH JOIN [LEFT SEMI JOIN]
| hash predicates: o_orderkey = l_orderkey
| runtime filters: RF000 <- l_orderkey
| row-size=100B cardinality=600.12K
|
|--04:AGGREGATE [FINALIZE]
| | output: sum(l_quantity)
| | group by: l_orderkey
| | having: sum(l_quantity) > 300
| | row-size=24B cardinality=156.34K
| |
| 03:SCAN HDFS [tpch.lineitem]
| HDFS partitions=1/1 files=1 size=718.94MB
| row-size=16B cardinality=6.00M
|
06:HASH JOIN [INNER JOIN]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF002 <- c_custkey
| row-size=100B cardinality=5.76M
|
|--00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| row-size=38B cardinality=150.00K
|
05:HASH JOIN [INNER JOIN]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF004 <- o_orderkey
| row-size=62B cardinality=5.76M
|
|--01:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| runtime filters: RF000 -> o_orderkey, RF002 -> o_custkey
| row-size=46B cardinality=1.50M
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
runtime filters: RF000 -> tpch.lineitem.l_orderkey, RF004 -> l_orderkey
row-size=16B cardinality=6.00M
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=220.38MB Threads=11
Per-Host Resource Estimates: Memory=1005MB
PLAN-ROOT SINK
|
17:MERGING-EXCHANGE [UNPARTITIONED]
| order by: o_totalprice DESC, o_orderdate ASC
| limit: 100
|
09:TOP-N [LIMIT=100]
| order by: o_totalprice DESC, o_orderdate ASC
| row-size=92B cardinality=100
|
16:AGGREGATE [FINALIZE]
| output: sum:merge(l_quantity)
| group by: c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
| row-size=92B cardinality=600.12K
|
15:EXCHANGE [HASH(c_name,c_custkey,o_orderkey,o_orderdate,o_totalprice)]
|
08:AGGREGATE [STREAMING]
| output: sum(l_quantity)
| group by: c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
| row-size=92B cardinality=600.12K
|
07:HASH JOIN [LEFT SEMI JOIN, PARTITIONED]
| hash predicates: o_orderkey = l_orderkey
| runtime filters: RF000 <- l_orderkey
| row-size=100B cardinality=600.12K
|
|--14:AGGREGATE [FINALIZE]
| | output: sum:merge(l_quantity)
| | group by: l_orderkey
| | having: sum(l_quantity) > 300
| | row-size=24B cardinality=156.34K
| |
| 13:EXCHANGE [HASH(l_orderkey)]
| |
| 04:AGGREGATE [STREAMING]
| | output: sum(l_quantity)
| | group by: l_orderkey
| | row-size=24B cardinality=1.56M
| |
| 03:SCAN HDFS [tpch.lineitem]
| HDFS partitions=1/1 files=1 size=718.94MB
| row-size=16B cardinality=6.00M
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF002 <- c_custkey
| row-size=100B cardinality=5.76M
|
|--12:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| row-size=38B cardinality=150.00K
|
05:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF004 <- o_orderkey
| row-size=62B cardinality=5.76M
|
|--11:EXCHANGE [HASH(o_orderkey)]
| |
| 01:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| runtime filters: RF000 -> o_orderkey, RF002 -> o_custkey
| row-size=46B cardinality=1.50M
|
10:EXCHANGE [HASH(l_orderkey)]
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
runtime filters: RF000 -> tpch.lineitem.l_orderkey, RF004 -> l_orderkey
row-size=16B cardinality=6.00M
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=268.88MB Threads=13
Per-Host Resource Estimates: Memory=846MB
PLAN-ROOT SINK
|
17:MERGING-EXCHANGE [UNPARTITIONED]
| order by: o_totalprice DESC, o_orderdate ASC
| limit: 100
|
09:TOP-N [LIMIT=100]
| order by: o_totalprice DESC, o_orderdate ASC
| row-size=92B cardinality=100
|
16:AGGREGATE [FINALIZE]
| output: sum:merge(l_quantity)
| group by: c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
| row-size=92B cardinality=600.12K
|
15:EXCHANGE [HASH(c_name,c_custkey,o_orderkey,o_orderdate,o_totalprice)]
|
08:AGGREGATE [STREAMING]
| output: sum(l_quantity)
| group by: c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
| row-size=92B cardinality=600.12K
|
07:HASH JOIN [LEFT SEMI JOIN, PARTITIONED]
| hash predicates: o_orderkey = l_orderkey
| runtime filters: RF000 <- l_orderkey
| row-size=100B cardinality=600.12K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: l_orderkey
| |
| 14:AGGREGATE [FINALIZE]
| | output: sum:merge(l_quantity)
| | group by: l_orderkey
| | having: sum(l_quantity) > 300
| | row-size=24B cardinality=156.34K
| |
| 13:EXCHANGE [HASH(l_orderkey)]
| |
| 04:AGGREGATE [STREAMING]
| | output: sum(l_quantity)
| | group by: l_orderkey
| | row-size=24B cardinality=1.56M
| |
| 03:SCAN HDFS [tpch.lineitem]
| HDFS partitions=1/1 files=1 size=718.94MB
| row-size=16B cardinality=6.00M
|
06:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: o_custkey = c_custkey
| runtime filters: RF002 <- c_custkey
| row-size=100B cardinality=5.76M
|
|--JOIN BUILD
| | join-table-id=01 plan-id=02 cohort-id=01
| | build expressions: c_custkey
| |
| 12:EXCHANGE [BROADCAST]
| |
| 00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| row-size=38B cardinality=150.00K
|
05:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: l_orderkey = o_orderkey
| runtime filters: RF004 <- o_orderkey
| row-size=62B cardinality=5.76M
|
|--JOIN BUILD
| | join-table-id=02 plan-id=03 cohort-id=01
| | build expressions: o_orderkey
| |
| 11:EXCHANGE [HASH(o_orderkey)]
| |
| 01:SCAN HDFS [tpch.orders]
| HDFS partitions=1/1 files=1 size=162.56MB
| runtime filters: RF000 -> o_orderkey, RF002 -> o_custkey
| row-size=46B cardinality=1.50M
|
10:EXCHANGE [HASH(l_orderkey)]
|
02:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
runtime filters: RF000 -> tpch.lineitem.l_orderkey, RF004 -> l_orderkey
row-size=16B cardinality=6.00M
====
# TPCH-Q19
# Q19 - Discounted Revenue Query
select
sum(l_extendedprice * (1 - l_discount)) as revenue
from
lineitem,
part
where
p_partkey = l_partkey
and (
(
p_brand = 'Brand#12'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 1 and l_quantity <= 11
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_brand = 'Brand#23'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= 10 and l_quantity <= 20
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_brand = 'Brand#34'
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity >= 20 and l_quantity <= 30
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
)
---- PLAN
Max Per-Host Resource Reservation: Memory=18.94MB Threads=3
Per-Host Resource Estimates: Memory=331MB
PLAN-ROOT SINK
|
03:AGGREGATE [FINALIZE]
| output: sum(l_extendedprice * (1 - l_discount))
| row-size=16B cardinality=1
|
02:HASH JOIN [INNER JOIN]
| hash predicates: l_partkey = p_partkey
| other predicates: ((p_brand = 'Brand#12' AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND l_quantity >= 1 AND l_quantity <= 11 AND p_size <= 5) OR (p_brand = 'Brand#23' AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND l_quantity >= 10 AND l_quantity <= 20 AND p_size <= 10) OR (p_brand = 'Brand#34' AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND l_quantity >= 20 AND l_quantity <= 30 AND p_size <= 15))
| runtime filters: RF000 <- p_partkey
| row-size=124B cardinality=79.99K
|
|--01:SCAN HDFS [tpch.part]
| HDFS partitions=1/1 files=1 size=22.83MB
| predicates: p_size >= 1
| row-size=52B cardinality=20.00K
|
00:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipmode IN ('AIR', 'AIR REG'), l_shipinstruct = 'DELIVER IN PERSON'
runtime filters: RF000 -> l_partkey
row-size=72B cardinality=801.95K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=18.94MB Threads=5
Per-Host Resource Estimates: Memory=351MB
PLAN-ROOT SINK
|
06:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| row-size=16B cardinality=1
|
05:EXCHANGE [UNPARTITIONED]
|
03:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount))
| row-size=16B cardinality=1
|
02:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = p_partkey
| other predicates: ((p_brand = 'Brand#12' AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND l_quantity >= 1 AND l_quantity <= 11 AND p_size <= 5) OR (p_brand = 'Brand#23' AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND l_quantity >= 10 AND l_quantity <= 20 AND p_size <= 10) OR (p_brand = 'Brand#34' AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND l_quantity >= 20 AND l_quantity <= 30 AND p_size <= 15))
| runtime filters: RF000 <- p_partkey
| row-size=124B cardinality=79.99K
|
|--04:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.part]
| HDFS partitions=1/1 files=1 size=22.83MB
| predicates: p_size >= 1
| row-size=52B cardinality=20.00K
|
00:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipmode IN ('AIR', 'AIR REG'), l_shipinstruct = 'DELIVER IN PERSON'
runtime filters: RF000 -> l_partkey
row-size=72B cardinality=801.95K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=37.88MB Threads=5
Per-Host Resource Estimates: Memory=276MB
PLAN-ROOT SINK
|
06:AGGREGATE [FINALIZE]
| output: sum:merge(l_extendedprice * (1 - l_discount))
| row-size=16B cardinality=1
|
05:EXCHANGE [UNPARTITIONED]
|
03:AGGREGATE
| output: sum(l_extendedprice * (1 - l_discount))
| row-size=16B cardinality=1
|
02:HASH JOIN [INNER JOIN, BROADCAST]
| hash predicates: l_partkey = p_partkey
| other predicates: ((p_brand = 'Brand#12' AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND l_quantity >= 1 AND l_quantity <= 11 AND p_size <= 5) OR (p_brand = 'Brand#23' AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND l_quantity >= 10 AND l_quantity <= 20 AND p_size <= 10) OR (p_brand = 'Brand#34' AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND l_quantity >= 20 AND l_quantity <= 30 AND p_size <= 15))
| runtime filters: RF000 <- p_partkey
| row-size=124B cardinality=79.99K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: p_partkey
| |
| 04:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [tpch.part]
| HDFS partitions=1/1 files=1 size=22.83MB
| predicates: p_size >= 1
| row-size=52B cardinality=20.00K
|
00:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipmode IN ('AIR', 'AIR REG'), l_shipinstruct = 'DELIVER IN PERSON'
runtime filters: RF000 -> l_partkey
row-size=72B cardinality=801.95K
====
# TPCH-Q20
# Q20 - Potential Part Promotion Query
select
s_name,
s_address
from
supplier,
nation
where
s_suppkey in (
select
ps_suppkey
from
partsupp
where
ps_partkey in (
select
p_partkey
from
part
where
p_name like 'forest%'
)
and ps_availqty > (
select
0.5 * sum(l_quantity)
from
lineitem
where
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and l_shipdate >= '1994-01-01'
and l_shipdate < '1995-01-01'
)
)
and s_nationkey = n_nationkey
and n_name = 'CANADA'
order by
s_name
---- PLAN
Max Per-Host Resource Reservation: Memory=71.75MB Threads=6
Per-Host Resource Estimates: Memory=612MB
PLAN-ROOT SINK
|
10:SORT
| order by: s_name ASC
| row-size=67B cardinality=400
|
09:HASH JOIN [RIGHT SEMI JOIN]
| hash predicates: ps_suppkey = s_suppkey
| runtime filters: RF000 <- s_suppkey
| row-size=98B cardinality=400
|
|--08:HASH JOIN [INNER JOIN]
| | hash predicates: s_nationkey = n_nationkey
| | runtime filters: RF008 <- n_nationkey
| | row-size=98B cardinality=400
| |
| |--01:SCAN HDFS [tpch.nation]
| | HDFS partitions=1/1 files=1 size=2.15KB
| | predicates: n_name = 'CANADA'
| | row-size=21B cardinality=1
| |
| 00:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF008 -> s_nationkey
| row-size=77B cardinality=10.00K
|
07:HASH JOIN [RIGHT SEMI JOIN]
| hash predicates: l_partkey = ps_partkey, l_suppkey = ps_suppkey
| other join predicates: ps_availqty > 0.5 * sum(l_quantity)
| runtime filters: RF002 <- ps_partkey, RF003 <- ps_suppkey
| row-size=20B cardinality=79.79K
|
|--06:HASH JOIN [LEFT SEMI JOIN]
| | hash predicates: ps_partkey = p_partkey
| | runtime filters: RF006 <- p_partkey
| | row-size=20B cardinality=79.79K
| |
| |--03:SCAN HDFS [tpch.part]
| | HDFS partitions=1/1 files=1 size=22.83MB
| | predicates: p_name LIKE 'forest%'
| | row-size=53B cardinality=20.00K
| |
| 02:SCAN HDFS [tpch.partsupp]
| HDFS partitions=1/1 files=1 size=112.71MB
| runtime filters: RF000 -> ps_suppkey, RF006 -> ps_partkey
| row-size=20B cardinality=800.00K
|
05:AGGREGATE [FINALIZE]
| output: sum(l_quantity)
| group by: l_partkey, l_suppkey
| row-size=32B cardinality=600.12K
|
04:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate < '1995-01-01', l_shipdate >= '1994-01-01'
runtime filters: RF000 -> tpch.lineitem.l_suppkey, RF002 -> tpch.lineitem.l_partkey, RF003 -> tpch.lineitem.l_suppkey
row-size=46B cardinality=600.12K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=107.63MB Threads=13
Per-Host Resource Estimates: Memory=660MB
PLAN-ROOT SINK
|
18:MERGING-EXCHANGE [UNPARTITIONED]
| order by: s_name ASC
|
10:SORT
| order by: s_name ASC
| row-size=67B cardinality=400
|
09:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED]
| hash predicates: ps_suppkey = s_suppkey
| runtime filters: RF000 <- s_suppkey
| row-size=98B cardinality=400
|
|--17:EXCHANGE [HASH(s_suppkey)]
| |
| 08:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: s_nationkey = n_nationkey
| | runtime filters: RF008 <- n_nationkey
| | row-size=98B cardinality=400
| |
| |--15:EXCHANGE [BROADCAST]
| | |
| | 01:SCAN HDFS [tpch.nation]
| | HDFS partitions=1/1 files=1 size=2.15KB
| | predicates: n_name = 'CANADA'
| | row-size=21B cardinality=1
| |
| 00:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF008 -> s_nationkey
| row-size=77B cardinality=10.00K
|
16:EXCHANGE [HASH(ps_suppkey)]
|
07:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED]
| hash predicates: l_partkey = ps_partkey, l_suppkey = ps_suppkey
| other join predicates: ps_availqty > 0.5 * sum(l_quantity)
| runtime filters: RF002 <- ps_partkey, RF003 <- ps_suppkey
| row-size=20B cardinality=79.79K
|
|--14:EXCHANGE [HASH(ps_partkey,ps_suppkey)]
| |
| 06:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
| | hash predicates: ps_partkey = p_partkey
| | runtime filters: RF006 <- p_partkey
| | row-size=20B cardinality=79.79K
| |
| |--13:EXCHANGE [BROADCAST]
| | |
| | 03:SCAN HDFS [tpch.part]
| | HDFS partitions=1/1 files=1 size=22.83MB
| | predicates: p_name LIKE 'forest%'
| | row-size=53B cardinality=20.00K
| |
| 02:SCAN HDFS [tpch.partsupp]
| HDFS partitions=1/1 files=1 size=112.71MB
| runtime filters: RF000 -> ps_suppkey, RF006 -> ps_partkey
| row-size=20B cardinality=800.00K
|
12:AGGREGATE [FINALIZE]
| output: sum:merge(l_quantity)
| group by: l_partkey, l_suppkey
| row-size=32B cardinality=600.12K
|
11:EXCHANGE [HASH(l_partkey,l_suppkey)]
|
05:AGGREGATE [STREAMING]
| output: sum(l_quantity)
| group by: l_partkey, l_suppkey
| row-size=32B cardinality=600.12K
|
04:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate < '1995-01-01', l_shipdate >= '1994-01-01'
runtime filters: RF000 -> tpch.lineitem.l_suppkey, RF002 -> tpch.lineitem.l_partkey, RF003 -> tpch.lineitem.l_suppkey
row-size=46B cardinality=600.12K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=163.39MB Threads=15
Per-Host Resource Estimates: Memory=609MB
PLAN-ROOT SINK
|
18:MERGING-EXCHANGE [UNPARTITIONED]
| order by: s_name ASC
|
10:SORT
| order by: s_name ASC
| row-size=67B cardinality=400
|
09:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED]
| hash predicates: ps_suppkey = s_suppkey
| runtime filters: RF000 <- s_suppkey
| row-size=98B cardinality=400
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: s_suppkey
| |
| 17:EXCHANGE [HASH(s_suppkey)]
| |
| 08:HASH JOIN [INNER JOIN, BROADCAST]
| | hash predicates: s_nationkey = n_nationkey
| | runtime filters: RF008 <- n_nationkey
| | row-size=98B cardinality=400
| |
| |--JOIN BUILD
| | | join-table-id=01 plan-id=02 cohort-id=02
| | | build expressions: n_nationkey
| | |
| | 15:EXCHANGE [BROADCAST]
| | |
| | 01:SCAN HDFS [tpch.nation]
| | HDFS partitions=1/1 files=1 size=2.15KB
| | predicates: n_name = 'CANADA'
| | row-size=21B cardinality=1
| |
| 00:SCAN HDFS [tpch.supplier]
| HDFS partitions=1/1 files=1 size=1.33MB
| runtime filters: RF008 -> s_nationkey
| row-size=77B cardinality=10.00K
|
16:EXCHANGE [HASH(ps_suppkey)]
|
07:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED]
| hash predicates: l_partkey = ps_partkey, l_suppkey = ps_suppkey
| other join predicates: ps_availqty > 0.5 * sum(l_quantity)
| runtime filters: RF002 <- ps_partkey, RF003 <- ps_suppkey
| row-size=20B cardinality=79.79K
|
|--JOIN BUILD
| | join-table-id=02 plan-id=03 cohort-id=01
| | build expressions: ps_partkey, ps_suppkey
| |
| 14:EXCHANGE [HASH(ps_partkey,ps_suppkey)]
| |
| 06:HASH JOIN [LEFT SEMI JOIN, BROADCAST]
| | hash predicates: ps_partkey = p_partkey
| | runtime filters: RF006 <- p_partkey
| | row-size=20B cardinality=79.79K
| |
| |--JOIN BUILD
| | | join-table-id=03 plan-id=04 cohort-id=03
| | | build expressions: p_partkey
| | |
| | 13:EXCHANGE [BROADCAST]
| | |
| | 03:SCAN HDFS [tpch.part]
| | HDFS partitions=1/1 files=1 size=22.83MB
| | predicates: p_name LIKE 'forest%'
| | row-size=53B cardinality=20.00K
| |
| 02:SCAN HDFS [tpch.partsupp]
| HDFS partitions=1/1 files=1 size=112.71MB
| runtime filters: RF000 -> ps_suppkey, RF006 -> ps_partkey
| row-size=20B cardinality=800.00K
|
12:AGGREGATE [FINALIZE]
| output: sum:merge(l_quantity)
| group by: l_partkey, l_suppkey
| row-size=32B cardinality=600.12K
|
11:EXCHANGE [HASH(l_partkey,l_suppkey)]
|
05:AGGREGATE [STREAMING]
| output: sum(l_quantity)
| group by: l_partkey, l_suppkey
| row-size=32B cardinality=600.12K
|
04:SCAN HDFS [tpch.lineitem]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l_shipdate < '1995-01-01', l_shipdate >= '1994-01-01'
runtime filters: RF000 -> tpch.lineitem.l_suppkey, RF002 -> tpch.lineitem.l_partkey, RF003 -> tpch.lineitem.l_suppkey
row-size=46B cardinality=600.12K
====
# TPCH-Q21
# Q21 - Suppliers Who Kept Orders Waiting Query
select
s_name,
count(*) as numwait
from
supplier,
lineitem l1,
orders,
nation
where
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists (
select
*
from
lineitem l2
where
l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists (
select
*
from
lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = 'SAUDI ARABIA'
group by
s_name
order by
numwait desc,
s_name
limit 100
---- PLAN
Max Per-Host Resource Reservation: Memory=58.88MB Threads=7
Per-Host Resource Estimates: Memory=1.03GB
PLAN-ROOT SINK
|
12:TOP-N [LIMIT=100]
| order by: count(*) DESC, s_name ASC
| row-size=38B cardinality=100
|
11:AGGREGATE [FINALIZE]
| output: count(*)
| group by: s_name
| row-size=38B cardinality=7.68K
|
10:HASH JOIN [RIGHT ANTI JOIN]
| hash predicates: l3.l_orderkey = l1.l_orderkey
| other join predicates: l3.l_suppkey != l1.l_suppkey
| row-size=142B cardinality=7.68K
|
|--09:HASH JOIN [RIGHT SEMI JOIN]
| | hash predicates: l2.l_orderkey = l1.l_orderkey
| | other join predicates: l2.l_suppkey != l1.l_suppkey
| | runtime filters: RF000 <- l1.l_orderkey
| | row-size=142B cardinality=7.68K
| |
| |--08:HASH JOIN [INNER JOIN]
| | | hash predicates: s_nationkey = n_nationkey
| | | runtime filters: RF002 <- n_nationkey
| | | row-size=142B cardinality=7.68K
| | |
| | |--03:SCAN HDFS [tpch.nation]
| | | HDFS partitions=1/1 files=1 size=2.15KB
| | | predicates: n_name = 'SAUDI ARABIA'
| | | row-size=21B cardinality=1
| | |
| | 07:HASH JOIN [INNER JOIN]
| | | hash predicates: l1.l_suppkey = s_suppkey
| | | runtime filters: RF004 <- s_suppkey
| | | row-size=121B cardinality=191.92K
| | |
| | |--00:SCAN HDFS [tpch.supplier]
| | | HDFS partitions=1/1 files=1 size=1.33MB
| | | runtime filters: RF002 -> s_nationkey
| | | row-size=40B cardinality=10.00K
| | |
| | 06:HASH JOIN [INNER JOIN]
| | | hash predicates: l1.l_orderkey = o_orderkey
| | | runtime filters: RF006 <- o_orderkey
| | | row-size=81B cardinality=191.92K
| | |
| | |--02:SCAN HDFS [tpch.orders]
| | | HDFS partitions=1/1 files=1 size=162.56MB
| | | predicates: o_orderstatus = 'F'
| | | row-size=21B cardinality=500.00K
| | |
| | 01:SCAN HDFS [tpch.lineitem l1]
| | HDFS partitions=1/1 files=1 size=718.94MB
| | predicates: l1.l_receiptdate > l1.l_commitdate
| | runtime filters: RF004 -> l1.l_suppkey, RF006 -> l1.l_orderkey
| | row-size=60B cardinality=600.12K
| |
| 04:SCAN HDFS [tpch.lineitem l2]
| HDFS partitions=1/1 files=1 size=718.94MB
| runtime filters: RF000 -> l2.l_orderkey
| row-size=16B cardinality=6.00M
|
05:SCAN HDFS [tpch.lineitem l3]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l3.l_receiptdate > l3.l_commitdate
row-size=60B cardinality=600.12K
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=56.51MB Threads=15
Per-Host Resource Estimates: Memory=1.08GB
PLAN-ROOT SINK
|
21:MERGING-EXCHANGE [UNPARTITIONED]
| order by: count(*) DESC, s_name ASC
| limit: 100
|
12:TOP-N [LIMIT=100]
| order by: count(*) DESC, s_name ASC
| row-size=38B cardinality=100
|
20:AGGREGATE [FINALIZE]
| output: count:merge(*)
| group by: s_name
| row-size=38B cardinality=7.68K
|
19:EXCHANGE [HASH(s_name)]
|
11:AGGREGATE [STREAMING]
| output: count(*)
| group by: s_name
| row-size=38B cardinality=7.68K
|
10:HASH JOIN [RIGHT ANTI JOIN, PARTITIONED]
| hash predicates: l3.l_orderkey = l1.l_orderkey
| other join predicates: l3.l_suppkey != l1.l_suppkey
| row-size=142B cardinality=7.68K
|
|--09:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED]
| | hash predicates: l2.l_orderkey = l1.l_orderkey
| | other join predicates: l2.l_suppkey != l1.l_suppkey
| | runtime filters: RF000 <- l1.l_orderkey
| | row-size=142B cardinality=7.68K
| |
| |--08:HASH JOIN [INNER JOIN, BROADCAST]
| | | hash predicates: s_nationkey = n_nationkey
| | | runtime filters: RF002 <- n_nationkey
| | | row-size=142B cardinality=7.68K
| | |
| | |--16:EXCHANGE [BROADCAST]
| | | |
| | | 03:SCAN HDFS [tpch.nation]
| | | HDFS partitions=1/1 files=1 size=2.15KB
| | | predicates: n_name = 'SAUDI ARABIA'
| | | row-size=21B cardinality=1
| | |
| | 07:HASH JOIN [INNER JOIN, BROADCAST]
| | | hash predicates: l1.l_suppkey = s_suppkey
| | | runtime filters: RF004 <- s_suppkey
| | | row-size=121B cardinality=191.92K
| | |
| | |--15:EXCHANGE [BROADCAST]
| | | |
| | | 00:SCAN HDFS [tpch.supplier]
| | | HDFS partitions=1/1 files=1 size=1.33MB
| | | runtime filters: RF002 -> s_nationkey
| | | row-size=40B cardinality=10.00K
| | |
| | 06:HASH JOIN [INNER JOIN, PARTITIONED]
| | | hash predicates: l1.l_orderkey = o_orderkey
| | | runtime filters: RF006 <- o_orderkey
| | | row-size=81B cardinality=191.92K
| | |
| | |--14:EXCHANGE [HASH(o_orderkey)]
| | | |
| | | 02:SCAN HDFS [tpch.orders]
| | | HDFS partitions=1/1 files=1 size=162.56MB
| | | predicates: o_orderstatus = 'F'
| | | row-size=21B cardinality=500.00K
| | |
| | 13:EXCHANGE [HASH(l1.l_orderkey)]
| | |
| | 01:SCAN HDFS [tpch.lineitem l1]
| | HDFS partitions=1/1 files=1 size=718.94MB
| | predicates: l1.l_receiptdate > l1.l_commitdate
| | runtime filters: RF004 -> l1.l_suppkey, RF006 -> l1.l_orderkey
| | row-size=60B cardinality=600.12K
| |
| 17:EXCHANGE [HASH(l2.l_orderkey)]
| |
| 04:SCAN HDFS [tpch.lineitem l2]
| HDFS partitions=1/1 files=1 size=718.94MB
| runtime filters: RF000 -> l2.l_orderkey
| row-size=16B cardinality=6.00M
|
18:EXCHANGE [HASH(l3.l_orderkey)]
|
05:SCAN HDFS [tpch.lineitem l3]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l3.l_receiptdate > l3.l_commitdate
row-size=60B cardinality=600.12K
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=109.27MB Threads=17
Per-Host Resource Estimates: Memory=863MB
PLAN-ROOT SINK
|
21:MERGING-EXCHANGE [UNPARTITIONED]
| order by: count(*) DESC, s_name ASC
| limit: 100
|
12:TOP-N [LIMIT=100]
| order by: count(*) DESC, s_name ASC
| row-size=38B cardinality=100
|
20:AGGREGATE [FINALIZE]
| output: count:merge(*)
| group by: s_name
| row-size=38B cardinality=7.68K
|
19:EXCHANGE [HASH(s_name)]
|
11:AGGREGATE [STREAMING]
| output: count(*)
| group by: s_name
| row-size=38B cardinality=7.68K
|
10:HASH JOIN [RIGHT ANTI JOIN, PARTITIONED]
| hash predicates: l3.l_orderkey = l1.l_orderkey
| other join predicates: l3.l_suppkey != l1.l_suppkey
| row-size=142B cardinality=7.68K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: l1.l_orderkey
| |
| 09:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED]
| | hash predicates: l2.l_orderkey = l1.l_orderkey
| | other join predicates: l2.l_suppkey != l1.l_suppkey
| | runtime filters: RF000 <- l1.l_orderkey
| | row-size=142B cardinality=7.68K
| |
| |--JOIN BUILD
| | | join-table-id=01 plan-id=02 cohort-id=02
| | | build expressions: l1.l_orderkey
| | |
| | 08:HASH JOIN [INNER JOIN, BROADCAST]
| | | hash predicates: s_nationkey = n_nationkey
| | | runtime filters: RF002 <- n_nationkey
| | | row-size=142B cardinality=7.68K
| | |
| | |--JOIN BUILD
| | | | join-table-id=02 plan-id=03 cohort-id=03
| | | | build expressions: n_nationkey
| | | |
| | | 16:EXCHANGE [BROADCAST]
| | | |
| | | 03:SCAN HDFS [tpch.nation]
| | | HDFS partitions=1/1 files=1 size=2.15KB
| | | predicates: n_name = 'SAUDI ARABIA'
| | | row-size=21B cardinality=1
| | |
| | 07:HASH JOIN [INNER JOIN, BROADCAST]
| | | hash predicates: l1.l_suppkey = s_suppkey
| | | runtime filters: RF004 <- s_suppkey
| | | row-size=121B cardinality=191.92K
| | |
| | |--JOIN BUILD
| | | | join-table-id=03 plan-id=04 cohort-id=03
| | | | build expressions: s_suppkey
| | | |
| | | 15:EXCHANGE [BROADCAST]
| | | |
| | | 00:SCAN HDFS [tpch.supplier]
| | | HDFS partitions=1/1 files=1 size=1.33MB
| | | runtime filters: RF002 -> s_nationkey
| | | row-size=40B cardinality=10.00K
| | |
| | 06:HASH JOIN [INNER JOIN, PARTITIONED]
| | | hash predicates: l1.l_orderkey = o_orderkey
| | | runtime filters: RF006 <- o_orderkey
| | | row-size=81B cardinality=191.92K
| | |
| | |--JOIN BUILD
| | | | join-table-id=04 plan-id=05 cohort-id=03
| | | | build expressions: o_orderkey
| | | |
| | | 14:EXCHANGE [HASH(o_orderkey)]
| | | |
| | | 02:SCAN HDFS [tpch.orders]
| | | HDFS partitions=1/1 files=1 size=162.56MB
| | | predicates: o_orderstatus = 'F'
| | | row-size=21B cardinality=500.00K
| | |
| | 13:EXCHANGE [HASH(l1.l_orderkey)]
| | |
| | 01:SCAN HDFS [tpch.lineitem l1]
| | HDFS partitions=1/1 files=1 size=718.94MB
| | predicates: l1.l_receiptdate > l1.l_commitdate
| | runtime filters: RF004 -> l1.l_suppkey, RF006 -> l1.l_orderkey
| | row-size=60B cardinality=600.12K
| |
| 17:EXCHANGE [HASH(l2.l_orderkey)]
| |
| 04:SCAN HDFS [tpch.lineitem l2]
| HDFS partitions=1/1 files=1 size=718.94MB
| runtime filters: RF000 -> l2.l_orderkey
| row-size=16B cardinality=6.00M
|
18:EXCHANGE [HASH(l3.l_orderkey)]
|
05:SCAN HDFS [tpch.lineitem l3]
HDFS partitions=1/1 files=1 size=718.94MB
predicates: l3.l_receiptdate > l3.l_commitdate
row-size=60B cardinality=600.12K
====
# TPCH-Q22
# Q22 - Global Sales Opportunity Query
select
cntrycode,
count(*) as numcust,
sum(c_acctbal) as totacctbal
from (
select
substr(c_phone, 1, 2) as cntrycode,
c_acctbal
from
customer
where
substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17')
and c_acctbal > (
select
avg(c_acctbal)
from
customer
where
c_acctbal > 0.00
and substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17')
)
and not exists (
select
*
from
orders
where
o_custkey = c_custkey
)
) as custsale
group by
cntrycode
order by
cntrycode
---- PLAN
Max Per-Host Resource Reservation: Memory=24.00MB Threads=4
Per-Host Resource Estimates: Memory=314MB
PLAN-ROOT SINK
|
07:SORT
| order by: cntrycode ASC
| row-size=36B cardinality=15.00K
|
06:AGGREGATE [FINALIZE]
| output: count(*), sum(c_acctbal)
| group by: substr(c_phone, 1, 2)
| row-size=36B cardinality=15.00K
|
05:HASH JOIN [RIGHT ANTI JOIN]
| hash predicates: o_custkey = c_custkey
| row-size=51B cardinality=15.00K
|
|--04:NESTED LOOP JOIN [INNER JOIN]
| | predicates: c_acctbal > avg(c_acctbal)
| | row-size=51B cardinality=15.00K
| |
| |--02:AGGREGATE [FINALIZE]
| | | output: avg(c_acctbal)
| | | row-size=8B cardinality=1
| | |
| | 01:SCAN HDFS [tpch.customer]
| | HDFS partitions=1/1 files=1 size=23.08MB
| | predicates: c_acctbal > 0, substr(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
| | row-size=35B cardinality=15.00K
| |
| 00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| predicates: substr(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
| row-size=43B cardinality=15.00K
|
03:SCAN HDFS [tpch.orders]
HDFS partitions=1/1 files=1 size=162.56MB
row-size=8B cardinality=1.50M
---- DISTRIBUTEDPLAN
Max Per-Host Resource Reservation: Memory=41.88MB Threads=10
Per-Host Resource Estimates: Memory=365MB
PLAN-ROOT SINK
|
15:MERGING-EXCHANGE [UNPARTITIONED]
| order by: cntrycode ASC
|
07:SORT
| order by: cntrycode ASC
| row-size=36B cardinality=15.00K
|
14:AGGREGATE [FINALIZE]
| output: count:merge(*), sum:merge(c_acctbal)
| group by: cntrycode
| row-size=36B cardinality=15.00K
|
13:EXCHANGE [HASH(cntrycode)]
|
06:AGGREGATE [STREAMING]
| output: count(*), sum(c_acctbal)
| group by: substr(c_phone, 1, 2)
| row-size=36B cardinality=15.00K
|
05:HASH JOIN [RIGHT ANTI JOIN, PARTITIONED]
| hash predicates: o_custkey = c_custkey
| row-size=51B cardinality=15.00K
|
|--12:EXCHANGE [HASH(c_custkey)]
| |
| 04:NESTED LOOP JOIN [INNER JOIN, BROADCAST]
| | predicates: c_acctbal > avg(c_acctbal)
| | row-size=51B cardinality=15.00K
| |
| |--10:EXCHANGE [BROADCAST]
| | |
| | 09:AGGREGATE [FINALIZE]
| | | output: avg:merge(c_acctbal)
| | | row-size=8B cardinality=1
| | |
| | 08:EXCHANGE [UNPARTITIONED]
| | |
| | 02:AGGREGATE
| | | output: avg(c_acctbal)
| | | row-size=8B cardinality=1
| | |
| | 01:SCAN HDFS [tpch.customer]
| | HDFS partitions=1/1 files=1 size=23.08MB
| | predicates: c_acctbal > 0, substr(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
| | row-size=35B cardinality=15.00K
| |
| 00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| predicates: substr(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
| row-size=43B cardinality=15.00K
|
11:EXCHANGE [HASH(o_custkey)]
|
03:SCAN HDFS [tpch.orders]
HDFS partitions=1/1 files=1 size=162.56MB
row-size=8B cardinality=1.50M
---- PARALLELPLANS
Max Per-Host Resource Reservation: Memory=83.75MB Threads=12
Per-Host Resource Estimates: Memory=414MB
PLAN-ROOT SINK
|
15:MERGING-EXCHANGE [UNPARTITIONED]
| order by: cntrycode ASC
|
07:SORT
| order by: cntrycode ASC
| row-size=36B cardinality=15.00K
|
14:AGGREGATE [FINALIZE]
| output: count:merge(*), sum:merge(c_acctbal)
| group by: cntrycode
| row-size=36B cardinality=15.00K
|
13:EXCHANGE [HASH(cntrycode)]
|
06:AGGREGATE [STREAMING]
| output: count(*), sum(c_acctbal)
| group by: substr(c_phone, 1, 2)
| row-size=36B cardinality=15.00K
|
05:HASH JOIN [RIGHT ANTI JOIN, PARTITIONED]
| hash predicates: o_custkey = c_custkey
| row-size=51B cardinality=15.00K
|
|--JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: c_custkey
| |
| 12:EXCHANGE [HASH(c_custkey)]
| |
| 04:NESTED LOOP JOIN [INNER JOIN, BROADCAST]
| | join table id: 01
| | predicates: c_acctbal > avg(c_acctbal)
| | row-size=51B cardinality=15.00K
| |
| |--JOIN BUILD
| | | join-table-id=01 plan-id=02 cohort-id=02
| | |
| | 10:EXCHANGE [BROADCAST]
| | |
| | 09:AGGREGATE [FINALIZE]
| | | output: avg:merge(c_acctbal)
| | | row-size=8B cardinality=1
| | |
| | 08:EXCHANGE [UNPARTITIONED]
| | |
| | 02:AGGREGATE
| | | output: avg(c_acctbal)
| | | row-size=8B cardinality=1
| | |
| | 01:SCAN HDFS [tpch.customer]
| | HDFS partitions=1/1 files=1 size=23.08MB
| | predicates: c_acctbal > 0, substr(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
| | row-size=35B cardinality=15.00K
| |
| 00:SCAN HDFS [tpch.customer]
| HDFS partitions=1/1 files=1 size=23.08MB
| predicates: substr(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
| row-size=43B cardinality=15.00K
|
11:EXCHANGE [HASH(o_custkey)]
|
03:SCAN HDFS [tpch.orders]
HDFS partitions=1/1 files=1 size=162.56MB
row-size=8B cardinality=1.50M
====