datafuse-v0.4.40-nightly-numbers-table-performance with max_threads=16

datafuse-0.4.1-numbers-table-performance

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

ClickHouse client version is older than ClickHouse server. It may lack support for new features.

datafuse :) 

SET max_threads = 16

Query id: dd69f8d3-bea8-4eae-9b72-d1163a57681b

Ok.

0 rows in set. Elapsed: 0.002 sec. 

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


SELECT avg(number)
FROM numbers_mt(100000000000)

Query id: e05347f1-47e3-4760-b882-675eab7b3983

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

1 rows in set. Elapsed: 4.354 sec. Processed 100.00 billion rows, 800.72 GB (22.97 billion rows/s., 183.91 GB/s.) 

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

SELECT sum(number)
FROM numbers_mt(100000000000)

Query id: e0aab336-b659-414d-9249-2fa0cdaf3075

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

1 rows in set. Elapsed: 4.203 sec. Processed 100.00 billion rows, 800.72 GB (23.79 billion rows/s., 190.50 GB/s.) 

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

SELECT min(number)
FROM numbers_mt(100000000000)

Query id: dc7b3b32-445b-41ad-8319-d1feb7da4f07

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

1 rows in set. Elapsed: 4.923 sec. Processed 100.00 billion rows, 800.72 GB (20.31 billion rows/s., 162.64 GB/s.)


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


SELECT max(number)
FROM numbers_mt(100000000000)

Query id: 6c29dcdd-ce26-4629-a237-ad462190f4c0

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

1 rows in set. Elapsed: 4.772 sec. Processed 100.00 billion rows, 800.72 GB (20.95 billion rows/s., 167.78 GB/s.) 

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

SELECT count(number)
FROM numbers_mt(100000000000)

Query id: 3458f862-792e-4735-8622-70daac2d8095

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

1 rows in set. Elapsed: 2.913 sec. Processed 100.00 billion rows, 800.72 GB (34.33 billion rows/s., 274.90 GB/s.) 

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

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

Query id: f1ea6007-25a9-4eb5-88f2-8cd0baf49e46

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

1 rows in set. Elapsed: 17.853 sec. Processed 100.00 billion rows, 800.72 GB (5.60 billion rows/s., 44.85 GB/s.) 

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

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

Query id: 9d7740c5-77e4-4512-8bf0-ce5ac0784c6c

┌─sum(((number + number) + number))─┐
│               2797067924134536192 │
└───────────────────────────────────┘

1 rows in set. Elapsed: 19.834 sec. Processed 100.00 billion rows, 800.72 GB (5.04 billion rows/s., 40.37 GB/s.) 

datafuse :) 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: 9af7a6c3-554f-44a9-b4d8-e67f5101452f

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

1 rows in set. Elapsed: 8.284 sec. Processed 100.00 billion rows, 800.72 GB (12.07 billion rows/s., 96.66 GB/s.) 

datafuse :) SELECT number FROM numbers_mt(10000000000) ORDER BY number DESC LIMIT 100;

datafuse :) SELECT number FROM numbers_mt(10000000000) ORDER BY number DESC LIMIT 100

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

Query id: 2cb0736f-35a0-4b92-a5ef-1f70bbaec942

100 rows in set. Elapsed: 4.802 sec. Processed 10.00 billion rows, 80.07 GB (2.08 billion rows/s., 16.67 GB/s.)


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

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

Query id: 19b84df5-ab56-4e1a-88d9-f6fd1eb26f1d

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

12 rows in set. Elapsed: 14.842 sec. Processed 1.00 billion rows, 8.01 GB (67.38 million rows/s., 539.51 MB/s.)