 DuckDB |
- 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 #11
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')
|
 Databricks |
150000 1 SEQUENCE
150000 7 ├─SORT custsale.cntrycode ASC NULLS FIRST
1 7 │ DISTRIBUTE HASH ON custsale.cntrycode ASC NULLS FIRST
1 7 │ 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 6317 │ LEFT ANTI JOIN HASH ON c_custkey = o_custkey
150000 18967 │ │└TABLE SCAN customer WHERE in(ephemeralsubstring(c_phone,1,2),'10'collate UTF8_BINARY,'17'collate UTF8_BINARY,'19'collate UTF8_BINARY,'23'collate UTF8_BINARY,'22'collate UTF8_BINARY,'31'collate UTF8_BINARY,'27'collate UTF8_BINARY)
150000 1500000 │ DISTRIBUTE HASH ON o_custkey
1500000 1500000 │ TABLE SCAN orders
150000 1 └─AGGREGATE AVG(unscaledvalue c_acctbal)
150000 1 DISTRIBUTE GATHER
150000 1 AGGREGATE AVG(unscaledvalue c_acctbal)
150000 38133 TABLE SCAN customer WHERE (c_acctbal > 0BD) AND in(ephemeralsubstring(c_phone,1,2),'10'collate UTF8_BINARY,'17'collate UTF8_BINARY,'19'collate UTF8_BINARY,'23'collate UTF8_BINARY,'22'collate UTF8_BINARY,'31'collate UTF8_BINARY,'27'collate UTF8_BINARY)
|
 ClickHouse |
- 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
|
 PostgreSQL |
756 7 SEQUENCE
1 1 ├─AGGREGATE AVG(c_acctbal)
3 3 │ AGGREGATE PARTIAL AVG(c_acctbal)
5958 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'))
756 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
|
 Trino |
- 7 PROJECT expr_gid AS cntrycode, count AS numcust, sum AS totacctbal
- 7 SORT expr_gid
- 7 AGGREGATE COUNT(count_10) AS count, SUM(sum_11) AS sum GROUP BY HASH expr_gid
- 7 DISTRIBUTE HASH ON expr_gid
- 7 AGGREGATE COUNT(*) AS count_10, SUM(c_acctbal) AS sum_11 GROUP BY PARTIAL expr_gid
- 6321 PROJECT c_acctbal, substring(c_phone,1,2) AS expr_gid
- 6321 LEFT ANTI JOIN HASH ON c_custkey = o_custkey
99996 99996 │└DISTRIBUTE HASH ON o_custkey
99996 99996 │ PROJECT true AS subquerytrue, o_custkey
99996 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
- 18975 FILTER c_acctbal > avg
135000 41958 CROSS JOIN LOOP
150000 41958 │└FILTER substring(c_phone,1,2) IN('10','17','19','23','22','31','27')
150000 150000 │ TABLE SCAN customer
1 1 DISTRIBUTE GATHER
1 1 AGGREGATE AVG(avg_12) AS avg
122727 1 DISTRIBUTE GATHER
122727 1 AGGREGATE AVG(c_acctbal_6) AS avg_12
150000 38137 PROJECT c_acctbal AS c_acctbal_6
150000 38137 FILTER (c_acctbal > 0.0) AND (substring(c_phone,1,2) IN('10','17','19','23','22','31','27'))
150000 150000 TABLE SCAN customer
|
 SQL Server |
231 7 SORT Expr1012
231 7 PROJECT CONVERT_IMPLICIT(int,Expr1028,0) AS Expr1013
231 7 AGGREGATE COUNT(*) AS Expr1028, SUM(c_acctbal) AS Expr1014 GROUP BY HASH Expr1012
1721 6317 INNER JOIN HASH ON o_custkey = c_custkey
4709 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'
10375 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 Expr1026 = 0 THEN NULL ELSE Expr1027 / CONVERT_IMPLICIT(decimal(19,0),Expr1026,0) END AS Expr1006
1 1 │ AGGREGATE COUNT(*) AS Expr1026, SUM(c_acctbal) AS Expr1027
9894 38133 │ FILTER Expr1015 = '27' OR Expr1015 = '31' OR Expr1015 = '22' OR Expr1015 = '23' OR Expr1015 = '19' OR Expr1015 = '17' OR Expr1015 = '10'
9894 136301 │ PROJECT substring(c_phone,1,2) AS Expr1015
9894 136301 │ TABLE SCAN customer WHERE c_acctbal > 0.00
150000 190674 TABLE SCAN orders WHERE BLOOM(o_custkey)
|