本文对比了presto和doris在即席查询场景下的性能对比
1. count(*) 查询数据总条数
例子: select count(*) from ods_tb_device_point_data;
presto查询结果
presto:vasyslink_yag001> select count(*) from ods_tb_device_point_data;
_col0
------------
2615538668
(1 row)
Query 20220908_023044_00006_mb379, FINISHED, 3 nodes
Splits: 9,060 total, 9,060 done (100.00%)
12:47 [2.62B rows, 467GB] [3.41M rows/s, 623MB/s]
doris查询结果
MySQL [vasyslink_yag001]> select count(*) from ods_tb_device_point_data;
+------------+
| count(*) |
+------------+
| 2615538668 |
+------------+
1 row in set (1 min 33.84 sec)
2. 根据时间做范围查询
select count(distinct device_id) from ods_tb_device_point_data where acq_time_by_day > '20220612';
使用presto
presto:vasyslink_yag001> select count(distinct device_id) from ods_tb_device_point_data where acq_time_by_day > '2022-06-12';
_col0
-------
5220
(1 row)
Query 20220908_075740_00015_mb379, FINISHED, 3 nodes
Splits: 726 total, 726 done (100.00%)
0:26 [153M rows, 28GB] [5.91M rows/s, 1.08GB/s]
doris查询
MySQL [vasyslink_yag001]> select count(distinct device_id) from ods_tb_device_point_data where acq_time_by_day > '20220612';
+-----------------------------+
| count(DISTINCT `device_id`) |
+-----------------------------+
| 5220 |
+-----------------------------+
1 row in set (13.22 sec)
3.做join查询
MySQL [vasyslink_yag001]> select count(*) from ods_tb_device_point_data aa join ods_tb_device_point_data bb on aa.device_id = bb.device_id where aa.acq_time_by_day >= '20220907' and bb.acq_time_by_day >= '20220907';
+------------+
| count(*) |
+------------+
| 7592514660 |
+------------+
1 row in set (1 min 10.66 sec)
presto查询
presto:vasyslink_yag001> select count(*) from ods_tb_device_point_data aa join ods_tb_device_point_data bb on aa.device_id = bb.device_id where aa.acq_time_by_day >= '2022-09-07' and bb.acq_time_by_day >= '2022-09-07';
_col0
------------
7592514660
(1 row)
Query 20220908_092647_00022_mb379, FINISHED, 3 nodes
Splits: 233 total, 233 done (100.00%)
0:13 [11M rows, 2.14GB] [881K rows/s, 175MB/s]
presto和doris的对比
presto | doris | 备注 | |
count(*) | 12:47min | 1 min 33.84 sec | 全量26.12亿条记录 |
count(DISTINCT `device_id`) | 26s | 13s | 2022-06-12以后到现在的1.5亿条记录 |
join查询 | 13s | 1 min 10.66 sec | 最近两天的点位数据与自身join(9月7号,8号) |