CH 22.12 join_algorithm

ch_join_algorithm.sql

create table A (A Int64, B Int64, S String) Engine=MergeTree order by A 
as select number,number, toString(arrayMap(i->cityHash64(i*number), range(100))) from numbers(1e7);


select * from A a join A as b on a.A = b.A format Null;


SET join_algorithm = 'hash';
Peak memory usage (for query): 32.62 GiB.
0 rows in set. Elapsed: 20.700 sec. Processed 20.00 million rows, 41.31 GB (966.18 thousand rows/s., 2.00 GB/s.)


SET join_algorithm = 'parallel_hash';
Peak memory usage (for query): 33.77 GiB.
0 rows in set. Elapsed: 32.303 sec. Processed 20.00 million rows, 41.31 GB (619.14 thousand rows/s., 1.28 GB/s.)


SET join_algorithm = 'partial_merge';
Peak memory usage (for query): 1.87 GiB.
1 row in set. Elapsed: 112.832 sec. Processed 10.08 million rows, 20.82 GB (89.35 thousand rows/s., 184.54 MB/s.)


SET join_algorithm = 'full_sorting_merge', optimize_sorting_by_input_stream_properties = 1;
Received exception from server (version 22.12.1):
Code: 241. DB::Exception: Received from localhost:9000. DB::Exception: Memory limit (for query) exceeded: would use 47.11 GiB (attempt to allocate chunk of 8455728 bytes), maximum: 47.11 GiB.



SET join_algorithm = 'grace_hash', grace_hash_join_initial_buckets=1024, grace_hash_join_max_buckets=1024;
Peak memory usage (for query): 17.79 GiB.
0 rows in set. Elapsed: 183.640 sec. Processed 20.00 million rows, 41.31 GB (108.91 thousand rows/s., 224.94 MB/s.)

SET join_algorithm = 'grace_hash', grace_hash_join_initial_buckets=512, grace_hash_join_max_buckets=512;
Peak memory usage (for query): 9.34 GiB.
0 rows in set. Elapsed: 159.831 sec. Processed 20.00 million rows, 41.31 GB (125.13 thousand rows/s., 258.44 MB/s.)

SET join_algorithm = 'grace_hash', grace_hash_join_initial_buckets=256, grace_hash_join_max_buckets=256;
Peak memory usage (for query): 4.91 GiB.
0 rows in set. Elapsed: 169.754 sec. Processed 20.00 million rows, 41.31 GB (117.82 thousand rows/s., 243.34 MB/s.)

SET join_algorithm = 'grace_hash', grace_hash_join_initial_buckets=128, grace_hash_join_max_buckets=128;
Peak memory usage (for query): 2.79 GiB.
0 rows in set. Elapsed: 151.914 sec. Processed 20.00 million rows, 41.31 GB (131.65 thousand rows/s., 271.91 MB/s.)

SET join_algorithm = 'grace_hash', grace_hash_join_initial_buckets=64, grace_hash_join_max_buckets=64;
Peak memory usage (for query): 2.44 GiB.
0 rows in set. Elapsed: 143.334 sec. Processed 20.00 million rows, 41.31 GB (139.53 thousand rows/s., 288.19 MB/s.)

SET join_algorithm = 'grace_hash', grace_hash_join_initial_buckets=32, grace_hash_join_max_buckets=32;
Peak memory usage (for query): 3.22 GiB.
0 rows in set. Elapsed: 130.363 sec. Processed 20.00 million rows, 41.31 GB (153.42 thousand rows/s., 316.87 MB/s.)

SET join_algorithm = 'grace_hash', grace_hash_join_initial_buckets=16, grace_hash_join_max_buckets=16;
Peak memory usage (for query): 4.47 GiB.
0 rows in set. Elapsed: 127.632 sec. Processed 20.00 million rows, 41.31 GB (156.70 thousand rows/s., 323.64 MB/s.)

SET join_algorithm = 'grace_hash', grace_hash_join_initial_buckets=8, grace_hash_join_max_buckets=8;
Peak memory usage (for query): 6.35 GiB.
0 rows in set. Elapsed: 116.621 sec. Processed 20.00 million rows, 41.31 GB (171.50 thousand rows/s., 354.20 MB/s.)

SET join_algorithm = 'grace_hash', grace_hash_join_initial_buckets=4, grace_hash_join_max_buckets=4;
Peak memory usage (for query): 12.16 GiB.
0 rows in set. Elapsed: 105.209 sec. Processed 20.00 million rows, 41.31 GB (190.10 thousand rows/s., 392.62 MB/s.)

SET join_algorithm = 'grace_hash', grace_hash_join_initial_buckets=2, grace_hash_join_max_buckets=2;
Peak memory usage (for query): 24.34 GiB.
0 rows in set. Elapsed: 99.727 sec. Processed 20.00 million rows, 41.31 GB (200.55 thousand rows/s., 414.21 MB/s.)

SET join_algorithm = 'grace_hash', grace_hash_join_initial_buckets=1, grace_hash_join_max_buckets=1;
DB::Exception: Memory limit (for query) exceeded: would use 47.97 GiB (attempt to allocate chunk of 8589934592 bytes), maximum: 47.11 GiB. OvercommitTracker decision: Memory overcommit isn't used. OvercommitTracker isn't set.: While executing DelayedJoinedBlocksTransform. (MEMORY_LIMIT_EXCEEDED)