databend visit file slower than duckdb

[l1t1]

SELECT avg(i) FROM range(1,1000000000)t(i) group by i%5;


~ $ duckdb
v0.7.1 b00b93f
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D .timer on
D create table t as SELECT i FROM range(1,100000000)t(i);
Run Time (s): real 0.978 user 1.610239 sys 0.324603
D SELECT avg(i) FROM t group by i%5;
┌────────────┐
│   avg(i)   │
│   double   │
├────────────┤
│ 49999998.5 │
│ 49999999.5 │
│ 50000000.5 │
│ 50000001.5 │
│ 50000000.0 │
└────────────┘
Run Time (s): real 0.432 user 3.070135 sys 0.005836
D .exit
~ $ ./da11
HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
date: Mon, 27 Mar 2023 07:50:37 GMT

HTTP/1.1 200 OK
content-type: application/json; charset=utf-8
date: Mon, 27 Mar 2023 07:50:39 GMT

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26-v1.0.26-nightly-d9b7f4a8080b54d2b4c4a515296ee7557fc135f1(rust-1.70.0-nightly-2023-03-21T04:43:48.225138595Z) 0

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> create table t as SELECT number FROM numbers(100000000) ;
Query OK, 100000000 rows affected (14.596 sec)

MySQL [(none)]> SELECT avg(number) FROM t group by number%5;
+-------------+
| avg(number) |
+-------------+
|  49999997.5 |
|  49999998.5 |
|  49999999.5 |
|  50000000.5 |
|  50000001.5 |
+-------------+
5 rows in set (0.840 sec)

MySQL [(none)]> drop table t;
Query OK, 0 rows affected (0.165 sec)

MySQL [(none)]> create table t as SELECT number FROM numbers(200000000) ;
Query OK, 200000000 rows affected (3 min 22.825 sec)

MySQL [(none)]> SELECT avg(number) FROM t group by number%5;
+-------------+
| avg(number) |
+-------------+
|  99999997.5 |
|  99999998.5 |
|  99999999.5 |
| 100000000.5 |
| 100000001.5 |
+-------------+
5 rows in set (11.456 sec)

MySQL [(none)]> exit
Bye
~ $ duckdb db
v0.7.1 b00b93f
Enter ".help" for usage hints.
D .timer on
D create table t as SELECT i FROM range(1,100000000)t(i);
100% ▕████████████████████████████████████████████████████████████▏
Run Time (s): real 2.864 user 5.291007 sys 0.392873
D SELECT avg(i) FROM t group by i%5;
┌────────────┐
│   avg(i)   │
│   double   │
├────────────┤
│ 49999998.5 │
│ 49999999.5 │
│ 50000000.5 │
│ 50000001.5 │
│ 50000000.0 │
└────────────┘
Run Time (s): real 0.438 user 3.282881 sys 0.011722
D drop table t;
Run Time (s): real 0.026 user 0.007801 sys 0.014024
D create table t as SELECT i FROM range(1,200000000)t(i);
100% ▕████████████████████████████████████████████████████████████▏
Run Time (s): real 5.788 user 10.538402 sys 0.978863
D SELECT avg(i) FROM t group by i%5;
┌─────────────┐
│   avg(i)    │
│   double    │
├─────────────┤
│  99999998.5 │
│  99999999.5 │
│ 100000000.5 │
│ 100000001.5 │
│ 100000000.0 │
└─────────────┘
Run Time (s): real 0.897 user 6.788605 sys 0.010458
D drop table t;
Run Time (s): real 0.019 user 0.017629 sys 0.000000
D create table t as SELECT i FROM range(1,400000000)t(i);
100% ▕████████████████████████████████████████████████████████████▏
Run Time (s): real 11.377 user 21.065964 sys 1.603532
D SELECT avg(i) FROM t group by i%5;
┌─────────────┐
│   avg(i)    │
│   double    │
├─────────────┤
│ 199999998.5 │
│ 199999999.5 │
│ 200000000.5 │
│ 200000001.5 │
│ 200000000.0 │
└─────────────┘
Run Time (s): real 1.914 user 14.453282 sys 0.037465
D drop table t;
Run Time (s): real 0.050 user 0.036285 sys 0.012161
D .exit

[l1t1]

table_engine_memory_enabled = true
default_storage_format = 'parquet'
default_compression = 'zstd'

[l1t1]

~ $ cd da
~/da $ cd ./.databend/stateless_test_data
~/da/.databend/stateless_test_data $ ls -l
total 4
drwx------ 8 u0_a200 u0_a200 3488 Mar 27 16:08 1
~/da/.databend/stateless_test_data $ cd 1
~/.../stateless_test_data/1 $ ls -l
total 21
drwx------ 6 u0_a200 u0_a200 3488 Mar 13 14:00 250
drwx------ 6 u0_a200 u0_a200 3488 Mar 13 14:03 269
drwx------ 5 u0_a200 u0_a200 3488 Mar  9 21:46 37
drwx------ 6 u0_a200 u0_a200 3488 Mar 19 20:24 508
drwx------ 6 u0_a200 u0_a200 3488 Mar 27 15:52 590
drwx------ 6 u0_a200 u0_a200 3488 Mar 27 16:12 620
~/.../stateless_test_data/1 $ cd 620
~/.../1/620 $ ls -l
total 71
drwx------ 2 u0_a200 u0_a200 45056 Mar 27 16:12 _b
drwx------ 2 u0_a200 u0_a200 45056 Mar 27 16:12 _i_b_v2
drwx------ 2 u0_a200 u0_a200  3488 Mar 27 16:12 _sg
drwx------ 2 u0_a200 u0_a200  3488 Mar 27 16:12 _ss
-rw------- 1 u0_a200 u0_a200   115 Mar 27 16:12 last_snapshot_location_hint
~/.../1/620 $ cd *b
~/.../620/_b $ ls -l
total 199236
-rw------- 1 u0_a200 u0_a200 1016169 Mar 27 16:11 030c2c4ca3254760b045634e40171fb0_v2.parquet
-rw------- 1 u0_a200 u0_a200 1016164 Mar 27 16:10 0495dd3194da4f83ab916a1843608305_v2.parquet
-rw------- 1 u0_a200 u0_a200 1016180 Mar 27 16:09 05a27c339ef2426b84d75bd93011c0d9_v2.parquet
-rw------- 1 u0_a200 u0_a200 1016182 Mar 27 16:10 07630ec8942a4eb89788c7eae4dc29f7_v2.parquet
-rw------- 1 u0_a200 u0_a200 1016165 Mar 27 16:11 07c5b71c59874cb7b5e02b9cf4b06aec_v2.parquet
-rw------- 1 u0_a200 u0_a200 1016164 Mar 27 16:09 07f19e101cb54e288e2fc584c55ed68d_v2.parquet

[l1t1]

MySQL [(none)]> create table t2 (i bigint) storage_format = 'native';
Query OK, 0 rows affected (0.047 sec)


MySQL [(none)]> insert into t2 SELECT number FROM numbers(200000000);
Query OK, 200000000 rows affected (30.621 sec)


MySQL [(none)]> SELECT avg(i) FROM t2 group by i%5;
+-------------+
| avg(i)      |
+-------------+
|  99999997.5 |
|  99999998.5 |
|  99999999.5 |
| 100000000.5 |
| 100000001.5 |
+-------------+
5 rows in set (3.126 sec)

[sundy-li]

Please test with lz4 compression create table t2 (i bigint) storage_format = 'native' compression = 'lz4' ;

[l1t1]

MySQL [(none)]>  create table t2 (i bigint) storage_format = 'native' compression = 'lz4' ;
Query OK, 0 rows affected (0.041 sec)

MySQL [(none)]> insert into t2 SELECT number FROM numbers(200000000);
Query OK, 200000000 rows affected (30.020 sec)

MySQL [(none)]> SELECT avg(i) FROM t2 group by i%5;
+-------------+
| avg(i)      |
+-------------+
|  99999997.5 |
|  99999998.5 |
|  99999999.5 |
| 100000000.5 |
| 100000001.5 |
+-------------+
5 rows in set (1.050 sec)

[sundy-li]

MySQL [(none)]> create table t2 storage_format = 'native' compression = 'lz4' as SELECT number FROM numbers(200000000);
Query OK, 200000000 rows affected (14.372 sec)

[l1t1]

MySQL [(none)]> create table t2 storage_format = 'native' compression = 'lz4'  as SELECT number FROM numbers(200000000);
Query OK, 200000000 rows affected (27.947 sec)

MySQL [(none)]> SELECT avg(number) FROM t2 group by number%5;                                             
+-------------+
| avg(number) |
+-------------+
|  99999997.5 |
|  99999998.5 |
|  99999999.5 |
| 100000000.5 |
| 100000001.5 |
+-------------+
5 rows in set (1.125 sec)

[l1t1]

MySQL [(none)]> create table t3 engine=memory  as SELECT number FROM numbers(200000000);
Query OK, 200000000 rows affected (0.898 sec)

MySQL [(none)]> SELECT avg(number) FROM t3 group by number%5;
+-------------+
| avg(number) |
+-------------+
|  99999997.5 |
|  99999998.5 |
|  99999999.5 |
| 100000000.5 |
| 100000001.5 |
+-------------+
5 rows in set (0.566 sec)

[l1t1]

duckdb has temp table

~ $ duckdb duckdb.db
v0.7.1 b00b93f
Enter ".help" for usage hints.
D .timer on
D create table t as SELECT i FROM range(1,200000000)t(i);
100% ▕████████████████████████████████████████████████████████████▏
Run Time (s): real 5.911 user 10.691025 sys 1.056016
 
D create temp table temp as SELECT i FROM range(1,200000000)t(i);
100% ▕████████████████████████████████████████████████████████████▏
Run Time (s): real 2.805 user 1.886404 sys 0.830373
D SELECT avg(i) FROM t group by i%5;
┌─────────────┐
│   avg(i)    │
│   double    │
├─────────────┤
│  99999998.5 │
│  99999999.5 │
│ 100000000.5 │
│ 100000001.5 │
│ 100000000.0 │
└─────────────┘
Run Time (s): real 0.930 user 6.738657 sys 0.016429
D SELECT avg(i) FROM temp group by i%5;
┌─────────────┐
│   avg(i)    │
│   double    │
├─────────────┤
│  99999998.5 │
│  99999999.5 │
│ 100000000.5 │
│ 100000001.5 │
│ 100000000.0 │
└─────────────┘
Run Time (s): real 0.888 user 6.600488 sys 0.045822