clickhouse-21.9.1-numbers-table-performance

clickhouse-21.9.1-numbers-table-performance

bohu@thinkpad:~/github/cppwork/ClickHouse/build$ ./programs/clickhouse client
ClickHouse client version 21.9.1.1.
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.9.1 revision 54449.

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

SELECT avg(number)
FROM numbers_mt(100000000000)

Query id: e78bb289-5a80-4d9b-8aa9-090e63a8d5c3

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

1 rows in set. Elapsed: 6.570 sec. Processed 100.00 billion rows, 800.00 GB (15.22 billion rows/s., 121.76 GB/s.)

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

SELECT sum(number)
FROM numbers_mt(100000000000)

Query id: dd5d089a-cf53-4a32-8b66-8bb79994a78f

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

1 rows in set. Elapsed: 6.596 sec. Processed 100.00 billion rows, 800.00 GB (15.16 billion rows/s., 121.28 GB/s.)

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

SELECT min(number)
FROM numbers_mt(100000000000)

Query id: 83a486b9-0ccc-4b29-bf3c-7dc64bab8b28

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

1 rows in set. Elapsed: 9.805 sec. Processed 100.00 billion rows, 800.00 GB (10.20 billion rows/s., 81.59 GB/s.)

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

SELECT max(number)
FROM numbers_mt(100000000000)

Query id: 52953637-3658-4758-80e9-a573ca8e0aef

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

1 rows in set. Elapsed: 14.719 sec. Processed 100.00 billion rows, 800.00 GB (6.79 billion rows/s., 54.35 GB/s.)

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

SELECT count(number)
FROM numbers_mt(100000000000)

Query id: ccd7a979-3322-4167-b7a5-07b20d2446c3

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

1 rows in set. Elapsed: 4.271 sec. Processed 100.00 billion rows, 800.00 GB (23.41 billion rows/s., 187.31 GB/s.)

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

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

Query id: d1f94411-6519-4540-a880-7dd89d1074d7

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

1 rows in set. Elapsed: 80.773 sec. Processed 100.00 billion rows, 800.00 GB (1.24 billion rows/s., 9.90 GB/s.)

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

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

Query id: 546ceafa-00a1-44b8-b031-aa2afb952628

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

1 rows in set. Elapsed: 7.977 sec. Processed 100.00 billion rows, 800.00 GB (12.54 billion rows/s., 100.29 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: c91155c4-f488-4fc6-b7aa-620ea9e33681

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

1 rows in set. Elapsed: 26.039 sec. Processed 100.00 billion rows, 800.00 GB (3.84 billion rows/s., 30.72 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: 710aa1b0-6d47-43a8-bff6-5b711229061d

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

10 rows in set. Elapsed: 3.268 sec. Processed 10.00 billion rows, 80.00 GB (3.06 billion rows/s., 24.48 GB/s.)

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

SELECT
    max(number),
    sum(number)
FROM numbers_mt(1000000000)
GROUP BY
    number % 3,
    number % 4,
    number % 5
LIMIT 10

Query id: 16555c60-820b-40df-8390-88d0afb1f1ba

┌─max(number)─┬──────sum(number)─┐
│   999999960 │ 8333333166666660 │
│   999999982 │ 8333333533333334 │
│   999999984 │ 8333333566666668 │
│   999999955 │ 8333333083333330 │
│   999999991 │ 8333333683333337 │
│   999999959 │ 8333333149999994 │
│   999999958 │ 8333333133333328 │
│   999999956 │ 8333333099999996 │
│   999999964 │ 8333333233333328 │
│   999999942 │ 8333332866666672 │
└─────────────┴──────────────────┘

10 rows in set. Elapsed: 2.157 sec. Processed 1.00 billion rows, 8.00 GB (463.62 million rows/s., 3.71 GB/s.)

thinkpad :)