10x Performance drop on join queries on Clickhouse version 22.4 & 22.5

[shawel]

Setup

AWS: c5d.4xlarge

CREATE TEMPORARY TABLE _v AS
SELECT
    xxHash64(rand()) % 100000 AS _did,
    toString(number + (1 * 10000)) AS _pid,
    toString(number + (1 * 1000)) AS _aid,
    toString(number + (1 * 1000)) AS _sid,
    toDate(parseDateTimeBestEffort('2022-01-01') + (rand() % 2600000)) AS v_date
FROM numbers(1000000)

CREATE TEMPORARY TABLE _i AS
SELECT
    xxHash64(rand()) % 40000 AS _did,
    toDate(i_datestamp) AS i_date,
    parseDateTimeBestEffort('2022-01-15') - (rand() % 2600000) AS i_datestamp,
    toString((rand() % 99) + 1) AS ad__aid,
    toString((rand() % 99) + 1) AS ad__sid,
    xxHash32(number) % 2000 AS _dim_hash,
    rand() % 10 AS i_count
FROM numbers(40000000)

Query

SELECT * FROM ( SELECT * FROM _i ) INNER JOIN (SELECT _did, v_date FROM _v ) USING (_did) WHERE v_date > i_datestamp FORMAT `Null`

Result for various clickhouse versions

22.5.1.2079
0 rows in set. Elapsed: 23.645 sec. Processed 41.00 million rows, 1.48 GB (1.73 million rows/s., 62.54 MB/s.)

22.4.5.9 
0 rows in set. Elapsed: 23.577 sec. Processed 41.00 million rows, 1.48 GB (1.74 million rows/s., 62.72 MB/s.)

------

22.3.6.5
0 rows in set. Elapsed: 1.817 sec. Processed 41.00 million rows, 1.48 GB (22.56 million rows/s., 813.64 MB/s.)


22.2.3.5 
0 rows in set. Elapsed: 1.799 sec. Processed 41.00 million rows, 1.48 GB (22.79 million rows/s., 821.93 MB/s.)

22.1.4.30
0 rows in set. Elapsed: 2.966 sec. Processed 41.00 million rows, 1.32 GB (13.82 million rows/s., 444.66 MB/s.)

21.12.4.1
0 rows in set. Elapsed: 1.901 sec. Processed 41.00 million rows, 1.48 GB (21.57 million rows/s., 777.91 MB/s.)

21.11.11.1 
0 rows in set. Elapsed: 1.912 sec. Processed 41.00 million rows, 1.48 GB (21.44 million rows/s., 773.22 MB/s.)

21.10.5.3
0 rows in set. Elapsed: 1.992 sec. Processed 41.00 million rows, 1.48 GB (20.58 million rows/s., 742.30 MB/s.)

21.8.13.6 (our current production)
0 rows in set. Elapsed: 2.067 sec. Processed 41.00 million rows, 1.48 GB (19.84 million rows/s., 715.48 MB/s.)

[den-crane]

What is 21.4 & 2.15 in the title ?

[shawel]

@den-crane sry clickhouse version -- changed title