ClickHouse ® JOINs... 100x faster

The recent introduction of two pull requests in ClickHouse® 24.4 has significantly enhanced the performance of JOIN operations, with some queries experiencing speed increases by multiple orders of magnitude. The first pull request implemented a more sophisticated predicate pushdown technique using equivalence classes, which allows predicates to be applied to both sides of a JOIN operation, enhancing query performance by reducing the data processed. This technique leverages the equivalence of JOINed columns to optimize the logical query plan, improving the execution of INNER, LEFT, and RIGHT JOINs. The second pull request introduced a mechanism for automatically converting OUTER JOINs to INNER JOINs when predicates filter out non-joined rows with default values, further optimizing query execution by enabling more scenarios for predicate pushdown. These optimizations have resolved preexisting issues within ClickHouse® and have resulted in substantial improvements in query performance, making data processing more efficient for users.