Planner — TPC-H — TPCH-Q18

Engine Compare

Accuracy chart, rows processed ?
	Scan Scan	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
ClickHouse	Estimation Error Est Err Row Operations Ops 6,148,825 6.1M Rank	Estimation Error Est Err Row Operations Ops 5,998,855 6M Rank	Estimation Error Est Err Row Operations Ops 5 5 Rank	Estimation Error Est Err Row Operations Ops 150,035 150K Rank	Estimation Error Est Err Row Operations Ops 35 35 Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 6,710,479 6.7M Rank	Estimation Error Est Err Row Operations Ops 561,666 562K Rank	Estimation Error Est Err Row Operations Ops 5 5 Rank	Estimation Error Est Err Row Operations Ops 150,038 150K Rank	Estimation Error Est Err Row Operations Ops 5,998,855 6M Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 13,647,640 14M Rank	Estimation Error Est Err Row Operations Ops 7,498,825 7.5M Rank	Estimation Error Est Err Row Operations Ops 5,998,860 6M Rank	Estimation Error Est Err Row Operations Ops 150,040 150K Rank	Estimation Error Est Err Row Operations Ops 7,500,985 7.5M Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 11,997,650 12M Rank	Estimation Error Est Err Row Operations Ops 5,998,830 6M Rank	Estimation Error Est Err Row Operations Ops 5 5 Rank	Estimation Error Est Err Row Operations Ops 45 45 Rank	Estimation Error Est Err Row Operations Ops 5,998,855 6M Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	`Estimate Actual Operator - 5 SORT o_totalprice, o_orderdate - 5 GROUP BY HASH c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice AGGREGATE SUM(l_quantity) - 35 INNER JOIN HASH ON o_custkey = c_custkey - 150000 │└TABLE SCAN customer - 35 INNER JOIN HASH ON l_orderkey = o_orderkey - 5 │└TABLE SCAN orders WHERE o_orderkey IN (SELECT l_orderkey FROM tpch.lineitem GROUP BY l_orderkey HAVING SUM(l_quantity) > 314) - 5998820 TABLE SCAN lineitem WHERE TRUE`
DuckDB	Estimate Actual Operator - 5 SORT o_totalprice, o_orderdate 1279731 5 GROUP BY HASH #0, #1, #2, #3, #4 AGGREGATE SUM(#5) 1279731 35 PROJECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, l_quantity 1279731 35 INNER JOIN HASH ON l_orderkey = o_orderkey 317162 5 │└INNER JOIN HASH ON o_custkey = c_custkey 150000 149998 │ │└TABLE SCAN customer WHERE c_custkey >= 3 300000 5 │ LEFT SEMI JOIN HASH ON o_orderkey = #0 145107 5 │ │└FILTER SUM(l_quantity) > 314.000 725537 1500000 │ │ GROUP BY HASH #0 AGGREGATE sum_no_overflow #1 5998820 5998820 │ │ PROJECT l_orderkey, l_quantity 5998820 5998820 │ │ TABLE SCAN lineitem 1500000 162448 │ TABLE SCAN orders 5998820 399213 TABLE SCAN lineitem
SQL Server	Estimate Actual Operator 297 5 INNER JOIN LOOP ON o_custkey = c_custkey 1 5 │└TABLE SEEK customer 297 5 SORT o_totalprice, o_orderdate 297 5 INNER JOIN LOOP ON l_orderkey = o_orderkey 1 5 │└TABLE SEEK orders 297 5 PROJECT CASE WHEN Expr1024 = 0 THEN NULL ELSE Expr1025 END AS Expr1011 297 5 GROUP BY HASH AGGREGATE COUNT(l_quantity) AS Expr1024, SUM(l_quantity) AS Expr1025, IN(l_orderkey) AS l_orderkey 1350 35 INNER JOIN HASH ON l_orderkey = l_orderkey 297 5 │└FILTER Expr1010 > 314.00 1321840 1500000 │ PROJECT CASE WHEN Expr1022 = 0 THEN NULL ELSE Expr1023 END AS Expr1010 1321840 1500000 │ GROUP BY HASH AGGREGATE COUNT(l_quantity) AS Expr1022, SUM(l_quantity) AS Expr1023 5998820 5998820 │ TABLE SCAN lineitem 5998820 5998820 TABLE SCAN lineitem
PostgreSQL	Estimate Actual Operator 1999602 5 SORT o_totalprice, o_orderdate 1999602 5 GROUP BY SORT c_custkey, o_orderkey AGGREGATE c_name, o_orderdate, o_totalprice, SUM(l_quantity) 1999602 35 SORT c_custkey, o_orderkey 1999602 35 INNER JOIN HASH ON l_orderkey = o_orderkey 147762 5 │└INNER JOIN HASH ON o_custkey = c_custkey 150000 150000 │ │└TABLE SCAN customer 147762 5 │ INNER JOIN HASH ON o_orderkey = l_orderkey 147762 5 │ │└GROUP BY SORT l_orderkey AGGREGATE 2216435 1502130 │ │ GROUP BY SORT l_orderkey AGGREGATE PARTIALSUM(l_quantity) 7498525 5998820 │ │ SORT l_orderkey 7498525 5998820 │ │ TABLE SCAN lineitem_1 1500000 1500000 │ TABLE SCAN orders 5998820 5998820 TABLE SCAN lineitem