Skip to content

Instantly share code, notes, and snippets.

@BohuTANG

BohuTANG/clickhouse-21.9.1-numbers-table-performance

Created August 14, 2021 15:23
Show Gist options
  • Save BohuTANG/54b874bd3e5d4292c04b7a55c6f68ba3 to your computer and use it in GitHub Desktop.
Save BohuTANG/54b874bd3e5d4292c04b7a55c6f68ba3 to your computer and use it in GitHub Desktop.
Download ZIP
clickhouse-21.9.1-numbers-table-performance
Raw
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 :)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment