my test of duckdb vs clickhouse vs databend

[l1t1]

test on a PC with 8core CPU 8GB memory linux(free mem 6GB)	duckdb	clickhouse	databend
query a range
SELECT avg(i) FROM range(1,1000000000)t(i) group by round(log10(i));	64.068		36.979
SELECT avg(number) FROM numbers(1,1000000000)t group by round(log(number));		20.243
SELECT avg(number) FROM numbers_mt(1,1000000000)t group by round(log(number));		2.691
SELECT avg(number) FROM numbers(1000000000)t group by round(log(number));			5.475
SELECT avg(number) FROM numbers_mt(1000000000)t group by round(log(number));			5.634
SELECT avg(i::int) FROM range(1,1000000000)t(i) group by round(log10(i));	63.27
query a mem table
create table t as select i::int i from range(1,1000000000)t(i);	9.898
create table t engine=Memory as SELECT number::int i FROM numbers(1,1000000000)t;		6.451
create table t engine=memory as SELECT i::int i FROM numbers(1000000000)t(i);			1.275
SELECT avg(i) FROM t group by round(log10(i));	9.336	5.742	5.351
SELECT avg(i) FROM t group by round(ln(i))	8.378
SELECT avg(i) FROM t group by round(log(i));		2.745	4.998
query a file table
create table t as select i::int i from range(1,1000000000)t(i);	21.786
create table t engine=MergeTree order by i as SELECT number::int i FROM numbers(1,1000000000)t;		85.022
create table t storage_format='native' compression='lz4' as SELECT i::int i FROM numbers(1000000000)t(i);			126.15
SELECT avg(i) FROM t group by round(log10(i));	9.246	7.602	6.525
SELECT avg(i) FROM t group by round(log(i));		3.615	6.339
create table tz storage_format='native' compression='zstd' as SELECT i::int i FROM numbers(1000000000)t(i);		126.095
SELECT avg(i) FROM tz group by round(log10(i));			9.296
SELECT avg(i) FROM tz group by round(log(i));			12.575
create table tpz storage_format='parquet' compression='zstd' as SELECT i::int i FROM numbers(1000000000)t(i);		130.245
SELECT avg(i) FROM tpz group by round(log10(i));			12.24
SELECT avg(i) FROM tpz group by round(log(i));			12.082
query a temp directory table
set temp_directory='tmp.tmp';set memory_limit='5GB';	8.995
create table t engine=Memory as SELECT number i FROM numbers(1,1000000000)t;		23.458
SELECT avg(i) FROM t group by round(log10(i));	25.524	36.979
query a parquet file
copy (select i::int i from range(1,1000000000)t(i)) to 't.parquet'; -4GB	36.724
SELECT avg(i) FROM 't.parquet' group by round(log10(i));	9.453
copy (select i::int i from range(1,1000000000)t(i)) to 'tu.parquet' (codec 'uncompressed'); --4GB	35.608
SELECT avg(i) FROM 'tu.parquet' group by round(log10(i));	9.367
copy (select i::int i from range(1,1000000000)t(i)) to 'tpz.parquet' (codec 'snappy');  --4GB	36.686
SELECT avg(i) FROM 'tpz.parquet' group by round(log10(i));	9.452
copy (select i::int i from range(1,1000000000)t(i)) to 'tgz.parquet' (codec 'gzip');  -1.4GB	819.954
SELECT avg(i) FROM 'tgz.parquet' group by round(log10(i));	10.892
copy (select i::int i from range(1,1000000000)t(i)) to 'tz.parquet' (codec 'ZSTD'); --2.7GB	45.009
SELECT avg(i) FROM 'tz.parquet' group by round(log10(i));	9.82
copy (select i::int i from range(1,1000000000)t(i)) to 't.csv' (header '1');  --9.9GB	72.943
SELECT avg(i) FROM 't.csv' group by round(log10(i));	143.343
create stage lake;
copy into @lake from(select i::int i from range(1,1000000000)t(i))file_format=(type=parquet); --4GB,15 files		435.898
SELECT avg(i) FROM @lake (pattern => '.*parquet') group by round(log10(i)) ;			35.683
select number::int i FROM numbers_mt(1,1000000000)t into outfile '/data/t.parquet';  --6GB		69.345
SELECT avg(i) FROM file('/data/t.parquet') group by round(log10(i));		50.262
convert a csv file to parquet
copy (select i::int i from 't.csv') to 't.parquet';	134.378
select number::int i FROM numbers_mt(1,1000000000)t into outfile 't.csv';		38.509
select *::int i from file('/data/t.csv') into outfile 't.parquet';		105.857

[l1t1]

SET preserve_insertion_order=false;	duckdb parallel write parquet
copy (select i::int i from t) to '/data/tgz2.parquet' (codec 'gzip');	104.411
copy (select i::int i from t) to '/data/tz2.parquet' (codec 'ZSTD');	24.488
copy (select i::int i from t) to '/data/tpz2.parquet' (codec 'snappy');	39.532
copy (select i::int i from t) to '/data/tu2.parquet' (codec 'uncompressed');	35.526