Planner — TPC-H — TPCH-Q04

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 3,843,682 3.8M Rank	Estimation Error Est Err Row Operations Ops 52,371 52K Rank	Estimation Error Est Err Row Operations Ops 5 5 Rank	Estimation Error Est Err Row Operations Ops 3,791,311 3.8M Rank	Estimation Error Est Err Row Operations Ops 52,371 52K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
DuckDB	Estimation Error Est Err Row Operations Ops 6,050,773 6.1M Rank	Estimation Error Est Err Row Operations Ops 3,974,931 4M Rank	Estimation Error Est Err Row Operations Ops 5 5 Rank	Estimation Error Est Err Row Operations Ops 231,607 232K Rank	Estimation Error Est Err Row Operations Ops 47,831 48K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
PostgreSQL	Estimation Error Est Err Row Operations Ops 3,843,516 3.8M Rank	Estimation Error Est Err Row Operations Ops 52,204 52K Rank	Estimation Error Est Err Row Operations Ops 47,832 48K Rank	Estimation Error Est Err Row Operations Ops 3,791,312 3.8M Rank	Estimation Error Est Err Row Operations Ops 47,852 48K Rank	Estimation Error Est Err Row Operations Ops 0 0 Rank
SQL Server	Estimation Error Est Err Row Operations Ops 260,636 261K Rank	Estimation Error Est Err Row Operations Ops 208,432 208K Rank	Estimation Error Est Err Row Operations Ops 5 5 Rank	Estimation Error Est Err Row Operations Ops 52,204 52K Rank	Estimation Error Est Err Row Operations Ops 47,831 48K 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 PROJECT COUNT() - 5 SORT o_orderpriority - 5 GROUP BY HASH o_orderpriority AGGREGATE COUNT() - 52371 LEFT SEMI JOIN HASH ON TRUE - 3791311 │└PROJECT - 3791311 │ PROJECT l_orderkey - 3791311 │ TABLE SCAN lineitem WHERE (l_commitdate < l_receiptdate) AND 1 = 1 - 52371 TABLE SCAN orders WHERE (o_orderdate >= '1995-02-01') AND (o_orderdate < '1995-05-01')`
DuckDB	Estimate Actual Operator - 5 SORT o_orderpriority 5 5 GROUP BY HASH #0 AGGREGATE count_star() 60000 47831 PROJECT o_orderpriority 60000 0 RIGHT SEMI INNER JOIN HASH ON o_orderkey IS NOT DISTINCT FROM o_orderkey 271669 52204 │└GROUP BY HASH #0 AGGREGATE 60000 47831 │ RIGHT SEMI INNER JOIN HASH ON o_orderkey IS NOT DISTINCT FROM o_orderkey - 0 │ │└SCAN EMPTY 449122 131572 │ PROJECT o_orderkey 449122 131572 │ INNER JOIN HASH ON l_orderkey = o_orderkey 271669 0 │ │└SCAN MATERIALISED 1199764 3791155 │ FILTER l_commitdate < l_receiptdate 5998820 5998569 │ TABLE SCAN lineitem 300000 52204 TABLE SCAN orders WHERE o_orderdate >= '1995-02-01' AND o_orderdate < '1995-05-01'
PostgreSQL	`Estimate Actual Operator 5 5 GROUP BY SORT o_orderpriority AGGREGATE COUNT() 20 20 GROUP BY SORT o_orderpriority AGGREGATE PARTIALCOUNT() 20760 47832 SORT o_orderpriority 20760 47832 LEFT SEMI JOIN HASH ON o_orderkey = l_orderkey 1999608 3791312 │└TABLE SCAN lineitem WHERE l_commitdate < l_receiptdate 70248 52204 TABLE SCAN orders WHERE (o_orderdate >= '1995-02-01') AND (o_orderdate < '1995-05-01')`
SQL Server	`Estimate Actual Operator 5 5 SORT o_orderpriority 5 5 PROJECT CONVERT_IMPLICIT(int,Expr1013,0) AS Expr1006 5 5 GROUP BY HASH AGGREGATE COUNT(*) AS Expr1013 42114 47831 INNER JOIN HASH ON l_orderkey = o_orderkey 53073 52204 │└TABLE SEEK orders WHERE o_orderdate >= '1995-02-01' AND o_orderdate < '1995-05-01' 599882 208432 TABLE SEEK lineitem WHERE (BLOOM(l_orderkey)) AND (l_commitdate < l_receiptdate)`

Commentary

## EXIST decorrelation into SEMI JOIN

This expression shoudl be decorrelated:

EXISTS (SELECT *
          FROM tpch.lineitem
         WHERE l_orderkey = o_orderkey
           AND l_commitdate < l_receiptdate)

Optimizers will generally convert this into a SEMI JOIN.

Flipping the inner and outer side of the join (for optimisers that know how to do this) will greatly reduce the amount of memory consumed and it opens up additional opportunities for bloom filtering.