Planner — TPC-H — TPCH-Q20

/* TPC-H Q20 */
SELECT s_name,
       s_address
FROM tpch_sf1.supplier
INNER JOIN tpch_sf1.nation
    ON s_nationkey = n_nationkey
WHERE s_suppkey IN (SELECT ps_suppkey
                    FROM tpch_sf1.partsupp
                    WHERE ps_partkey IN (SELECT p_partkey
                                         FROM tpch_sf1.part AS p_inner
                                         WHERE p_name LIKE 'almond%')
                      AND ps_availqty > (SELECT 0.5 * SUM(l_quantity) AS qty
                                         FROM tpch_sf1.lineitem as li_inner
                                         WHERE l_partkey = ps_partkey
                                           AND l_suppkey = ps_suppkey
                                           AND l_shipdate >= '1993-01-01'
                                           AND l_shipdate < '1994-01-01'))
  AND n_name = 'KENYA'
ORDER BY s_name

Engine Compare

Accuracy chart, rows processed ?
Scan Scan	Seek Seek	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
Estimation Error Est Err 515,786 516K Rank		Estimation Error Est Err 21,351 21K Rank	Estimation Error Est Err 158 158 Rank	Estimation Error Est Err 10,418 10K Rank	Estimation Error Est Err 498,728 499K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,720,946 1.7M Rank		Estimation Error Est Err 552,941 553K Rank	Estimation Error Est Err 376 376 Rank	Estimation Error Est Err 800,376 800K Rank	Estimation Error Est Err 908,721 909K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,716,910 1.7M Rank		Estimation Error Est Err 819,418 819K Rank	Estimation Error Est Err 158 158 Rank	Estimation Error Est Err 558,023 558K Rank	Estimation Error Est Err 1,587,744 1.6M Rank	Estimation Error Est Err 1,249,113 1.2M Rank
Estimation Error Est Err 7,011,240 7M Rank		Estimation Error Est Err 18,544 19K Rank	Estimation Error Est Err 316 316 Rank	Estimation Error Est Err 1,358,969 1.4M Rank	Estimation Error Est Err 1,767,927 1.8M Rank	Estimation Error Est Err 1,080,728 1.1M Rank
Estimation Error Est Err 925,588 926K Rank		Estimation Error Est Err 947,183 947K Rank	Estimation Error Est Err 158 158 Rank	Estimation Error Est Err 37,470 37K Rank	Estimation Error Est Err 10,030 10K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 18,680 19K Rank	Estimation Error Est Err 16,455 16K Rank	Estimation Error Est Err 21,328 21K Rank	Estimation Error Est Err 158 158 Rank	Estimation Error Est Err 12,807 13K Rank	Estimation Error Est Err 10,052 10K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 21,309 21K Rank		Estimation Error Est Err 12,387 12K Rank	Estimation Error Est Err 158 158 Rank	Estimation Error Est Err 21,110 21K Rank	Estimation Error Est Err 10,030 10K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 920,000 920K Rank		Estimation Error Est Err 27,004 27K Rank	Estimation Error Est Err 158 158 Rank	Estimation Error Est Err 565,449 565K Rank	Estimation Error Est Err 1,365,029 1.4M Rank	Estimation Error Est Err 684,005 684K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 256 158 SORT s_name 256 158 RIGHT SEMI INNER JOIN HASH ON s_suppkey = ps_suppkey 400 376 │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │└TABLE SCAN nation WHERE n_name = KENYA 10000 10000 │ TABLE SCAN supplier 306 3255 INNER JOIN HASH ON ps_suppkey = l_suppkey58 AND ps_partkey = l_partkey57 AND ps_availqty > 5 * SUM(l_quantity) 532 4833 │└LEFT SEMI JOIN HASH ON ps_partkey = p_partkey 1562 2224 │ │└TABLE SCAN part WHERE p_name) almond 71699 4833 │ TABLE SCAN partsupp 305 3263 AGGREGATE sum GROUP BY HASH l_partkey, l_suppkey 310159 498728 TABLE SCAN lineitem WHERE l_shipdate BETWEEN 2448989 AND 2449353
	Estimate Actual Operator - 2224 SEQUENCE - 376 ├─PROJECT s_name, s_address - 376 │ SORT s_name - 376 │ PROJECT s_name, s_address - 376 │ PROJECT s_name, s_address - 376 │ INNER JOIN HASH ON PROJECTION_443.s_nationkey = PROJECTION_440.n_nationkey - 1 │ │└PROJECT n_nationkey - 1 │ │ PROJECT n_nationkey - 1 │ │ TABLE SCAN nation WHERE n_name = 'KENYA' - 10000 │ PROJECT s_nationkey, s_name, s_address - 10000 │ PROJECT s_nationkey, s_name, s_address - 10000 │ TABLE SCAN supplier WHERE TRUE - 541794 ├─PROJECT ps_suppkey - 541794 │ FILTER ps_availqty > a10 - 542941 │ PROJECT a10, ps_suppkey, ps_availqty - 542941 │ INNER JOIN HASH ON tuple(PROJECTION_453.l_partkey,PROJECTION_453.l_suppkey) = tuple(PROJECTION_450.ps_partkey,PROJECTION_450.ps_suppkey) - 800000 │ │└PROJECT ps_partkey AS ps_partkey_right, ps_suppkey AS ps_suppkey_right, ps_availqty - 800000 │ │ PROJECT ps_partkey, ps_suppkey, ps_availqty - 800000 │ │ TABLE SCAN partsupp WHERE TRUE - 542941 │ PROJECT ps_partkey, ps_suppkey, a10 - 542941 │ PROJECT l_partkey AS ps_partkey, l_suppkey AS ps_suppkey, 0.5 * a11 AS a10 - 542941 │ AGGREGATE SUM(l_quantity) AS a11 GROUP BY HASH l_partkey, l_suppkey - 908721 │ PROJECT l_quantity, l_partkey, l_suppkey, l_shipdate, l_partkey, l_suppkey - 908721 │ TABLE SCAN lineitem WHERE (l_shipdate >= '1993-01-01') AND (l_shipdate < '1994-01-01') AND (l_suppkey = l_suppkey) AND (l_partkey = l_partkey) AND TRUE - 2224 └─PROJECT p_partkey - 2224 PROJECT p_partkey - 2224 TABLE SCAN part WHERE startsWith(p_name,'almond')
	Estimate Actual Operator 76 158 SORT s_name 76 158 RIGHT SEMI INNER JOIN HASH ON #0 = s_suppkey 384 376 │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │└TABLE SCAN nation WHERE n_name = 'KENYA' 10000 376 │ TABLE SCAN supplier 160000 6028 FILTER CAST(ps_availqty AS DECIMAL(38,3)) > SUBQUERY 0 0 RIGHT SEMI INNER JOIN HASH ON ps_suppkey IS NOT DISTINCT FROM ps_suppkey AND ps_partkey IS NOT DISTINCT FROM ps_partkey 159973 8896 │└AGGREGATE GROUP BY HASH #2, #0 0 8896 │ RIGHT OUTER JOIN HASH ON ps_suppkey IS NOT DISTINCT FROM ps_suppkey AND ps_partkey IS NOT DISTINCT FROM ps_partkey - 0 │ │└SCAN EMPTY 251666 6043 │ PROJECT qty, ps_suppkey, ps_partkey 251666 6043 │ AGGREGATE SUM(#2) GROUP BY HASH #0, #1 503333 10030 │ PROJECT ps_suppkey, ps_partkey, l_quantity 503333 10030 │ INNER JOIN HASH ON l_partkey = ps_partkey AND l_suppkey = ps_suppkey 159973 0 │ │└SCAN MATERIALISED 1200243 908627 │ TABLE SCAN lineitem WHERE l_shipdate >= '1993-01-01' AND l_shipdate < '1994-01-01' 160000 8896 LEFT SEMI JOIN HASH ON ps_partkey = #0 40000 2224 │└TABLE SCAN part WHERE p_name >= 'almond' AND p_name < 'almone' 800000 14360 TABLE SCAN partsupp
	Estimate Actual Operator 2000 158 SORT s_name 2000 158 SORT s_name 2000 158 LEFT SEMI JOIN HASH ON s_suppkey = ps_suppkey 96019440 6028 │└INNER JOIN HASH ON ps_partkey = l_partkey AND ps_suppkey = l_suppkey AND (CAST(ps_availqty AS FLOAT64) > qty) 1200243 542941 │ │└PROJECT qty, l_partkey, l_suppkey 1200243 542941 │ │ AGGREGATE SUM(l_quantity) GROUP BY HASH l_partkey, l_suppkey 1200243 859206 │ │ DISTRIBUTE HASH ON l_partkey, l_suppkey 1200243 859206 │ │ AGGREGATE SUM(l_quantity) GROUP BY HASH l_partkey, l_suppkey 1200243 908721 │ │ FILTER (l_shipdate >= DATE'1993-01-01') AND (l_shipdate < DATE'1994-01-01') 6001215 6001215 │ │ TABLE SCAN lineitem WHERE ((l_shipdate >= DATE'1993-01-01') AND (l_shipdate < DATE'1994-01-01')) AND CASE MOD(HASH_REPARTITION(l_partkey,l_suppkey),10) WHEN 0 THEN ((((l_partkey >= 113) AND (l_partkey <= 199955)) AND ((l_suppkey >= 1) AND (l_suppkey <= 9988))) AND TRUE) WHEN 1 THEN ((((l_partkey >= 167) AND (l_partkey <= 199882)) AND ((l_suppkey >= 1) AND (l_suppkey <= 9986))) AND TRUE) WHEN 2 THEN ((((l_partkey >= 113) AND (l_partkey <= 199821)) AND ((l_suppkey >=... 800000 8896 │ DISTRIBUTE HASH ON ps_partkey, ps_suppkey 800000 8896 │ RIGHT SEMI INNER JOIN HASH ON p_partkey = ps_partkey 800000 800000 │ │└TABLE SCAN partsupp 40000 2224 │ DISTRIBUTE GATHER 40000 2224 │ FILTER p_name LIKE 'almond%' 200000 200000 │ DISTRIBUTE ROUND ROBIN 200000 200000 │ TABLE SCAN part WHERE p_name LIKE 'almond%' 2000 376 DISTRIBUTE GATHER 2000 376 INNER JOIN HASH ON n_nationkey = s_nationkey 10000 10000 │└DISTRIBUTE ROUND ROBIN 10000 10000 │ TABLE SCAN supplier WHERE ((s_nationkey >= 14) AND (s_nationkey <= 14)) AND s_nationkey IN 14 5 1 DISTRIBUTE GATHER 5 1 FILTER n_name = 'KENYA' 25 25 DISTRIBUTE ROUND ROBIN 25 25 TABLE SCAN nation WHERE n_name = 'KENYA'
	Estimate Actual Operator 385 158 SORT s_name ASC NULLS FIRST 385 158 INNER JOIN HASH ON s_nationkey = n_nationkey 385 1 │└DISTRIBUTE GATHER 10000 1 │ TABLE SCAN nation WHERE n_name = 'KENYA' 10000 4558 LEFT SEMI JOIN HASH ON s_suppkey = ps_suppkey 10000 6028 │└DISTRIBUTE GATHER 800000 6028 │ LEFT SEMI JOIN HASH ON ps_partkey = li_inner.l_partkey 800000 542941 │ │└DISTRIBUTE GATHER 140000 542941 │ │ FILTER 6000000 542941 │ │ AGGREGATE SUM(li_inner.l_quantity) GROUP BY HASH li_inner.l_partkey, li_inner.l_suppkey 6000000 679023 │ │ DISTRIBUTE HASH ON li_inner.l_partkey, li_inner.l_suppkey 140000 679023 │ │ AGGREGATE SUM(li_inner.l_quantity) GROUP BY HASH li_inner.l_partkey, li_inner.l_suppkey 25 908721 │ │ TABLE SCAN lineitem WHERE (li_inner.l_shipdate >= DATE'1993-01-01') AND (li_inner.l_shipdate < DATE'1994-01-01') 800000 8896 │ DISTRIBUTE HASH ON ps_partkey, ps_suppkey 800000 8896 │ LEFT SEMI JOIN HASH ON ps_partkey = p_inner.p_partkey 800000 2224 │ │└DISTRIBUTE GATHER 6000000 2224 │ │ TABLE SCAN part WHERE startswith(p_inner.p_name,'almond') 200000 795964 │ TABLE SCAN partsupp 10000 10000 DISTRIBUTE HASH ON s_suppkey 800000 10000 TABLE SCAN supplier
	Estimate Actual Operator 51 158 SORT s_name 51 158 RIGHT SEMI INNER JOIN HASH ON s_suppkey = ps_suppkey 1377 6028 │└INNER JOIN HASH ON ps_partkey = p_inner.p_partkey 2266 2224 │ │└TABLE SCAN part AS p_inner WHERE p_name as p_name LIKE 'almond%' 1210 6028 │ INNER JOIN HASH ON l_partkey as l_partkey = ps_partkey AND l_suppkey as l_suppkey = ps_suppkey AND ps_availqty > 0.5 * Expr1013 8000 8896 │ │└TABLE SCAN partsupp WHERE BLOOM(ps_partkey) 906 6043 │ AGGREGATE SUM(l_quantity as l_quantity) AS Expr1013 GROUP BY HASH l_partkey, l_suppkey 907 10030 │ TABLE SCAN lineitem AS li_inner WHERE l_shipdate as l_shipdate >= '1993-01-01' AND l_shipdate as l_shipdate < '1994-01-01' AND BLOOM(l_partkey as l_partkey) AND BLOOM(l_suppkey as l_suppkey) 40 158 INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │└TABLE SCAN nation WHERE n_name = 'KENYA' 100 158 TABLE SCAN supplier WHERE BLOOM(s_nationkey) AND BLOOM(s_suppkey)
	Estimate Actual Operator - 158 SORT s_name - 158 INNER JOIN HASH ON s_suppkey = ps_suppkey - 4558 │└GROUP BY HASH ps_suppkey - 4558 │ DISTRIBUTE HASH ON ps_suppkey - 4558 │ GROUP BY PARTIAL ps_suppkey - 6028 │ FILTER CAST(ps_availqty AS decimal(38,3)) > (COALESCE(sum,sum_26) * 0.5) - 8896 │ CROSS JOIN LOOP - 8896 │ │└LEFT OUTER JOIN HASH ON (ps_partkey = l_partkey) AND (ps_suppkey = l_suppkey) - 542941 │ │ │└AGGREGATE SUM(sum_27) AS sum GROUP BY HASH l_partkey, l_suppkey 867502 674997 │ │ │ DISTRIBUTE HASH ON l_partkey, l_suppkey 867502 674997 │ │ │ AGGREGATE SUM(l_quantity) AS sum_27 GROUP BY PARTIAL l_partkey, l_suppkey 867502 674997 │ │ │ FILTER non_null 867502 908721 │ │ │ PROJECT l_quantity, l_partkey, l_suppkey, true AS non_null 867502 908721 │ │ │ FILTER (l_shipdate >= '1993-01-01') AND (l_shipdate < '1994-01-01') 867502 908721 │ │ │ TABLE SCAN lineitem - 8896 │ │ INNER JOIN HASH ON ps_partkey = p_partkey - 2224 │ │ │└DISTRIBUTE GATHER - 2224 │ │ │ GROUP BY HASH p_partkey 180000 2224 │ │ │ DISTRIBUTE HASH ON p_partkey 180000 2224 │ │ │ GROUP BY PARTIAL p_partkey 180000 2224 │ │ │ FILTER (p_name >= 'almond') AND (p_name < 'almone') AND (p_name LIKE 'almond%') 180000 2224 │ │ │ TABLE SCAN part 800000 8896 │ │ TABLE SCAN partsupp 1 1 │ DISTRIBUTE GATHER 1 1 │ AGGREGATE SUM(null_value) AS sum_26 1 1 │ FILTER null_25 1 1 │ PROJECT 0.0 AS null_value, false AS null_25 1 1 │ SELECT - 158 INNER JOIN HASH ON s_nationkey = n_nationkey 25 1 │└DISTRIBUTE GATHER 25 1 │ FILTER n_name = 'KENYA' 25 1 │ TABLE SCAN nation 10000 158 TABLE SCAN supplier
	Estimate Actual Operator 108 158 SORT s_name 108 158 RIGHT SEMI INNER JOIN HASH ON ps_suppkey = s_suppkey 400 376 │└INNER JOIN LOOP ON s_nationkey = n_nationkey 1 1 │ │└TABLE SCAN nation AS nation WHERE n_name = 'KENYA' 400 376 │ TABLE SEEK supplier AS supplier 2693 6028 INNER JOIN LOOP ON ps_partkey = p_partkey 2020 2224 │└TABLE SCAN part AS p_inner WHERE p_inner.p_name LIKE 'almond%' 2224 6027 LEFT SEMI JOIN LOOP ON (li_inner.l_partkey = ps_partkey) AND (li_inner.l_suppkey = ps_suppkey) 2224 6027 │└TABLE SEEK partsupp AS partsupp WHERE ps_availqty > (SubPlan 1) 8896 8896 AGGREGATE 0.5 * SUM(l_quantity) 8896 10052 TABLE SEEK lineitem AS li_inner WHERE (li_inner.l_shipdate >= '1993-01-01') AND (li_inner.l_shipdate < '1994-01-01')