why chdb is always slower than clickhouse local

[l1t1]

chdb 1.2

:) select cast(log10(i) as int),count(*)  FROM (select number+1 i from numbers(100000000)) group by 1;                                                                          ┏━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━┓
┃ CAST(log10(i), 'int') ┃  count() ┃
┡━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━┩                                                    │                     0 │        9 │
├───────────────────────┼──────────┤
│                     4 │    90000 │
├───────────────────────┼──────────┤                                                    │                     3 │     9000 │
├───────────────────────┼──────────┤
│                     2 │      900 │
├───────────────────────┼──────────┤                                                    │                     5 │   900000 │
├───────────────────────┼──────────┤
│                     1 │       90 │
├───────────────────────┼──────────┤
│                     6 │  9000000 │
├───────────────────────┼──────────┤                                                    │                     7 │ 90000000 │
├───────────────────────┼──────────┤
│                     8 │        1 │
└───────────────────────┴──────────┘

4.151766061782837
:) select cast(length(cast(i as varchar)) as int),count()FROM (select number+1 i from numbers(100000000)) group by 1;                                                           ┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━┓                                  ┃ CAST(length(CAST(i, 'varchar')), 'int') ┃  count() ┃                                  ┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━┩
│                                       4 │     9000 │
├─────────────────────────────────────────┼──────────┤
│                                       3 │      900 │
├─────────────────────────────────────────┼──────────┤
│                                       2 │       90 │                                  ├─────────────────────────────────────────┼──────────┤                                  │                                       5 │    90000 │
├─────────────────────────────────────────┼──────────┤
│                                       1 │        9 │
├─────────────────────────────────────────┼──────────┤
│                                       6 │   900000 │
├─────────────────────────────────────────┼──────────┤
│                                       7 │  9000000 │
├─────────────────────────────────────────┼──────────┤
│                                       9 │        1 │
├─────────────────────────────────────────┼──────────┤
│                                       8 │ 90000000 │
└─────────────────────────────────────────┴──────────┘

4.171696186065674
:) select sum(log(i))FROM (select number+1 i from numbers(100000000)) ;
┏━━━━━━━━━━━━━━━━━━━━┓
┃        sum(log(i)) ┃                                                                  ┡━━━━━━━━━━━━━━━━━━━━┩
│ 1742068084.5245106 │
└────────────────────┘
                                                                                        2.387582302093506
 

clickhouse 23.11

ClickHouse local version 23.11.1.602 (official build).                                  
localhost.localdomain :) select cast(log10(i) as int),count(*)  FROM (select number+1 i
from numbers(100000000)) group by 1;

SELECT
    CAST(log10(i), 'int'),
    count(*)                                                                            FROM                                                                                    (
    SELECT number + 1 AS i
    FROM numbers(100000000)                                                             )
GROUP BY 1
                                                                                        Query id: 9c57e920-8fad-4f58-a9bd-05806bcd9708                                          
┌─CAST(log10(i), 'int')─┬──count()─┐
│                     0 │        9 │                                                    │                     4 │    90000 │
│                     3 │     9000 │
│                     2 │      900 │
│                     5 │   900000 │
│                     1 │       90 │
│                     6 │  9000000 │
│                     7 │ 90000000 │
│                     8 │        1 │                                                    └───────────────────────┴──────────┘

9 rows in set. Elapsed: 3.770 sec. Processed 98.05 million rows, 784.38 MB (26.00 millio
n rows/s., 208.03 MB/s.)
Peak memory usage: 53.64 KiB.                                                           
localhost.localdomain :) select cast(length(cast(i as varchar)) as int),count()FROM (sel
ect number+1 i from numbers(100000000)) group by 1;

SELECT
    CAST(length(CAST(i, 'varchar')), 'int'),
    count()
FROM
(
    SELECT number + 1 AS i
    FROM numbers(100000000)
)
GROUP BY 1

Query id: 458cd2e8-6c7b-4d3d-89eb-33230536e867

┌─CAST(length(CAST(i, 'varchar')), 'int')─┬──count()─┐
│                                       4 │     9000 │
│                                       3 │      900 │
│                                       2 │       90 │
│                                       5 │    90000 │
│                                       1 │        9 │
│                                       6 │   900000 │
│                                       7 │  9000000 │
│                                       9 │        1 │
│                                       8 │ 90000000 │
└─────────────────────────────────────────┴──────────┘

9 rows in set. Elapsed: 3.608 sec. Processed 97.85 million rows, 782.81 MB (27.12 millio
n rows/s., 216.98 MB/s.)
Peak memory usage: 54.54 KiB.
localhost.localdomain :) select sum(log(i))FROM (select number+1 i from numbers(10000000
0)) ;

SELECT sum(log(i))
FROM
(
    SELECT number + 1 AS i
    FROM numbers(100000000)
)
                                                                                        Query id: 4bd4b967-38fa-4986-aa7a-04447dc7f791

┌────────sum(log(i))─┐                                                                  │ 1742068084.5245106 │
└────────────────────┘

1 row in set. Elapsed: 1.968 sec. Processed 96.09 million rows, 768.69 MB (48.83 million rows/s., 390.62 MB/s.)

[auxten]

Which chdb CLI are you using?

[l1t1]

I use python

[auxten]

This CLI is not maintained. Please try chdb-go-cli.