Planner — TPC-H — TPCH-Q21

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 14,367,450 14M Rank	Estimation Error Est Err Row Operations Ops 7,483,048 7.5M Rank	Estimation Error Est Err Row Operations Ops 427 427 Rank	Estimation Error Est Err Row Operations Ops 13,409,167 13M Rank	Estimation Error Est Err Row Operations Ops 68,697 69K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 18,754,973 19M Rank	Estimation Error Est Err Row Operations Ops 14,924,939 15M Rank	Estimation Error Est Err Row Operations Ops 396 396 Rank	Estimation Error Est Err Row Operations Ops 1,031,358 1M Rank	Estimation Error Est Err Row Operations Ops 4,060 4.1K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 13,599,420 14M Rank	Estimation Error Est Err Row Operations Ops 7,613,884 7.6M Rank	Estimation Error Est Err Row Operations Ops 4,456 4.5K Rank	Estimation Error Est Err Row Operations Ops 6,308,500 6.3M Rank	Estimation Error Est Err Row Operations Ops 4,060 4.1K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 14,367,119 14M Rank	Estimation Error Est Err Row Operations Ops 14,367,118 14M Rank	Estimation Error Est Err Row Operations Ops 396 396 Rank	Estimation Error Est Err Row Operations Ops 518,976 519K Rank	Estimation Error Est Err Row Operations Ops 4,060 4.1K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
ClickHouse	Estimate Actual Operator - 427 PROJECT COUNT() - 427 SORT COUNT(), s_name - 427 GROUP BY HASH s_name AGGREGATE COUNT() - 68697 LEFT SEMI JOIN HASH ON (l_orderkey = l_orderkey) AND (l_suppkey <> l_suppkey) AND (l_receiptdate > l_commitdate) - 3791311 │└PROJECT - 3791311 │ FILTER (l_orderkey = l_orderkey) AND (l_suppkey <> l_suppkey) AND (l_receiptdate > l_commitdate) - 3791311 │ TABLE SCAN lineitem WHERE l_receiptdate > l_commitdate - 72701 LEFT SEMI JOIN HASH ON (l_orderkey = l_orderkey) AND (l_suppkey <> l_suppkey) - 5998820 │└PROJECT - 5998820 │ FILTER (l_orderkey = l_orderkey) AND (l_suppkey <> l_suppkey) - 5998820 │ TABLE SCAN lineitem - 75322 INNER JOIN HASH ON s_nationkey = n_nationkey - 1 │└TABLE SCAN nation WHERE n_name = 'GERMANY' - 1771781 INNER JOIN HASH ON l_suppkey = s_suppkey - 10000 │└TABLE SCAN supplier - 1771933 INNER JOIN HASH ON l_orderkey = o_orderkey - 776007 │└TABLE SCAN orders WHERE o_orderstatus = 'F' - 3791311 TABLE SCAN lineitem WHERE l_receiptdate > l_commitdate
DuckDB	Estimate Actual Operator - 396 SORT count_star(), s_name 881 396 GROUP BY HASH #0 AGGREGATE count_star() 1086 4060 PROJECT s_name 1086 0 RIGHT SEMI INNER JOIN HASH ON l_orderkey IS NOT DISTINCT FROM l_orderkeyl_suppkey IS NOT DISTINCT FROM l_suppkey 5430 74300 │└GROUP BY HASH #0, #1 AGGREGATE 1086 4060 │ RIGHT ANTI JOIN HASH ON l_orderkey IS NOT DISTINCT FROM l_orderkeyl_suppkey IS NOT DISTINCT FROM l_suppkey - 0 │ │└SCAN EMPTY 4488 194446 │ INNER JOIN HASH ON l_orderkey = l_orderkeyl_suppkey != l_suppkey 5430 0 │ │└SCAN MATERIALISED 1199764 3791176 │ FILTER l_receiptdate > l_commitdate 5998820 5998605 │ TABLE SCAN lineitem 5431 0 RIGHT SEMI INNER JOIN HASH ON l_orderkey IS NOT DISTINCT FROM l_orderkeyl_suppkey IS NOT DISTINCT FROM l_suppkey 27158 77151 │└GROUP BY HASH #1, #2 AGGREGATE 5431 74310 │ RIGHT SEMI INNER JOIN HASH ON l_orderkey IS NOT DISTINCT FROM l_orderkeyl_suppkey IS NOT DISTINCT FROM l_suppkey - 0 │ │└SCAN EMPTY 112243 306977 │ INNER JOIN HASH ON l_orderkey = l_orderkeyl_suppkey != l_suppkey 27158 0 │ │└SCAN MATERIALISED 5998820 5998605 │ TABLE SCAN lineitem 27159 77161 INNER JOIN HASH ON o_orderkey = l_orderkey 53838 149859 │└INNER JOIN HASH ON l_suppkey = s_suppkey 384 396 │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │ │└TABLE SCAN nation WHERE n_name = 'GERMANY' 10000 396 │ │ TABLE SCAN supplier 1199764 3780503 │ FILTER l_suppkey >= 1 1199764 3780503 │ FILTER l_receiptdate > l_commitdate 5998820 5981691 │ TABLE SCAN lineitem 750000 775675 TABLE SCAN orders WHERE o_orderstatus = 'F'
SQL Server	Estimate Actual Operator 400 396 SORT Expr1014, s_name 400 396 PROJECT CONVERT_IMPLICIT(int,Expr1017,0) AS Expr1014 400 396 GROUP BY HASH AGGREGATE COUNT(*) AS Expr1017 42231 4060 INNER JOIN HASH ON l_orderkey as l_orderkey = l_orderkey as l_orderkey AND l_suppkey as l_suppkey <> l_suppkey as l_suppkey 42231 74310 │└INNER JOIN HASH ON 71979 144552 │ │└INNER JOIN HASH ON l_orderkey as l_orderkey = l_orderkey as l_orderkey AND l_suppkey as l_suppkey <> l_suppkey as l_suppkey 71979 149859 │ │ │└INNER JOIN HASH ON s_suppkey = l_suppkey as l_suppkey 400 396 │ │ │ │└INNER JOIN HASH ON n_nationkey = s_nationkey 1 1 │ │ │ │ │└TABLE SEEK nation WHERE n_name = 'GERMANY' 10000 10000 │ │ │ │ TABLE SEEK supplier 1799650 3791311 │ │ │ TABLE SCAN lineitem WHERE l_receiptdate as l_receiptdate > l_commitdate as l_commitdate 5998820 5998820 │ │ TABLE SCAN lineitem 775534 775676 │ TABLE SEEK orders WHERE o_orderstatus = 'F' 1799650 3791311 TABLE SCAN lineitem WHERE l_receiptdate as l_receiptdate > l_commitdate as l_commitdate
PostgreSQL	Estimate Actual Operator 1 396 SORT COUNT(), s_name 1 396 GROUP BY SORT s_name AGGREGATE COUNT() 1 4060 SORT s_name 1 4060 INNER JOIN LOOP ON o_orderkey = l_orderkey 5 7980 │└LEFT SEMI JOIN HASH ON l_orderkey = l_orderkey AND (l_suppkey <> l_suppkey) 7498525 5998820 │ │└TABLE SCAN l2 5 13285 │ RIGHT ANTI JOIN HASH ON l_orderkey = l_orderkey AND (l_suppkey <> l_suppkey) 99690 149860 │ │└INNER JOIN HASH ON l_suppkey = s_suppkey 2000 1980 │ │ │└INNER JOIN HASH ON s_nationkey = n_nationkey 1 1 │ │ │ │└TABLE SCAN nation WHERE n_name = 'GERMANY' 10000 10000 │ │ │ TABLE SCAN supplier 2499510 3791310 │ │ TABLE SCAN l1 WHERE l_receiptdate > l_commitdate 2499510 3791310 │ TABLE SCAN l3 WHERE l_receiptdate > l_commitdate 7979 7979 TABLE SEEK orders WHERE o_orderstatus = 'F'