On multi table join optimization

[yangshike]

At present, the performance of a single table is very good. However, the performance of two or more large table joins is not very good. I would like to know whether there has been any optimization for this area recently. Currently, other products have done better in this area, such as Apache Doris and ByteHouse

[linghengqian]

• You need to point out some plausible practical cases, as far as I've tested Apache Doris 1.1.1 locally, its join performance is lower than Clickhouse 22.8.4.7.

• Same at Inner join is more slow than `ClickHouse` apache/doris#11593 It is reflected that the execution time of Clickhouse's multi-table join is only 1/4 of that of Apache Doris by executing the same SQL.

[yangshike]

doris ： Server version: 5.1.0 Doris version 0.15.3-Unknown
mysql> select count()
-> from t_mobile_addition_data a
-> left join t_customer b on a.mobile_encrypt=b.mobile_encrypt
-> left join t_live_entry_record c on b.id=c.customer_id
-> where a.mobile_encrypt is not null and c.created_time >= DATE_ADD(a.created_time, INTERVAL 48 hour)
-> and a.created_time >= '2022-08-01' and a.created_time < '2022-08-31'
-> union all
-> select count()
-> from t_mobile_addition_data a
-> left join t_customer b on a.mobile_encrypt=b.mobile_encrypt
-> left join t_live_entry_record c on b.id=c.customer_id
-> where a.mobile_encrypt is not null and c.status is null
-> and a.created_time >= '2022-08-01' and a.created_time < '2022-08-31';

2 rows in set (17.47 sec)

clickhouse：clickHouse server version 22.8.4 revision 54460

clickhouse01 :) select count()
from t_mobile_addition_data a
left join t_customer b on a.mobile_encrypt=b.mobile_encrypt
left join t_live_entry_record c on b.id=c.customer_id
where a.mobile_encrypt is not null and c.created_time >= DATE_ADD(a.created_time, INTERVAL 48 hour)
and a.created_time >= '2022-08-01' and a.created_time < '2022-08-31'
union all
select count()
from t_mobile_addition_data a
left join t_customer b on a.mobile_encrypt=b.mobile_encrypt
left join t_live_entry_record c on b.id=c.customer_id
where a.mobile_encrypt is not null and c.status is null
and a.created_time >= '2022-08-01' and a.created_time < '2022-08-31';

SELECT count()
FROM t_mobile_addition_data AS a
LEFT JOIN t_customer AS b ON a.mobile_encrypt = b.mobile_encrypt
LEFT JOIN t_live_entry_record AS c ON b.id = c.customer_id
WHERE (a.mobile_encrypt IS NOT NULL) AND (c.created_time >= (a.created_time + toIntervalHour(48))) AND (a.created_time >= '2022-08-01') AND (a.created_time < '2022-08-31')
UNION ALL
SELECT count()
FROM t_mobile_addition_data AS a
LEFT JOIN t_customer AS b ON a.mobile_encrypt = b.mobile_encrypt
LEFT JOIN t_live_entry_record AS c ON b.id = c.customer_id
WHERE (a.mobile_encrypt IS NOT NULL) AND (c.status IS NULL) AND (a.created_time >= '2022-08-01') AND (a.created_time < '2022-08-31')

Query id: d1c4559a-705d-4298-9388-a76104aad7e0

2 rows in set. Elapsed: 27.916 sec. Processed 220.19 million rows, 5.30 GB (7.89 million rows/s., 189.91 MB/s.)

[yangshike]

clickhouse01 :) select count(*) from t_mobile_addition_data;

SELECT count(*)
FROM t_mobile_addition_data

Query id: 4263363a-5e1e-450c-9e76-b6c3587b55b6

┌──count()─┐
│ 73676045 │
└──────────┘

1 row in set. Elapsed: 0.002 sec.

clickhouse01 :) select count(*) from t_customer;

SELECT count(*)
FROM t_customer

Query id: aa2317af-bd74-4f8e-a935-abf8f16bd044

┌──count()─┐
│ 59021614 │
└──────────┘

1 row in set. Elapsed: 0.002 sec.

clickhouse01 :) select count(*) from t_live_entry_record;

SELECT count(*)
FROM t_live_entry_record

Query id: 121a50b9-258c-4611-8a04-b525ebfeb111

┌──count()─┐
│ 44899600 │
└──────────┘

1 row in set. Elapsed: 0.002 sec.

[yangshike]

doris 3 nodes (3 replics) : 32 cpu core, 128GB memory 2 * 1400G ESSD
clickhouse 3 nodes(3 replics, 1 shard) : 32 cpu core, 128GB memory 12 * 5T HDD

[yangshike]

This SQL is synchronized from MySQL and is not optimized for Clickhouse. After optimization, it also takes about 10 seconds,

[glebinc]

@yangshike Could you share the version optimized for Clickhouse, please?