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,319,731 8.3M Rank	Estimation Error Est Err Row Operations Ops 17,429,499 17M Rank	Estimation Error Est Err Row Operations Ops 175 175 Rank	Estimation Error Est Err Row Operations Ops 11,726,288 12M Rank	Estimation Error Est Err Row Operations Ops 295,609 296K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 8,318,877 8.3M Rank	Estimation Error Est Err Row Operations Ops 7,463,459 7.5M Rank	Estimation Error Est Err Row Operations Ops 175 175 Rank	Estimation Error Est Err Row Operations Ops 3,437,615 3.4M Rank	Estimation Error Est Err Row Operations Ops 327,773 328K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 1,772,860 1.8M Rank	Estimation Error Est Err Row Operations Ops 2,111,176 2.1M Rank	Estimation Error Est Err Row Operations Ops 327,772 328K Rank	Estimation Error Est Err Row Operations Ops 1,354,928 1.4M Rank	Estimation Error Est Err Row Operations Ops 328,472 328K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 2,648,766 2.6M Rank	Estimation Error Est Err Row Operations Ops 1,783,552 1.8M Rank	Estimation Error Est Err Row Operations Ops 175 175 Rank	Estimation Error Est Err Row Operations Ops 1,027,279 1M Rank	Estimation Error Est Err Row Operations Ops 327,948 328K 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) - 295609 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) - 295609 INNER JOIN HASH ON s_nationkey = n_nationkey - 25 │└TABLE SCAN nation - 295609 INNER JOIN HASH ON l_suppkey = s_suppkey - 10000 │└TABLE SCAN supplier - 295630 INNER JOIN HASH ON l_partkey = p_partkey - 10886 │└TABLE SCAN part WHERE p_name LIKE '%lace%' - 5419720 INNER JOIN HASH ON (l_suppkey,l_partkey) = (ps_suppkey,ps_partkey) - 800000 │└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 5226787 175 GROUP BY HASH #0, #1 AGGREGATE SUM(#2) 5432872 327773 PROJECT nation, o_year, amount 5432872 327773 PROJECT nation, o_year, amount 5432872 327773 INNER JOIN HASH ON l_suppkey = s_suppkeyp_partkey = ps_partkey 894974 799921 │└INNER JOIN HASH ON ps_suppkey = s_suppkey 9615 10000 │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 25 25 │ │ │└TABLE SCAN nation 10000 10000 │ │ TABLE SCAN supplier 800000 799921 │ TABLE SCAN partsupp WHERE ps_suppkey >= 1 1212281 327773 INNER JOIN HASH ON l_orderkey = o_orderkey 1500000 1500000 │└TABLE SCAN orders 1201548 327773 INNER JOIN HASH ON l_partkey = p_partkey 40000 10939 │└TABLE SCAN part WHERE contains(p_name,'lace') 5998820 5997992 TABLE SCAN lineitem WHERE l_suppkey >= 1
PostgreSQL	Estimate Actual Operator 148 175 GROUP BY SORT n_name, EXTRACT(EXTRACT(YEAR FROM o_orderdate) AGGREGATE SUM((l_extendedprice * ('1' - l_discount)) - (ps_supplycost * l_quantity)) 192 700 GROUP BY SORT n_name, EXTRACT(EXTRACT(YEAR FROM o_orderdate) AGGREGATE PARTIALSUM((l_extendedprice * ('1' - l_discount)) - (ps_supplycost * l_quantity)) 192 327772 SORT n_name, EXTRACT(EXTRACT(YEAR FROM o_orderdate) 192 327772 INNER JOIN HASH ON s_nationkey = n_nationkey 25 25 │└TABLE SCAN nation 192 327772 INNER JOIN LOOP ON s_suppkey = l_suppkey 192 327800 │└INNER JOIN LOOP ON o_orderkey = l_orderkey 192 327800 │ │└INNER JOIN LOOP ON l_partkey = ps_partkey 62560 43756 │ │ │└INNER JOIN HASH ON ps_partkey = p_partkey 5050 10939 │ │ │ │└TABLE SCAN part WHERE p_name LIKE '%lace%' 1032260 800000 │ │ │ TABLE SCAN partsupp 43756 306292 │ │ TABLE SEEK lineitem WHERE ps_suppkey = l_suppkey 327802 327802 │ TABLE SEEK orders 327802 327802 TABLE SEEK supplier
SQL Server	Estimate Actual Operator 175 175 PROJECT CASE WHEN globalagg1026 = 0 THEN NULL ELSE globalagg1028 END AS Expr1016 175 175 GROUP BY HASH , AGGREGATE SUM(partialagg1025) AS globalagg1026, SUM(partialagg1027) AS globalagg1028 175 175 SORT n_name, Expr1014 175 175 INNER JOIN HASH ON n_nationkey = s_nationkey 25 25 │└TABLE SEEK nation 175 175 GROUP BY HASH AGGREGATE COUNT(Expr1118) AS partialagg1025, SUM(Expr1118) AS partialagg1027 45605 327773 PROJECT Expr1017 - ps_supplycost * l_quantity AS Expr1118 45605 327773 INNER JOIN HASH ON o_orderkey = l_orderkey 45605 327773 │└INNER JOIN HASH ON 10000 10000 │ │└TABLE SEEK supplier 45608 327802 │ INNER JOIN HASH ON l_suppkey = ps_suppkey AND l_partkey = p_partkey 45542 43756 │ │└INNER JOIN HASH ON ps_partkey = p_partkey 11385 10939 │ │ │└TABLE SEEK part WHERE p_name LIKE '%lace%' 800000 800000 │ │ TABLE SCAN partsupp 59988 327802 │ PROJECT l_extendedprice * (1. - l_discount) AS Expr1017 59988 327802 │ TABLE SEEK lineitem WHERE BLOOM(l_suppkey) AND BLOOM(l_partkey) 15000 294180 PROJECT datepart(EXTRACT(YEAR FROM o_orderdate) AS Expr1014 1500000 1500000 TABLE SEEK orders