query_plan_join_swap_table doesn't perform as expected with WHERE #78441
Description
Company or project name
Movable Ink
Describe the unexpected behaviour
SETTINGS query_plan_join_swap_table='auto' (or leaving it as default) doesn't swap the join tables, when there is a WHERE condition
How to reproduce
ClickHouse version: 25.3.1.2703
It's a self-hosted container, connected with clickhouse client
CREATE TABLE t1
(
`col1` String
)
ENGINE = ReplacingMergeTree
ORDER BY col1;
CREATE TABLE t2
(
`col1` String,
`col2` String
)
ENGINE = ReplacingMergeTree
ORDER BY (col1, col2);
INSERT INTO t1 SELECT rand64()
FROM numbers(10_000_000);
INSERT INTO t2 SELECT col1, 'ABC'
FROM t1;
INSERT INTO t2 SELECT rand64(), 'XYZ'
FROM numbers(100_000_000);-- t1 has 10 million rows, 150.44 MiB on disk
-- t2 has 110 million rows, 1.57 GiB on disk
SELECT
table,
formatReadableQuantity(sum(rows)) AS rows,
formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active AND database = 'default' AND table LIKE 't%'
GROUP BY table
ORDER BY sum(bytes_on_disk) DESC;-- Elapsed: 4.945 sec. Processed 120.00 million rows, 4.73 GB (24.26 million rows/s., 955.96 MB/s.) Peak memory usage: 15.34 GiB.
SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 WHERE t1.col1 != '' SETTINGS query_plan_join_swap_table = 'auto';
-- Elapsed: 4.420 sec. Processed 120.00 million rows, 4.73 GB (27.15 million rows/s., 1.07 GB/s.) Peak memory usage: 2.73 GiB.
SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 WHERE t1.col1 != '' SETTINGS query_plan_join_swap_table = 'true';
-- Elapsed: 4.977 sec. Processed 120.00 million rows, 4.73 GB (24.11 million rows/s., 949.86 MB/s.) Peak memory usage: 15.34 GiB.
SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 WHERE t1.col1 != '' SETTINGS query_plan_join_swap_table = 'false';It could be inferred from the results that running with query_plan_join_swap_table = 'auto' seems to be the same as query_plan_join_swap_table = 'false'
This can be confirmed with EXPLAIN actions=0
EXPLAIN actions = 0
SELECT *
FROM t1
LEFT JOIN t2 ON t1.col1 = t2.col1
WHERE t1.col1 != ''
SETTINGS query_plan_join_swap_table = 'auto'
┌─explain───────────────────────────────────────────────────────┐
1. │ Expression ((Project names + (Projection + ))) │
2. │ Expression │
3. │ Join │
4. │ Filter (( + Change column names to column identifiers)) │
5. │ ReadFromMergeTree (default.t1) │
6. │ Expression │
7. │ ReadFromMergeTree (default.t2) │
└───────────────────────────────────────────────────────────────┘as opposed to running the same query but with SETTINGS query_plan_join_swap_table = 'true'
EXPLAIN actions = 0
SELECT *
FROM t1
LEFT JOIN t2 ON t1.col1 = t2.col1
WHERE t1.col1 != ''
SETTINGS query_plan_join_swap_table = 'true'
┌─explain───────────────────────────────────────────────────────┐
1. │ Expression ((Project names + (Projection + ))) │
2. │ Expression │
3. │ Join │
4. │ Expression │
5. │ ReadFromMergeTree (default.t2) │
6. │ Filter (( + Change column names to column identifiers)) │
7. │ ReadFromMergeTree (default.t1) │
└───────────────────────────────────────────────────────────────┘Note 1: the WHERE in this case doesn't really filter out any rows
SELECT COUNT()
FROM t1
WHERE col1 = ''
┌─COUNT()─┐
1. │ 0 │
└─────────┘Note 2: if the WHERE is removed, things work as expected
EXPLAIN actions = 0
SELECT *
FROM t1
LEFT JOIN t2 ON t1.col1 = t2.col1
SETTINGS query_plan_join_swap_table = 'auto'
┌─explain──────────────────────────────────────────────────────┐
1. │ Expression ((Project names + Projection)) │
2. │ Expression │
3. │ Join │
4. │ Expression (Change column names to column identifiers) │
5. │ ReadFromMergeTree (default.t2) │
6. │ Expression (Change column names to column identifiers) │
7. │ ReadFromMergeTree (default.t1) │
└──────────────────────────────────────────────────────────────┘Expected behavior
SETTINGS query_plan_join_swap_table='auto' should swap the join table based on estimations of the table sizes, and use the smaller one for the build table.
Error message and/or stacktrace
No response
Additional context
No response