Planner — TPC-H — TPCH-Q18

Engine Compare

Accuracy chart, rows processed ?
	Scan Scan	Seek Seek	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
ClickHouse	Estimation Error Est Err Row Operations Ops 13,652,430 14M Rank		Estimation Error Est Err Row Operations Ops 12,002,430 12M Rank	Estimation Error Est Err Row Operations Ops 1,500,000 1.5M Rank	Estimation Error Est Err Row Operations Ops 12,002,430 12M Rank	Estimation Error Est Err Row Operations Ops 12,002,430 12M Rank	Estimation Error Est Err Row Operations Ops 0 Rank
Databricks	Estimation Error Est Err Row Operations Ops 13,332,943 13M Rank		Estimation Error Est Err Row Operations Ops 1,650,010 1.7M Rank	Estimation Error Est Err Row Operations Ops 9 9 Rank	Estimation Error Est Err Row Operations Ops 1,180,531 1.2M Rank	Estimation Error Est Err Row Operations Ops 13,502,440 14M Rank	Estimation Error Est Err Row Operations Ops 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 6,151,569 6.2M Rank		Estimation Error Est Err Row Operations Ops 364 364 Rank	Estimation Error Est Err Row Operations Ops 9 9 Rank	Estimation Error Est Err Row Operations Ops 150,071 150K Rank	Estimation Error Est Err Row Operations Ops 6,001,278 6M Rank	Estimation Error Est Err Row Operations Ops 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 19,650,905 20M Rank	Estimation Error Est Err Row Operations Ops 18,150,905 18M Rank	Estimation Error Est Err Row Operations Ops 1,647,260 1.6M Rank	Estimation Error Est Err Row Operations Ops 18 18 Rank	Estimation Error Est Err Row Operations Ops 99 99 Rank	Estimation Error Est Err Row Operations Ops 18,003,708 18M Rank	Estimation Error Est Err Row Operations Ops 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 6,001,296 6M Rank		Estimation Error Est Err Row Operations Ops 81 81 Rank	Estimation Error Est Err Row Operations Ops 9 9 Rank	Estimation Error Est Err Row Operations Ops 81 81 Rank	Estimation Error Est Err Row Operations Ops 6,001,278 6M Rank	Estimation Error Est Err Row Operations Ops 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	Estimate Actual Operator - 9 SEQUENCE - 1500000 ├─PROJECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, a1 - 1500000 │ SORT o_totalprice, o_orderdate - 1500000 │ PROJECT o_totalprice, o_orderdate, a1, c_name, c_custkey, o_orderkey - 1500000 │ AGGREGATE SUM(l_quantity) AS a1 GROUP BY HASH c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice - 6001215 │ PROJECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, l_quantity - 6001215 │ PROJECT l_quantity, o_orderkey, o_orderdate, o_totalprice, c_name, c_custkey - 6001215 │ INNER JOIN HASH ON PROJECTION_408.o_custkey = PROJECTION_405.c_custkey - 150000 │ │└PROJECT c_custkey, c_name - 150000 │ │ PROJECT c_name, c_custkey - 150000 │ │ TABLE SCAN customer - 6001215 │ PROJECT o_custkey, l_quantity, o_orderkey, o_orderdate, o_totalprice - 6001215 │ PROJECT l_quantity, o_orderkey, o_orderdate, o_totalprice, o_custkey - 6001215 │ INNER JOIN HASH ON PROJECTION_414.l_orderkey = PROJECTION_411.o_orderkey - 1500000 │ │└PROJECT o_orderkey, o_orderdate, o_totalprice, o_custkey - 1500000 │ │ PROJECT o_orderkey, o_orderdate, o_totalprice, o_custkey - 1500000 │ │ TABLE SCAN orders WHERE TRUE - 6001215 │ PROJECT l_orderkey, l_quantity - 6001215 │ PROJECT l_orderkey, l_quantity - 6001215 │ TABLE SCAN lineitem WHERE TRUE - 9 └─PROJECT l_orderkey - 9 FILTER (a9 > 314) AS a7 - 1500000 AGGREGATE SUM(l_quantity) AS a9 GROUP BY HASH l_orderkey - 6001215 PROJECT l_orderkey, l_quantity - 6001215 TABLE SCAN lineitem
DuckDB	Estimate Actual Operator - 9 SORT o_totalprice, o_orderdate 1277320 9 AGGREGATE SUM(#5) GROUP BY HASH #0, #1, #2, #3, #4 1277320 63 PROJECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, l_quantity 1277320 63 INNER JOIN HASH ON l_orderkey = o_orderkey 316622 9 │└INNER JOIN HASH ON o_custkey = c_custkey 150000 149999 │ │└TABLE SCAN customer WHERE c_custkey <= 149999 300000 9 │ LEFT SEMI JOIN HASH ON o_orderkey = #3 96764 9 │ │└FILTER SUM(l_quantity) > 314.00 483820 1500000 │ │ AGGREGATE sum_no_overflow #1 GROUP BY HASH #0 6001215 6001215 │ │ PROJECT l_orderkey, l_quantity 6001215 6001215 │ │ TABLE SCAN lineitem 1500000 116 │ TABLE SCAN orders 6001215 239 TABLE SCAN lineitem
Databricks	Estimate Actual Operator 6000000 9 SORT o_totalprice DESC NULLS LAST, o_orderdate ASC NULLS FIRST 6000000 9 DISTRIBUTE HASH ON o_totalprice DESC NULLS LAST, o_orderdate ASC NULLS FIRST 6000000 9 AGGREGATE SUM(l_quantity) GROUP BY HASH c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice 6000000 9 INNER JOIN HASH ON o_orderkey = l_orderkey 6000000 9 │└DISTRIBUTE GATHER 1500000 9 │ INNER JOIN HASH ON o_custkey = c_custkey 1500000 9 │ │└LEFT SEMI JOIN HASH ON o_orderkey = l_orderkey 6000000 1180513 │ │ │└TABLE SCAN orders 1500000 9 │ │ DISTRIBUTE GATHER 1480000 9 │ │ FILTER (SUM(l_quantity) IS NOT NULL) AND (SUM(l_quantity) > 314.00BD) 1480000 1500000 │ │ AGGREGATE SUM(l_quantity) GROUP BY HASH l_orderkey 1480000 1500001 │ │ DISTRIBUTE HASH ON l_orderkey 1480000 1500001 │ │ AGGREGATE SUM(l_quantity) GROUP BY HASH l_orderkey 6000000 6001215 │ │ TABLE SCAN lineitem 1500000 150000 │ DISTRIBUTE GATHER 150000 150000 │ TABLE SCAN customer 6000000 1500001 DISTRIBUTE HASH ON l_orderkey 6000000 1500001 AGGREGATE SUM(l_quantity) GROUP BY HASH l_orderkey 1500000 6001215 TABLE SCAN lineitem
PostgreSQL	Estimate Actual Operator 2000405 9 SORT o_totalprice, o_orderdate 2000405 9 AGGREGATE c_name, o_orderdate, o_totalprice, SUM(l_quantity) GROUP BY SORT c_custkey, o_orderkey 2000405 63 INNER JOIN LOOP ON l_orderkey = o_orderkey 145726 9 │└INNER JOIN MERGE ON o_custkey = c_custkey 182157 9 │ │└SORT o_custkey, o_orderkey 60719 3 │ │ INNER JOIN HASH ON o_orderkey = l_orderkey 437178 27 │ │ │└GROUP BY SORT l_orderkey 18003645 18003645 │ │ │ TABLE SEEK lineitem AS lineitem_1 1875000 1500000 │ │ TABLE SCAN orders AS orders 150000 147197 │ TABLE SEEK customer AS customer 126 63 TABLE SEEK lineitem AS lineitem
SQL Server	Estimate Actual Operator 536 9 SORT o_totalprice, o_orderdate 536 9 INNER JOIN HASH ON c_custkey = o_custkey 536 9 │└INNER JOIN HASH ON o_orderkey = l_orderkey 536 9 │ │└AGGREGATE SUM(l_quantity) AS Expr1013, l_orderkey GROUP BY HASH l_orderkey 2520 63 │ │ INNER JOIN HASH ON l_orderkey = l_orderkey 536 9 │ │ │└FILTER Expr1012 > 314.00 1278220 1500000 │ │ │ AGGREGATE SUM(l_quantity) AS Expr1012 GROUP BY HASH l_orderkey 6001220 6001215 │ │ │ TABLE SCAN lineitem 6001 63 │ │ TABLE SCAN lineitem WHERE BLOOM(l_orderkey) 1500 9 │ TABLE SCAN orders WHERE BLOOM(o_orderkey) 1500 9 TABLE SCAN customer WHERE BLOOM(c_custkey)