Planner — TPC-H — TPCH-Q11

/* TPC-H Q11 */
SELECT ps_partkey,
       SUM(ps_supplycost * ps_availqty) AS v
FROM tpch_sf1.partsupp
INNER JOIN tpch_sf1.supplier
    ON ps_suppkey = s_suppkey
INNER JOIN tpch_sf1.nation
    ON s_nationkey = n_nationkey
WHERE n_name = 'JAPAN'
GROUP BY ps_partkey
HAVING SUM(ps_supplycost * ps_availqty)
           > (SELECT SUM(ps_supplycost * ps_availqty) * 0.0001
              FROM tpch_sf1.partsupp
              INNER JOIN tpch_sf1.supplier
                  ON ps_suppkey = s_suppkey
              INNER JOIN tpch_sf1.nation
                  ON s_nationkey = n_nationkey
              WHERE n_name = 'JAPAN')
ORDER BY v DESC

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 840,161 840K Rank		Estimation Error Est Err 838,515 839K Rank	Estimation Error Est Err 1,225 1.2K Rank	Estimation Error Est Err 31,762 32K Rank	Estimation Error Est Err 60,320 60K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 810,001 810K Rank		Estimation Error Est Err 810,000 810K Rank	Estimation Error Est Err 1,225 1.2K Rank	Estimation Error Est Err 30,537 31K Rank	Estimation Error Est Err 30,160 30K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,604,219 1.6M Rank		Estimation Error Est Err 1,598,155 1.6M Rank	Estimation Error Est Err 1,225 1.2K Rank	Estimation Error Est Err 60,697 61K Rank	Estimation Error Est Err 88,836 89K Rank	Estimation Error Est Err 43,470 43K Rank
Estimation Error Est Err 1,620,050 1.6M Rank		Estimation Error Est Err 1,661,545 1.7M Rank	Estimation Error Est Err 2,450 2.5K Rank	Estimation Error Est Err 3,228,515 3.2M Rank	Estimation Error Est Err 88,845 89K Rank	Estimation Error Est Err 28,577 29K Rank
Estimation Error Est Err 858,258 858K Rank		Estimation Error Est Err 826,452 826K Rank	Estimation Error Est Err 1,225 1.2K Rank	Estimation Error Est Err 31,762 32K Rank	Estimation Error Est Err 60,321 60K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 61,076 61K Rank	Estimation Error Est Err 61,074 61K Rank	Estimation Error Est Err 61,074 61K Rank	Estimation Error Est Err 1,225 1.2K Rank	Estimation Error Est Err 61,074 61K Rank	Estimation Error Est Err 60,320 60K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 61,076 61K Rank		Estimation Error Est Err 62,299 62K Rank	Estimation Error Est Err 1,225 1.2K Rank	Estimation Error Est Err 89,589 90K Rank	Estimation Error Est Err 60,320 60K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 61,076 61K Rank		Estimation Error Est Err 149,155 149K Rank	Estimation Error Est Err 1,225 1.2K Rank	Estimation Error Est Err 149,155 149K Rank	Estimation Error Est Err 88,836 89K Rank	Estimation Error Est Err 29,274 29K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	`Estimate Actual Operator 7503 1225 SORT SUM() 33 7503 1225 INNER JOIN LOOP ON SUM() 33 > SUM() * 1 1 1 │└AGGREGATE sum 32316 30160 │ INNER JOIN HASH ON ps_suppkey = s_suppkey 399 377 │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │ │└TABLE SCAN nation WHERE n_name = JAPAN 10000 10000 │ │ TABLE SCAN supplier 800000 800000 │ TABLE SCAN partsupp 29084 28515 AGGREGATE sum GROUP BY HASH ps_partkey29 32316 30160 TABLE SCAN < temp >`
	Estimate Actual Operator - 1225 PROJECT ps_partkey, a4 AS v - 1225 SORT a4 - 1225 PROJECT a4, ps_partkey - 1225 FILTER (a4 > 7589177.37) AS a2 - 28515 AGGREGATE SUM(a6) AS a4 GROUP BY HASH ps_partkey - 30160 PROJECT ps_partkey, ps_supplycost * ps_availqty AS a6 - 30160 PROJECT ps_partkey, ps_supplycost, ps_availqty - 30160 INNER JOIN HASH ON PROJECTION_304.ps_suppkey = PROJECTION_295.s_suppkey - 377 │└PROJECT s_suppkey - 377 │ PROJECT s_suppkey - 377 │ INNER JOIN HASH ON PROJECTION_301.s_nationkey = PROJECTION_298.n_nationkey - 1 │ │└PROJECT n_nationkey - 1 │ │ PROJECT n_nationkey - 1 │ │ TABLE SCAN nation WHERE n_name = 'JAPAN' - 10000 │ PROJECT s_nationkey, s_suppkey - 10000 │ PROJECT s_suppkey, s_nationkey - 10000 │ TABLE SCAN supplier - 800000 PROJECT ps_suppkey, ps_partkey, ps_supplycost, ps_availqty - 800000 PROJECT ps_partkey, ps_supplycost, ps_availqty, ps_suppkey - 800000 TABLE SCAN partsupp
	Estimate Actual Operator 57 1225 SORT SUM(ps_supplycost * ps_availqty) 57 1225 PROJECT ps_partkey, v 57 1225 SEQUENCE 30769 30160 ├─MATERIALISE AS m___common_subplan_1 30769 30160 │ INNER JOIN HASH ON ps_suppkey = s_suppkey 384 377 │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │ │└TABLE SCAN nation WHERE n_name = 'JAPAN' 10000 377 │ │ TABLE SCAN supplier 800000 797560 │ TABLE SCAN partsupp 57 1225 └─INNER JOIN LOOP ON CAST(SUM(ps_supplycost * CAST(ps_availqty AS DECIMAL(18,0))) AS DECIMAL(38,6)) > 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 1 │ PROJECT SUM(ps_supplycost * CAST(ps_availqty AS DECIMAL(18,0))) * 0.0001 - 1 │ AGGREGATE SUM(#0) 0 30160 │ PROJECT ps_supplycost * CAST(ps_availqty AS DECIMAL(18,0)) 30769 30160 │ SCAN MATERIALISED __common_subplan_1 23979 28515 AGGREGATE SUM(#1) GROUP BY HASH #0 30769 30160 PROJECT ps_partkey, ps_supplycost * CAST(ps_availqty AS DECIMAL(18,0)) 30769 30160 SCAN MATERIALISED __common_subplan_1
	Estimate Actual Operator - 1225 SORT v - 1225 SORT v - 1225 PROJECT ps_partkey, v - 1225 INNER JOIN LOOP ON join_proj_push_down_1 > SUM(ps_supplycost * ps_availqty) * Float64 0.0001 160000 28515 │└PROJECT ps_partkey, SUM(ps_supplycost * ps_availqty), join_proj_push_down_1 160000 28515 │ AGGREGATE SUM(ps_supplycost * CAST(ps_availqty AS DECIMAL(20,0))) GROUP BY HASH ps_partkey 160000 28515 │ DISTRIBUTE HASH ON ps_partkey 160000 28515 │ AGGREGATE SUM(ps_supplycost * CAST(ps_availqty AS DECIMAL(20,0))) GROUP BY HASH ps_partkey 160000 30160 │ INNER JOIN HASH ON n_nationkey = s_nationkey 800000 800000 │ │└PROJECT ps_partkey, ps_availqty, ps_supplycost, s_nationkey 800000 800000 │ │ INNER JOIN HASH ON s_suppkey = ps_suppkey 800000 800000 │ │ │└TABLE SCAN partsupp WHERE ((ps_suppkey >= 1) AND (ps_suppkey <= 10000)) AND TRUE 10000 10000 │ │ TABLE SCAN supplier WHERE ((s_nationkey >= 12) AND (s_nationkey <= 12)) AND s_nationkey IN 12 5 1 │ DISTRIBUTE GATHER 5 1 │ FILTER n_name = 'JAPAN' 25 25 │ DISTRIBUTE ROUND ROBIN 25 25 │ TABLE SCAN nation WHERE n_name = 'JAPAN' 1 1 PROJECT CAST((CAST(SUM(ps_supplycost * ps_availqty) AS FLOAT64) * 0.000100) AS DECIMAL(38,15)) 1 1 AGGREGATE SUM(ps_supplycost * CAST(ps_availqty AS DECIMAL(20,0))) 160000 10 DISTRIBUTE GATHER 160000 10 AGGREGATE SUM(ps_supplycost * CAST(ps_availqty AS DECIMAL(20,0))) 160000 30160 INNER JOIN HASH ON n_nationkey = s_nationkey 800000 800000 │└PROJECT ps_availqty, ps_supplycost, s_nationkey 800000 800000 │ INNER JOIN HASH ON s_suppkey = ps_suppkey 800000 800000 │ │└TABLE SCAN partsupp WHERE ((ps_suppkey >= 1) AND (ps_suppkey <= 10000)) AND TRUE 10000 10000 │ TABLE SCAN supplier WHERE ((s_nationkey >= 12) AND (s_nationkey <= 12)) AND s_nationkey IN 12 5 1 DISTRIBUTE GATHER 5 1 FILTER n_name = 'JAPAN' 25 25 DISTRIBUTE ROUND ROBIN 25 25 TABLE SCAN nation WHERE n_name = 'JAPAN'
	Estimate Actual Operator 1 1 SEQUENCE 7750 1225 ├─SORT v DESC NULLS LAST 7750 2450 │ DISTRIBUTE HASH ON v 7750 2450 │ FILTER 385 57030 │ AGGREGATE SUM(ps_supplycost * CAST(ps_availqty AS DECIMAL(20,0))) GROUP BY HASH ps_partkey 385 28515 │ DISTRIBUTE HASH ON ps_partkey 385 28515 │ AGGREGATE SUM(ps_supplycost * CAST(ps_availqty AS DECIMAL(20,0))) GROUP BY HASH ps_partkey 385 30160 │ INNER JOIN HASH ON s_suppkey = ps_suppkey 385 377 │ │└DISTRIBUTE GATHER 32200 377 │ │ INNER JOIN HASH ON s_nationkey = n_nationkey 32200 1 │ │ │└DISTRIBUTE GATHER 25 1 │ │ │ TABLE SCAN nation WHERE n_name = 'JAPAN' 10000 6063 │ │ TABLE SCAN supplier 800000 796046 │ TABLE SCAN partsupp 1 1 └─AGGREGATE SUM(ps_supplycost * CAST(ps_availqty AS DECIMAL(20,0))) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE SUM(ps_supplycost * CAST(ps_availqty AS DECIMAL(20,0))) 32200 30160 INNER JOIN HASH ON s_suppkey = ps_suppkey 32200 6063 │└DISTRIBUTE HASH 25 6063 │ DISTRIBUTE HASH 25 6063 │ TABLE SCAN supplier 10000 796046 TABLE SCAN partsupp
	Estimate Actual Operator 9035 1225 SORT Expr1009 9035 1225 INNER JOIN LOOP ON n_name = 'JAPAN' 30119 28515 │└AGGREGATE SUM(Expr1021) AS Expr1009 GROUP BY HASH ps_partkey 32005 30160 │ INNER JOIN HASH ON ps_suppkey = s_suppkey 400 377 │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │ │└TABLE SCAN nation WHERE n_name = 'JAPAN' 1000 377 │ │ TABLE SCAN supplier WHERE BLOOM(s_nationkey) 80000 30160 │ PROJECT ps_supplycost * CONVERT_IMPLICIT(decimal(10,0),ps_availqty,0) AS Expr1021 80000 30160 │ TABLE SCAN partsupp WHERE BLOOM(ps_suppkey) 1 1 PROJECT CASE WHEN Expr1042 = 0 THEN NULL ELSE Expr1043 END AS Expr1019 1 1 AGGREGATE COUNT(Expr1022) AS Expr1042, SUM(Expr1022) AS Expr1043 32005 30160 INNER JOIN HASH ON ps_suppkey = s_suppkey 400 377 │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │└TABLE SCAN nation WHERE n_name = 'JAPAN' 1000 377 │ TABLE SCAN supplier WHERE BLOOM(s_nationkey) 80000 30160 PROJECT ps_supplycost * CONVERT_IMPLICIT(decimal(10,0),ps_availqty,0) AS Expr1022 80000 30160 TABLE SCAN partsupp WHERE BLOOM(ps_suppkey)
	Estimate Actual Operator - 1225 PROJECT ps_partkey, sum AS v - 1225 SORT sum - 1225 FILTER CAST(sum AS decimal(38,6)) > expr_33 - 28515 CROSS JOIN LOOP - 28515 │└AGGREGATE SUM(sum_34) AS sum GROUP BY HASH ps_partkey - 28515 │ DISTRIBUTE HASH ON ps_partkey - 28515 │ AGGREGATE SUM(expr) AS sum_34 GROUP BY PARTIAL ps_partkey - 30160 │ PROJECT ps_supplycost * CAST(ps_availqty AS decimal(10,0)) AS expr, ps_partkey - 30160 │ INNER JOIN HASH ON s_nationkey = n_nationkey 25 1 │ │└DISTRIBUTE GATHER 25 1 │ │ FILTER n_name = 'JAPAN' 25 1 │ │ TABLE SCAN nation - 30160 │ INNER JOIN HASH ON ps_suppkey = s_suppkey 10000 377 │ │└DISTRIBUTE GATHER 10000 377 │ │ TABLE SCAN supplier 800000 30160 │ TABLE SCAN partsupp 1 1 DISTRIBUTE GATHER 1 1 PROJECT sum_32 * 0.0001 AS expr_33 1 1 DISTRIBUTE ROUND ROBIN 1 1 AGGREGATE SUM(sum_35) AS sum_32 - 1 DISTRIBUTE GATHER - 1 AGGREGATE SUM(expr_31) AS sum_35 - 30160 PROJECT ps_supplycost_9 * CAST(ps_availqty_8 AS decimal(10,0)) AS expr_31 - 30160 INNER JOIN HASH ON s_nationkey_17 = n_nationkey_24 25 1 │└DISTRIBUTE GATHER 25 1 │ PROJECT n_nationkey AS n_nationkey_24 25 1 │ FILTER n_name = 'JAPAN' 25 1 │ TABLE SCAN nation - 30160 INNER JOIN HASH ON ps_suppkey_7 = s_suppkey_14 10000 377 │└DISTRIBUTE GATHER 10000 377 │ PROJECT s_suppkey AS s_suppkey_14, s_nationkey AS s_nationkey_17 10000 377 │ TABLE SCAN supplier 800000 30160 PROJECT ps_suppkey AS ps_suppkey_7, ps_availqty AS ps_availqty_8, ps_supplycost AS ps_supplycost_9 800000 30160 TABLE SCAN partsupp
	Estimate Actual Operator 10667 1225 SEQUENCE 1 1 ├─AGGREGATE SUM(ps_supplycost * ps_availqty) * 0.0001 32000 30160 │ INNER JOIN LOOP ON ps_suppkey = s_suppkey 400 377 │ │└INNER JOIN LOOP ON s_nationkey = n_nationkey 1 1 │ │ │└TABLE SCAN nation AS nation_1 WHERE nation_1.n_name = 'JAPAN' 400 377 │ │ TABLE SEEK supplier AS supplier_1 30160 30160 │ TABLE SEEK partsupp AS partsupp_1 10667 1225 └─SORT SUM(ps_supplycost * ps_availqty) 10667 1225 AGGREGATE SUM(ps_supplycost * ps_availqty) GROUP BY HASH ps_partkey 32000 30160 INNER JOIN LOOP ON ps_suppkey = s_suppkey 400 377 │└INNER JOIN LOOP ON s_nationkey = n_nationkey 1 1 │ │└TABLE SCAN nation AS nation WHERE n_name = 'JAPAN' 400 377 │ TABLE SEEK supplier AS supplier 30160 30160 TABLE SEEK partsupp AS partsupp