Planner — TPC-H — TPCH-Q04

/* TPC-H Q04 */
SELECT o_orderpriority,
       COUNT(*) AS order_count
FROM tpch_sf1.orders
WHERE o_orderdate >= '1995-02-01'
  AND o_orderdate < '1995-05-01'
  AND EXISTS (SELECT *
              FROM tpch_sf1.lineitem
              WHERE l_orderkey = o_orderkey
                AND l_commitdate < l_receiptdate)
GROUP BY o_orderpriority
ORDER BY o_orderpriority

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 1,462,672 1.5M Rank		Estimation Error Est Err 1,406,737 1.4M Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 55,935 56K Rank	Estimation Error Est Err 51,276 51K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,849,231 3.8M Rank		Estimation Error Est Err 3,793,296 3.8M Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 55,935 56K Rank	Estimation Error Est Err 51,276 51K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,905,166 3.9M Rank		Estimation Error Est Err 55,935 56K Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 198,120 198K Rank	Estimation Error Est Err 107,211 107K Rank	Estimation Error Est Err 365,926 366K Rank
Estimation Error Est Err 7,501,215 7.5M Rank		Estimation Error Est Err 55,935 56K Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 3,793,296 3.8M Rank	Estimation Error Est Err 51,326 51K Rank	Estimation Error Est Err 3,849,281 3.8M Rank
Estimation Error Est Err 300,175 300K Rank		Estimation Error Est Err 350,930 351K Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 248,261 248K Rank	Estimation Error Est Err 51,276 51K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 111,870 112K Rank	Estimation Error Est Err 55,935 56K Rank	Estimation Error Est Err 55,935 56K Rank	Estimation Error Est Err 51,276 51K Rank	Estimation Error Est Err 55,935 56K Rank	Estimation Error Est Err 51,296 51K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 196,985 197K Rank		Estimation Error Est Err 141,050 141K Rank	Estimation Error Est Err 50 50 Rank	Estimation Error Est Err 55,935 56K Rank	Estimation Error Est Err 51,326 51K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,849,231 3.8M Rank		Estimation Error Est Err 55,935 56K Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 1,375,365 1.4M Rank	Estimation Error Est Err 2,802,011 2.8M Rank	Estimation Error Est Err 2,750,735 2.8M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	`Estimate Actual Operator 5 5 SORT o_orderpriority32 5 5 AGGREGATE countstar GROUP BY HASH o_orderpriority 50555 51276 RIGHT SEMI INNER JOIN HASH ON l_orderkey = o_orderkey 54199 55935 │└TABLE SCAN orders WHERE o_orderdate BETWEEN 2449750 AND 2449838 3709735 1406737 TABLE SCAN lineitem WHERE l_commitdate < l_receiptdate`
	Estimate Actual Operator - 5 PROJECT o_orderpriority, a1 AS order_count - 5 SORT o_orderpriority - 5 PROJECT o_orderpriority, a1 - 5 AGGREGATE COUNT() AS a1 GROUP BY HASH o_orderpriority - 51276 PROJECT o_orderpriority - 51276 PROJECT o_orderpriority - 51276 RIGHT SEMI INNER JOIN HASH ON PROJECTION_94.l_orderkey = PROJECTION_91.o_orderkey - 55935 │└PROJECT o_orderkey AS o_orderkey_right, o_orderpriority - 55935 │ PROJECT o_orderkey, o_orderpriority - 55935 │ TABLE SCAN orders WHERE (o_orderdate >= '1995-02-01') AND (o_orderdate < '1995-05-01') - 3793296 PROJECT l_orderkey - 3793296 TABLE SCAN lineitem WHERE (l_orderkey = l_orderkey) AND (l_commitdate < l_receiptdate)
	Estimate Actual Operator 5 5 SORT o_orderpriority 5 5 AGGREGATE count_star() GROUP BY HASH #0 60000 51276 PROJECT o_orderpriority 60000 0 RIGHT SEMI INNER JOIN HASH ON o_orderkey IS NOT DISTINCT FROM o_orderkey 271684 55935 │└AGGREGATE GROUP BY HASH #0 60000 51276 │ RIGHT SEMI INNER JOIN HASH ON o_orderkey IS NOT DISTINCT FROM o_orderkey - 0 │ │└SCAN EMPTY 684972 141050 │ PROJECT o_orderkey 684972 141050 │ INNER JOIN HASH ON l_orderkey = o_orderkey 271684 0 │ │└SCAN MATERIALISED 1200243 153945 │ FILTER l_commitdate < l_receiptdate 6001215 244240 │ TABLE SCAN lineitem 300000 55935 TABLE SCAN orders WHERE o_orderdate >= '1995-02-01' AND o_orderdate < '1995-05-01'
	Estimate Actual Operator 300000 5 SORT o_orderpriority 300000 5 SORT o_orderpriority 300000 5 PROJECT o_orderpriority, order_count 300000 5 AGGREGATE COUNT(1) GROUP BY HASH o_orderpriority 300000 50 DISTRIBUTE HASH ON o_orderpriority 300000 50 AGGREGATE COUNT(1) GROUP BY HASH o_orderpriority 300000 51276 LEFT SEMI JOIN HASH ON o_orderkey = l_orderkey 1200243 3793296 │└DISTRIBUTE HASH ON l_orderkey 1200243 3793296 │ FILTER l_receiptdate > l_commitdate 6001215 6001215 │ TABLE SCAN lineitem WHERE l_receiptdate > l_commitdate 300000 55935 DISTRIBUTE HASH ON o_orderkey 300000 55935 FILTER (o_orderdate >= DATE'1995-02-01') AND (o_orderdate < DATE'1995-05-01') 1500000 1500000 TABLE SCAN orders WHERE (o_orderdate >= DATE'1995-02-01') AND (o_orderdate < DATE'1995-05-01')
	Estimate Actual Operator 1500000 0 SEQUENCE 1500000 5 ├─SORT o_orderpriority ASC NULLS FIRST 1500000 5 │ AGGREGATE COUNT(1) GROUP BY HASH o_orderpriority 1500000 51276 │ LEFT SEMI JOIN HASH ON o_orderkey = l_orderkey 1500000 198120 │ │└DISTRIBUTE HASH ON l_orderkey 6000000 198120 │ │ FILTER - 3793296 │ │ TABLE SCAN lineitem WHERE l_commitdate < l_receiptdate 1500000 55935 │ DISTRIBUTE HASH ON o_orderkey 1500000 55935 │ TABLE SCAN orders WHERE (o_orderdate >= DATE'1995-02-01') AND (o_orderdate < DATE'1995-05-01') 1500000 0 └─FILTER 1500000 1 DISTRIBUTE HASH 1500000 1 AGGREGATE bloom_filter_agg(bloom_expr(o_orderkey,o_orderkey),55935L,524288L) 6000000 55935 DISTRIBUTE HASH 6000000 55935 DISTRIBUTE HASH 6000000 55935 TABLE SCAN orders WHERE (o_orderdate >= DATE'1995-02-01') AND (o_orderdate < DATE'1995-05-01')
	Estimate Actual Operator 5 5 PROJECT CONVERT_IMPLICIT(int,globalagg1009,0) AS Expr1007 5 5 AGGREGATE SUM(partialagg1008) AS globalagg1009 GROUP BY SORT o_orderpriority 25 50 SORT o_orderpriority 25 50 AGGREGATE COUNT(*) AS partialagg1008 GROUP BY HASH o_orderpriority 18594 51276 LEFT SEMI JOIN HASH ON l_orderkey = o_orderkey 57280 55935 │└TABLE SCAN orders WHERE o_orderdate >= '1995-02-01' AND o_orderdate < '1995-05-01' 18003 141050 TABLE SCAN lineitem WHERE (BLOOM(l_orderkey)) AND (l_commitdate < l_receiptdate)
	Estimate Actual Operator - 5 PROJECT o_orderpriority, count AS order_count - 5 SORT o_orderpriority - 5 AGGREGATE COUNT(count_3) AS count GROUP BY HASH o_orderpriority - 5 DISTRIBUTE HASH ON o_orderpriority - 5 AGGREGATE COUNT(*) AS count_3 GROUP BY PARTIAL o_orderpriority - 51276 INNER JOIN HASH ON o_orderkey = l_orderkey - 1375365 │└DISTRIBUTE HASH ON l_orderkey - 1375365 │ GROUP BY HASH l_orderkey 3040569 1375365 │ DISTRIBUTE HASH ON l_orderkey 3040569 1375365 │ GROUP BY PARTIAL l_orderkey 3040569 1375365 │ FILTER l_commitdate < l_receiptdate 3040569 3793296 │ TABLE SCAN lineitem 55509 55935 FILTER (o_orderdate >= '1995-02-01') AND (o_orderdate < '1995-05-01') 55509 55935 TABLE SCAN orders
	`Estimate Actual Operator 5 5 AGGREGATE COUNT() GROUP BY SORT o_orderpriority 20 20 AGGREGATE PARTIAL COUNT() GROUP BY SORT o_orderpriority 21164 51276 SORT o_orderpriority 21164 51276 LEFT SEMI JOIN LOOP ON l_orderkey = o_orderkey 69824 55935 │└TABLE SCAN orders AS orders WHERE (o_orderdate >= '1995-02-01') AND (o_orderdate < '1995-05-01') 223740 55935 TABLE SEEK lineitem AS lineitem WHERE l_commitdate < l_receiptdate`