Planner — TPC-H — TPCH-Q01

/* TPC-H Q01 */
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 tpch_sf1.lineitem
WHERE l_shipdate <= '1998-10-01'
GROUP BY l_returnflag,
         l_linestatus
ORDER BY l_returnflag,
         l_linestatus

Engine Compare

Accuracy chart, rows processed ?
Scan Scan	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
Estimation Error Est Err 5,962,146 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,962,146 6M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 5,962,146 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,962,146 6M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 5,962,146 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,962,154 6M Rank	Estimation Error Est Err 8 8 Rank
Estimation Error Est Err 6,001,215 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 8 8 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,962,186 6M Rank	Estimation Error Est Err 40 40 Rank
Estimation Error Est Err 5,962,146 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,962,146 6M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 5,962,146 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 12 12 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,962,158 6M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 5,962,146 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 28 28 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,962,174 6M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 5,962,146 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,962,154 6M Rank	Estimation Error Est Err 8 8 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	`Estimate Actual Operator 4 4 SORT l_returnflag20, l_linestatus21 4 4 AGGREGATE sum, sum, sum, sum, avg, avg, avg, countstar GROUP BY HASH l_returnflag, l_linestatus 5966051 5962146 TABLE SCAN lineitem WHERE l_shipdate <= 2451088`
	Estimate Actual Operator - 4 PROJECT l_returnflag, l_linestatus, a1 AS sum_qty, a2 AS sum_base_price, a3 AS sum_disc_price, a4 AS sum_charge, a5 AS avg_qty, a6 AS avg_price, a7 AS avg_disc, a8 AS count_order - 4 SORT l_returnflag, l_linestatus - 4 PROJECT l_returnflag, l_linestatus, a1, a2, a3, a4, a5, a6, a7, a8 - 4 AGGREGATE SUM(l_quantity) AS a1, SUM(l_extendedprice) AS a2, SUM(a17) AS a3, SUM(a19) AS a4, AVG(l_quantity) AS a5, AVG(l_extendedprice) AS a6, AVG(l_discount) AS a7, COUNT() AS a8 GROUP BY HASH l_returnflag, l_linestatus - 5962146 PROJECT l_returnflag, l_linestatus, l_quantity, l_extendedprice, l_extendedprice * (1 - l_discount) AS a17, (l_extendedprice * (1 - l_discount)) * (1 + l_tax) AS a19, l_discount - 5962146 PROJECT l_returnflag, l_linestatus, l_quantity, l_extendedprice, l_discount, l_tax - 5962146 TABLE SCAN lineitem WHERE l_shipdate <= '1998-10-01'
	Estimate Actual Operator - 4 SORT l_returnflag, l_linestatus - 4 AGGREGATE sum_no_overflow #2, sum_no_overflow #3, sum_no_overflow #4, sum_no_overflow #5, AVG(#6), AVG(#7), AVG(#8), count_star() GROUP BY HASH #0, #1 1200243 5962146 PROJECT l_returnflag, l_linestatus, l_quantity, l_extendedprice, #4, #4 * (1.00 + l_tax), l_quantity, l_extendedprice, l_discount 1200243 5962146 PROJECT l_returnflag, l_linestatus, l_quantity, l_extendedprice, l_extendedprice * (1.00 - l_discount), l_tax, l_discount 1200243 5962146 TABLE SCAN lineitem WHERE l_shipdate <= '1998-10-01'
	Estimate Actual Operator 1200243 4 SORT l_returnflag, l_linestatus 1200243 4 SORT l_returnflag, l_linestatus 1200243 4 PROJECT l_returnflag, l_linestatus, sum_qty, sum_base_price, sum_disc_price, sum_charge, avg_qty, avg_price, avg_disc, count_order 1200243 4 AGGREGATE SUM(l_quantity), SUM(l_extendedprice), SUM(__common_expr_1), SUM(__common_expr_1 * (1 + l_tax)), AVG(l_quantity), AVG(l_extendedprice), AVG(l_discount), COUNT(1) GROUP BY HASH l_returnflag, l_linestatus 1200243 40 DISTRIBUTE HASH ON l_returnflag, l_linestatus 1200243 40 AGGREGATE SUM(l_quantity), SUM(l_extendedprice), SUM(__common_expr_1), SUM(__common_expr_1 * (1 + l_tax)), AVG(l_quantity), AVG(l_extendedprice), AVG(l_discount), COUNT(1) GROUP BY HASH l_returnflag, l_linestatus 1200243 5962146 PROJECT __common_expr_1, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus 1200243 5962146 FILTER l_shipdate <= DATE'1998-10-01' 6001215 6001215 TABLE SCAN lineitem WHERE l_shipdate <= DATE'1998-10-01'
	Estimate Actual Operator 6000000 4 SORT l_returnflag ASC NULLS FIRST, l_linestatus ASC NULLS FIRST 6000000 4 AGGREGATE SUM(l_quantity), SUM(l_extendedprice), SUM(l_extendedprice * (1BD - l_discount)), SUM((l_extendedprice * (1BD - l_discount)) * (1BD + l_tax)), AVG(l_quantity), AVG(l_extendedprice), AVG(l_discount), COUNT(1) GROUP BY HASH l_returnflag, l_linestatus 6000000 8 DISTRIBUTE HASH ON l_returnflag, l_linestatus 6000000 8 AGGREGATE SUM(l_quantity), SUM(l_extendedprice), SUM(l_extendedprice * (1BD - l_discount)), SUM((l_extendedprice * (1BD - l_discount)) * (1BD + l_tax)), AVG(l_quantity), AVG(l_extendedprice), AVG(l_discount), COUNT(1) GROUP BY HASH l_returnflag, l_linestatus 6000000 5962146 TABLE SCAN lineitem WHERE l_shipdate <= DATE'1998-10-01'
	Estimate Actual Operator 6 4 PROJECT CASE WHEN globalagg1018 = 0 THEN NULL ELSE globalagg1020 / CONVERT_IMPLICIT(decimal(19,0),globalagg1018,0) END AS Expr1007, CASE WHEN globalagg1022 = 0 THEN NULL ELSE globalagg1024 / CONVERT_IMPLICIT(decimal(19,0),globalagg1022,0) END AS Expr1008, CASE WHEN globalagg1026 = 0 THEN NULL ELSE globalagg1028 / CONVERT_IMPLICIT(decimal(19,0),globalagg1026,0) END AS Expr1009, CONVERT_IMPLICIT(int,globalagg1030,0) AS Expr1010 6 4 AGGREGATE SUM(partialagg1013) AS Expr1003, SUM(partialagg1014) AS Expr1004, SUM(partialagg1015) AS Expr1005, SUM(partialagg1016) AS Expr1006, SUM(partialagg1017) AS globalagg1018, SUM(partialagg1019) AS globalagg1020, SUM(partialagg1021) AS globalagg1022, SUM(partialagg1023) AS globalagg1024, SUM(partialagg1025) AS globalagg1026, SUM(partialagg1027) AS globalagg1028, SUM(partialagg1029) AS globalagg1030 GROUP BY SORT l_returnflag, l_linestatus 30 28 SORT l_returnflag, l_linestatus 30 28 PROJECT partialagg1013 AS partialagg1019, partialagg1017 AS partialagg1021, partialagg1014 AS partialagg1023, partialagg1017 AS partialagg1025, partialagg1017 AS partialagg1029 30 28 AGGREGATE SUM(l_quantity) AS partialagg1013, SUM(l_extendedprice) AS partialagg1014, SUM(Expr1011) AS partialagg1015, SUM(Expr1012) AS partialagg1016, COUNT() AS partialagg1017, SUM(l_discount) AS partialagg1027 GROUP BY HASH l_returnflag, l_linestatus 5953520 5962146 PROJECT l_extendedprice (1. - l_discount) AS Expr1011, l_extendedprice * (1. - l_discount) * (1. + l_tax) AS Expr1012 5953520 5962146 TABLE SCAN lineitem WHERE l_shipdate <= '1998-10-01'
	Estimate Actual Operator - 4 PROJECT l_returnflag, l_linestatus, sum AS sum_qty, sum_1 AS sum_base_price, sum_2 AS sum_disc_price, sum_3 AS sum_charge, avg AS avg_qty, avg_4 AS avg_price, avg_5 AS avg_disc, count AS count_order - 4 SORT l_returnflag, l_linestatus - 4 AGGREGATE SUM(sum_6) AS sum, SUM(sum_7) AS sum_1, SUM(sum_8) AS sum_2, SUM(sum_9) AS sum_3, AVG(avg_10) AS avg, AVG(avg_11) AS avg_4, AVG(avg_12) AS avg_5, COUNT(count_13) AS count GROUP BY HASH l_returnflag, l_linestatus 5856235 8 DISTRIBUTE HASH ON l_returnflag, l_linestatus 5856235 8 AGGREGATE SUM(l_quantity) AS sum_6, SUM(l_extendedprice) AS sum_7, SUM(expr) AS sum_8, SUM(expr_0) AS sum_9, AVG(l_quantity) AS avg_10, AVG(l_extendedprice) AS avg_11, AVG(l_discount) AS avg_12, COUNT() AS count_13 GROUP BY PARTIAL l_returnflag, l_linestatus 5856235 5962146 PROJECT l_quantity, l_extendedprice, l_discount, l_extendedprice (1 - l_discount) AS expr, (l_extendedprice * (1 - l_discount)) * (l_tax + 1) AS expr_0, l_returnflag, l_linestatus 5856235 5962146 FILTER l_shipdate <= '1998-10-01' 5856235 5962146 TABLE SCAN lineitem
	Estimate Actual Operator 40000 4 AGGREGATE 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 SORT l_returnflag, l_linestatus 120000 12 SORT l_returnflag, l_linestatus 120000 12 AGGREGATE PARTIAL SUM(l_quantity), PARTIAL SUM(l_extendedprice), PARTIAL SUM(l_extendedprice ('1' - l_discount)), PARTIAL SUM((l_extendedprice * ('1' - l_discount)) * ('1' + l_tax)), PARTIAL AVG(l_quantity), PARTIAL AVG(l_extendedprice), PARTIAL AVG(l_discount), PARTIAL COUNT(*) GROUP BY HASH l_returnflag, l_linestatus 2500506 5962146 TABLE SCAN lineitem AS lineitem WHERE l_shipdate <= '1998-10-01'