clickhouse-21.4.6-numbers-table-performance with max_threads=16

clickhouse-21.4.6-numbers-table-performance

ClickHouse client version 21.4.6.55 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.4.6 revision 54447.

thinkpad :) set max_threads=16;

SET max_threads = 16



thinkpad :) SELECT avg(number) FROM numbers_mt(100000000000);

SELECT avg(number)
FROM numbers_mt(100000000000)

Query id: 5d6f2da3-27fb-44f8-bd35-35dde02e0529

┌──────avg(number)─┐
│ 9323559.74711512 │
└──────────────────┘

1 rows in set. Elapsed: 6.037 sec. Processed 100.00 billion rows, 800.00 GB (16.57 billion rows/s., 132.52 GB/s.) 

thinkpad :) SELECT sum(number) FROM numbers_mt(100000000000);

SELECT sum(number)
FROM numbers_mt(100000000000)

Query id: 6ed49af7-c8ee-4288-a8f6-f7f4bf88a077

┌────────sum(number)─┐
│ 932355974711512064 │
└────────────────────┘

1 rows in set. Elapsed: 5.899 sec. Processed 100.00 billion rows, 800.00 GB (16.95 billion rows/s., 135.62 GB/s.) 

thinkpad :) SELECT min(number) FROM numbers_mt(100000000000);

SELECT min(number)
FROM numbers_mt(100000000000)

Query id: eec5e9ec-8e55-4b65-be54-1e7d49c71194

┌─min(number)─┐
│           0 │
└─────────────┘

1 rows in set. Elapsed: 13.059 sec. Processed 100.00 billion rows, 800.00 GB (7.66 billion rows/s., 61.26 GB/s.) 

thinkpad :) SELECT max(number) FROM numbers_mt(100000000000);

SELECT max(number)
FROM numbers_mt(100000000000)

Query id: 686c9a28-81be-48f8-9fa9-f4fabffe83ef

┌─max(number)─┐
│ 99999999999 │
└─────────────┘

1 rows in set. Elapsed: 14.070 sec. Processed 100.00 billion rows, 800.00 GB (7.11 billion rows/s., 56.86 GB/s.) 

thinkpad :) SELECT count(number) FROM numbers_mt(100000000000);

SELECT count(number)
FROM numbers_mt(100000000000)

Query id: 4941c7e4-8cff-4cd9-aa44-ceb50a187528

┌─count(number)─┐
│  100000000000 │
└───────────────┘

1 rows in set. Elapsed: 3.714 sec. Processed 100.00 billion rows, 800.00 GB (26.93 billion rows/s., 215.43 GB/s.) 

thinkpad :) SELECT sum(number+number+number) FROM numbers_mt(100000000000);

SELECT sum((number + number) + number)
FROM numbers_mt(100000000000)

Query id: 683e3b7a-3be1-4f16-bb9d-8558df49800d

┌─sum(plus(plus(number, number), number))─┐
│                     2797067924134536192 │
└─────────────────────────────────────────┘

1 rows in set. Elapsed: 233.718 sec. Processed 100.00 billion rows, 800.00 GB (427.87 million rows/s., 3.42 GB/s.) 

thinkpad :) SELECT sum(number) / count(number) FROM numbers_mt(100000000000);

SELECT sum(number) / count(number)
FROM numbers_mt(100000000000)

Query id: 233fb24d-c6f8-405c-936b-47bc110c33a7

┌─divide(sum(number), count(number))─┐
│                   9323559.74711512 │
└────────────────────────────────────┘

1 rows in set. Elapsed: 9.695 sec. Processed 100.00 billion rows, 800.00 GB (10.31 billion rows/s., 82.52 GB/s.) 

thinkpad :) SELECT sum(number) / count(number), max(number), min(number) FROM numbers_mt(100000000000);

SELECT
    sum(number) / count(number),
    max(number),
    min(number)
FROM numbers_mt(100000000000)

Query id: e8ed9704-db8c-4e04-aa8b-3f43f83554b5

┌─divide(sum(number), count(number))─┬─max(number)─┬─min(number)─┐
│                   9323559.74711512 │ 99999999999 │           0 │
└────────────────────────────────────┴─────────────┴─────────────┘

1 rows in set. Elapsed: 32.873 sec. Processed 100.00 billion rows, 800.00 GB (3.04 billion rows/s., 24.34 GB/s.) 

thinkpad :) SELECT number FROM numbers_mt(10000000000) ORDER BY number DESC LIMIT 10;

SELECT number
FROM numbers_mt(10000000000)
ORDER BY number DESC
LIMIT 10

Query id: 73ff82da-58fd-4e9e-ac23-cde6cba080fe

┌─────number─┐
│ 9999999999 │
│ 9999999998 │
│ 9999999997 │
│ 9999999996 │
│ 9999999995 │
│ 9999999994 │
│ 9999999993 │
│ 9999999992 │
│ 9999999991 │
│ 9999999990 │
└────────────┘

10 rows in set. Elapsed: 13.954 sec. Processed 10.00 billion rows, 80.00 GB (716.62 million rows/s., 5.73 GB/s.) 


thinkpad :) SELECT max(number), sum(number) FROM numbers_mt(1000000000) GROUP BY sipHash64(number % 3), sipHash64(number % 4)

SELECT
    max(number),
    sum(number)
FROM numbers_mt(1000000000)
GROUP BY
    sipHash64(number % 3),
    sipHash64(number % 4)

Query id: 06143ea1-6e8c-41d2-b3f3-7e82d7f7900a

┌─max(number)─┬───────sum(number)─┐
│   999999995 │ 41666666749999999 │
│   999999993 │ 41666666583333333 │
│   999999988 │ 41666666166666668 │
│   999999994 │ 41666666666666666 │
│   999999989 │ 41666666250000001 │
│   999999999 │ 41666667083333334 │
│   999999998 │ 41666667000000000 │
│   999999992 │ 41666666500000000 │
│   999999991 │ 41666666416666667 │
│   999999996 │ 41666666833333332 │
│   999999990 │ 41666666333333334 │
│   999999997 │ 41666666916666666 │
└─────────────┴───────────────────┘

12 rows in set. Elapsed: 10.240 sec. Processed 1.00 billion rows, 8.00 GB (97.65 million rows/s., 781.23 MB/s.)