Planner — TPC-H — TPCH-Q09

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 8,119,731 8.1M Rank	Estimation Error Est Err Row Operations Ops 15,927,596 16M Rank	Estimation Error Est Err Row Operations Ops 175 175 Rank	Estimation Error Est Err Row Operations Ops 10,151,467 10M Rank	Estimation Error Est Err Row Operations Ops 222,691 223K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 6,618,379 6.6M Rank	Estimation Error Est Err Row Operations Ops 5,598,397 5.6M Rank	Estimation Error Est Err Row Operations Ops 175 175 Rank	Estimation Error Est Err Row Operations Ops 2,033,770 2M Rank	Estimation Error Est Err Row Operations Ops 245,491 245K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 1,331,549 1.3M Rank	Estimation Error Est Err Row Operations Ops 1,582,052 1.6M Rank	Estimation Error Est Err Row Operations Ops 245,492 245K Rank	Estimation Error Est Err Row Operations Ops 1,014,844 1M Rank	Estimation Error Est Err Row Operations Ops 246,192 246K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 8,119,783 8.1M Rank	Estimation Error Est Err Row Operations Ops 7,090,006 7.1M Rank	Estimation Error Est Err Row Operations Ops 245,841 246K Rank	Estimation Error Est Err Row Operations Ops 2,023,977 2M Rank	Estimation Error Est Err Row Operations Ops 245,666 246K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	Estimate Actual Operator - 175 PROJECT SUM(amount) - 175 SORT nation, o_year - 175 GROUP BY HASH nation, o_year AGGREGATE SUM(amount) - 222691 PROJECT EXTRACT(YEAR FROM o_orderdate), ps_supplycost * l_quantity, n_name, 1 - l_discount, EXTRACT(YEAR FROM o_orderdate), l_extendedprice * (1 - l_discount), (l_extendedprice * (1 - l_discount)) - (ps_supplycost * l_quantity), (l_extendedprice * (1 - l_discount)) - (ps_supplycost * l_quantity) - 222691 INNER JOIN HASH ON s_nationkey = n_nationkey - 25 │└TABLE SCAN nation - 222691 INNER JOIN HASH ON l_suppkey = s_suppkey - 10000 │└TABLE SCAN supplier - 222712 INNER JOIN HASH ON l_partkey = p_partkey - 10886 │└TABLE SCAN part WHERE p_name LIKE '%lace%' - 4063653 INNER JOIN HASH ON (l_suppkey,l_partkey) = (ps_suppkey,ps_partkey) - 600000 │└TABLE SCAN partsupp - 5419720 INNER JOIN HASH ON l_orderkey = o_orderkey - 1500000 │└TABLE SCAN orders - 5998820 TABLE SCAN lineitem
DuckDB	Estimate Actual Operator - 175 SORT nation, o_year 3949951 175 GROUP BY HASH #0, #1 AGGREGATE SUM(#2) 4066138 245491 PROJECT nation, o_year, amount 4066138 245491 PROJECT nation, o_year, amount 4066138 245491 INNER JOIN HASH ON l_suppkey = s_suppkey 9615 10000 │└INNER JOIN HASH ON s_nationkey = n_nationkey 25 25 │ │└TABLE SCAN nation 10000 10000 │ TABLE SCAN supplier 1214083 245491 INNER JOIN HASH ON l_orderkey = o_orderkey 1500000 1500000 │└TABLE SCAN orders 1203335 245491 INNER JOIN HASH ON l_suppkey = ps_suppkeyl_partkey = p_partkey 120178 32788 │└INNER JOIN HASH ON ps_partkey = p_partkey 40000 8198 │ │└FILTER p_partkey <= 150000 40000 10939 │ │ TABLE SCAN part WHERE contains(p_name,'lace') 600000 599897 │ TABLE SCAN partsupp WHERE ps_suppkey >= 1 5998820 4497518 FILTER l_suppkey >= 1 5998820 4497518 TABLE SCAN lineitem WHERE l_partkey <= 150000
SQL Server	Estimate Actual Operator 175 175 PROJECT CASE WHEN globalagg1019 = 0 THEN NULL ELSE globalagg1021 END AS Expr1016 175 175 GROUP BY HASH , AGGREGATE SUM(partialagg1018) AS globalagg1019, SUM(partialagg1020) AS globalagg1021 175 175 SORT n_name, Expr1014 175 175 INNER JOIN MERGE ON n_nationkey = s_nationkey 25 25 │└TABLE SEEK nation 175 175 SORT s_nationkey 175 175 GROUP BY HASH AGGREGATE COUNT(Expr1017 - ps_supplycost * l_quantity) AS partialagg1018, SUM(Expr1017 - ps_supplycost * l_quantity) AS partialagg1020 34408 245491 INNER JOIN MERGE ON o_orderkey = l_orderkey 1500000 1499999 │└PROJECT datepart(EXTRACT(YEAR FROM o_orderdate) AS Expr1014 1500000 1499999 │ TABLE SEEK orders 34408 245491 SORT l_orderkey 34408 245491 INNER JOIN HASH ON 10000 10000 │└TABLE SEEK supplier 34411 245520 INNER JOIN HASH ON l_partkey = p_partkey AND l_suppkey = ps_suppkey 53651 32792 │└INNER JOIN HASH ON ps_partkey = p_partkey 11385 10939 │ │└TABLE SEEK part WHERE p_name LIKE '%lace%' 600000 600000 │ TABLE SCAN partsupp 5998820 5998820 PROJECT l_extendedprice * (1. - l_discount) AS Expr1017 5998820 5998820 TABLE SCAN lineitem
PostgreSQL	Estimate Actual Operator 114 175 GROUP BY SORT n_name, EXTRACT(EXTRACT(YEAR FROM o_orderdate) AGGREGATE SUM((l_extendedprice * ('1' - l_discount)) - (ps_supplycost * l_quantity)) 148 700 GROUP BY SORT n_name, EXTRACT(EXTRACT(YEAR FROM o_orderdate) AGGREGATE PARTIALSUM((l_extendedprice * ('1' - l_discount)) - (ps_supplycost * l_quantity)) 148 245492 SORT n_name, EXTRACT(EXTRACT(YEAR FROM o_orderdate) 148 245492 INNER JOIN HASH ON s_nationkey = n_nationkey 25 25 │└TABLE SCAN nation 148 245492 INNER JOIN LOOP ON s_suppkey = l_suppkey 148 245520 │└INNER JOIN LOOP ON o_orderkey = l_orderkey 148 245520 │ │└INNER JOIN LOOP ON l_partkey = ps_partkey 46920 32792 │ │ │└INNER JOIN HASH ON ps_partkey = p_partkey 10100 10940 │ │ │ │└TABLE SCAN part WHERE p_name LIKE '%lace%' 774192 600000 │ │ │ TABLE SCAN partsupp 32792 229544 │ │ TABLE SEEK lineitem WHERE ps_suppkey = l_suppkey 245520 245520 │ TABLE SEEK orders 245520 245520 TABLE SEEK supplier