|
4130 7 SORT cntrycode
4130 7 AGGREGATE countstar, sum GROUP BY HASH LEFT(c_phone17,2)
4589 6321 RIGHT ANTI JOIN HASH ON o_custkey = c_custkey13
17506 18975 │└INNER JOIN LOOP ON c_acctbal18 > AVG(c_acctbal)
1 1 │ │└AGGREGATE avg
34130 38137 │ │ TABLE SCAN customer WHERE c_acctbal >= 1 AND LEFT(c_phone,2) IN(10,17,19,22,23,27,31) AND LEFT(c_phone,2) BETWEEN 10 AND 31
37060 41958 │ TABLE SCAN customer WHERE LEFT(c_phone17,2) IN(10,17,19,22,23,27,31) AND LEFT(c_phone17,2) BETWEEN 10 AND 31
1500000 1500000 TABLE SCAN orders
|
|
- 25 PROJECT cntrycode, a1 AS numcust, a2 AS totacctbal
- 25 SORT cntrycode
- 25 PROJECT cntrycode, a1, a2
- 25 AGGREGATE COUNT() AS a1, SUM(c_acctbal) AS a2 GROUP BY HASH cntrycode
- 22836 PROJECT left(c_phone,2) AS cntrycode, c_acctbal
- 22836 PROJECT c_phone, c_acctbal
- 22836 RIGHT ANTI JOIN HASH ON PROJECTION_528.o_custkey = PROJECTION_525.c_custkey
- 68282 │└PROJECT c_custkey AS c_custkey_right, c_phone, c_acctbal
- 68282 │ PROJECT c_custkey, c_acctbal, c_phone
- 68282 │ TABLE SCAN customer WHERE (c_acctbal > 4979.819810133222) AND TRUE
- 1500000 PROJECT o_custkey
- 1500000 TABLE SCAN orders WHERE o_custkey = o_custkey
|
|
2 7 SORT custsale.cntrycode
2 7 AGGREGATE count_star(), SUM(#1) GROUP BY HASH #0
3 6321 PROJECT cntrycode, c_acctbal
3 6321 PROJECT cntrycode, c_acctbal
3 0 RIGHT SEMI INNER JOIN HASH ON c_custkey IS NOT DISTINCT FROM c_custkey
18 18975 │└AGGREGATE GROUP BY HASH #2
3 6321 │ RIGHT ANTI JOIN HASH ON c_custkey IS NOT DISTINCT FROM c_custkey
- 0 │ │└SCAN EMPTY
180 190744 │ PROJECT c_custkey
180 190744 │ INNER JOIN HASH ON o_custkey = c_custkey
18 0 │ │└SCAN MATERIALISED
1500000 1499950 │ TABLE SCAN orders
19 18975 INNER JOIN LOOP ON CAST(c_acctbal AS DOUBLE) > SUBQUERY
1 1 │└PROJECT CASE WHEN (#1 > 1) THEN ("error"('More than one row returned by a subquery used as an expression - scalar subqueries can only return a single row. Use "SET scalar_subquery_error_on_multiple_rows=false" to revert to previous behavior of returning a random row.')) ELSE #0 END
- 1 │ AGGREGATE "first" #0, count_star()
- 1 │ AGGREGATE AVG(#0)
30000 38137 │ PROJECT c_acctbal
30000 38137 │ TABLE SCAN customer WHERE c_acctbal > 0.000 AND ("left"(c_phone,2) IN('10','17','19','23','22','31','27'))
30000 41958 TABLE SCAN customer WHERE "left"(c_phone,2) IN('10','17','19','23','22','31','27')
|
|
- 7 SORT cntrycode
- 7 SORT cntrycode
- 7 PROJECT cntrycode, numcust, totacctbal
- 7 AGGREGATE COUNT(1), SUM(c_acctbal) GROUP BY HASH cntrycode
- 7 DISTRIBUTE HASH ON cntrycode
- 7 AGGREGATE COUNT(1), SUM(c_acctbal) GROUP BY HASH cntrycode
- 6321 PROJECT cntrycode, c_acctbal
- 6321 INNER JOIN LOOP ON join_proj_push_down_1 > AVG(c_acctbal)
30000 14055 │└PROJECT c_phone, c_acctbal, join_proj_push_down_1
30000 14055 │ LEFT ANTI JOIN HASH ON c_custkey = o_custkey
1500000 1500000 │ │└TABLE SCAN orders
30000 41958 │ DISTRIBUTE GATHER
30000 41958 │ FILTER left(c_phone,2) IN('10','17','19','23','22','31','27')
150000 150000 │ TABLE SCAN customer WHERE left(c_phone,2) IN('10','17','19','23','22','31','27')
1 1 AGGREGATE AVG(c_acctbal)
30000 10 DISTRIBUTE GATHER
30000 10 AGGREGATE AVG(c_acctbal)
30000 38137 FILTER (c_acctbal > 0.00) AND left(c_phone,2) IN('10','17','19','23','22','31','27')
150000 150000 TABLE SCAN customer WHERE (c_acctbal > 0.00) AND left(c_phone,2) IN('10','17','19','23','22','31','27')
|
|
150000 1 SEQUENCE
150000 7 ├─SORT custsale.cntrycode ASC NULLS FIRST
1 14 │ DISTRIBUTE HASH ON custsale.cntrycode
1 14 │ AGGREGATE COUNT(1), SUM(custsale.c_acctbal) GROUP BY HASH custsale.cntrycode
1 7 │ DISTRIBUTE HASH ON custsale.cntrycode
1 7 │ AGGREGATE COUNT(1), SUM(custsale.c_acctbal) GROUP BY HASH custsale.cntrycode
150000 6321 │ LEFT ANTI JOIN HASH ON c_custkey = o_custkey
150000 1500000 │ │└DISTRIBUTE HASH ON o_custkey
150000 1500000 │ │ TABLE SCAN orders
1500000 18975 │ TABLE SCAN customer WHERE ephemeralsubstring(c_phone,1,2) IN('10','17','19','23','22','31','27')
150000 1 └─AGGREGATE AVG(c_acctbal)
150000 1 DISTRIBUTE GATHER
150000 1 AGGREGATE AVG(c_acctbal)
150000 38137 TABLE SCAN customer WHERE (c_acctbal > 0.00BD) AND (ephemeralsubstring(c_phone,1,2) IN('10','17','19','23','22','31','27'))
|
|
1 7 PROJECT CONVERT_IMPLICIT(int,Expr1025,0) AS Expr1013
1 7 AGGREGATE COUNT(*) AS Expr1025, SUM(c_acctbal) AS Expr1014 GROUP BY SORT Expr1012
1 6317 SORT Expr1012
1 6317 LEFT ANTI JOIN LOOP ON c_custkey = o_custkey
1 12650 │└TABLE SEEK orders
1 18967 INNER JOIN LOOP ON Expr1012 = '27' OR Expr1012 = '31' OR Expr1012 = '22' OR Expr1012 = '23' OR Expr1012 = '19' OR Expr1012 = '17' OR Expr1012 = '10'
1 41958 │└FILTER Expr1012 = '27' OR Expr1012 = '31' OR Expr1012 = '22' OR Expr1012 = '23' OR Expr1012 = '19' OR Expr1012 = '17' OR Expr1012 = '10'
150000 150000 │ PROJECT substring(c_phone,1,2) AS Expr1012
150000 150000 │ TABLE SCAN customer
1 1 PROJECT CASE WHEN Expr1023 = 0 THEN NULL ELSE Expr1024 / CONVERT_IMPLICIT(decimal(19,0),Expr1023,0) END AS Expr1006
1 1 AGGREGATE COUNT(*) AS Expr1023, SUM(c_acctbal) AS Expr1024
2585 38133 FILTER Expr1015 = '27' OR Expr1015 = '31' OR Expr1015 = '22' OR Expr1015 = '23' OR Expr1015 = '19' OR Expr1015 = '17' OR Expr1015 = '10'
2585 136301 PROJECT substring(c_phone,1,2) AS Expr1015
2585 136301 TABLE SCAN customer WHERE c_acctbal > 0.00
|
|
- 7 PROJECT expr_gid AS cntrycode, count AS numcust, sum AS totacctbal
- 7 SORT expr_gid
- 7 AGGREGATE COUNT(count_14) AS count, SUM(sum_15) AS sum GROUP BY HASH expr_gid
- 7 DISTRIBUTE HASH ON expr_gid
- 7 AGGREGATE COUNT(*) AS count_14, SUM(c_acctbal) AS sum_15 GROUP BY PARTIAL expr_gid
- 6317 PROJECT c_acctbal, substring(c_phone,1,2) AS expr_gid
- 18967 LEFT ANTI JOIN HASH ON c_custkey = o_custkey
- 99996 │└DISTRIBUTE HASH ON o_custkey
- 99996 │ PROJECT true AS subquerytrue, o_custkey
- 99996 │ GROUP BY HASH o_custkey
1500000 99996 │ DISTRIBUTE HASH ON o_custkey
1500000 99996 │ GROUP BY PARTIAL o_custkey
1500000 1500000 │ TABLE SCAN orders
- 18967 FILTER c_acctbal > avg
135000 41958 CROSS JOIN LOOP
135000 41958 │└FILTER substring(c_phone,1,2) IN('10','17','19','23','22','31','27')
135000 41958 │ TABLE SCAN customer
1 1 DISTRIBUTE GATHER
1 1 AGGREGATE AVG(avg_16) AS avg
122727 1 DISTRIBUTE GATHER
122727 1 AGGREGATE AVG(c_acctbal_5) AS avg_16
122727 38133 PROJECT c_acctbal AS c_acctbal_5
122727 38133 FILTER (c_acctbal > 0) AND (substring(c_phone,1,2) IN('10','17','19','23','22','31','27'))
122727 38133 TABLE SCAN customer
|
|
757 7 SEQUENCE
1 1 ├─AGGREGATE AVG(c_acctbal)
3 3 │ AGGREGATE PARTIAL AVG(c_acctbal)
5967 38136 │ TABLE SCAN customer AS customer_1 WHERE (customer_1.c_acctbal > 0.00) AND (LEFT(customer_1.c_phone,2) IN('10','17','19','23','22','31','27'))
757 7 └─AGGREGATE COUNT(*), SUM(c_acctbal) GROUP BY SORT LEFT(c_phone,2)
945 6321 SORT LEFT(c_phone,2)
945 6321 LEFT ANTI JOIN LOOP ON o_custkey = c_custkey
2187 18975 │└TABLE SCAN customer AS customer WHERE (c_acctbal > (InitPlan 1) .col1) AND (LEFT(c_phone,2) IN('10','17','19','23','22','31','27'))
341550 18975 TABLE SEEK orders AS orders
|
