Convert_tz function needs to improve execution efficiency
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
create table test1(id bigint(20),
dt datetime NOT NULL COMMENT '2022071110');
insert into test1 values(2022071110,'2022-07-11 10:00:00');insert into test select * from test;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
insert into test1 select * from test1;
explain analyze select * from test1;
explain analyze SELECT id,CONVERT_TZ(dt,'+08:00','US/Eastern') as date from test1;
MySQL [test]> explain analyze select * from test1;
+-----------------------+-----------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------+-----------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
| TableReader_5 | 524288.00 | 524288 | root | | time:30.3ms, loops:516, cop_task: {num: 62, max: 762.2µs, min: 245.8µs, avg: 389.3µs, p95: 526.3µs, tot_proc: 1ms, tot_wait: 7ms, rpc_num: 62, rpc_time: 23.4ms, copr_cache_hit_ratio: 1.00, distsql_concurrency: 15} | data:TableFullScan_4 | 287.8 KB | N/A |
| └─TableFullScan_4 | 524288.00 | 524288 | cop[tikv] | table:test1 | tikv_task:{proc max:22ms, min:4ms, avg: 12.3ms, p80:15ms, p95:21ms, iters:761, tasks:62}, scan_detail: {get_snapshot_time: 962.6µs, rocksdb: {block: {}}} | keep order:false | N/A | N/A |
+-----------------------+-----------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------+------+
2 rows in set (0.03 sec)
MySQL [test]> explain analyze SELECT id,CONVERT_TZ(dt,'+08:00','US/Eastern') as date from test1;
+-------------------------+-----------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------+-----------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+----------+------+
| Projection_3 | 524288.00 | 524288 | root | | time:2.47s, loops:516, Concurrency:5 | test.test1.id, convert_tz(test.test1.dt, +08:00, US/Eastern)->Column#4 | 168.6 KB | N/A |
| └─TableReader_5 | 524288.00 | 524288 | root | | time:12.6ms, loops:516, cop_task: {num: 62, max: 2.36ms, min: 459.1µs, avg: 858µs, p95: 1.28ms, tot_proc: 1ms, tot_wait: 6ms, rpc_num: 62, rpc_time: 50.8ms, copr_cache_hit_ratio: 1.00, distsql_concurrency: 15} | data:TableFullScan_4 | 287.8 KB | N/A |
| └─TableFullScan_4 | 524288.00 | 524288 | cop[tikv] | table:test1 | tikv_task:{proc max:22ms, min:4ms, avg: 12.3ms, p80:15ms, p95:21ms, iters:761, tasks:62}, scan_detail: {get_snapshot_time: 1.96ms, rocksdb: {block: {}}} | keep order:false | N/A | N/A |
+-------------------------+-----------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+----------+------+
3 rows in set (2.47 sec)
Contributor guide
Assessment
This issue has not been assessed yet.