pingcap / pingcap/tidb

Convert_tz support to pushdown to tikv

Open
#36,399 0 comments 0 reactions 0 assignees View on GitHub
type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
create table test(id bigint(20),dt datetime );
insert into test values(2022071110,'2022-07-11 10:00:00');
insert into test values(2022071110,'2022-07-11 10:00:00');
insert into test values(2022071110,'2022-07-11 10:00:00');
insert into test values(2022071110,'2022-07-11 10:00:00');
insert into test values(2022071110,'2022-07-11 10:00:00');
MySQL [test]> explain analyze SELECT count(*),CONVERT_TZ(dt,'+08:00','US/Eastern') as date from test group by date limit 10;
+--------------------------------+----------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------+----------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+-----------+------+
| Projection_7 | 10.00 | 1 | root | | time:1.24ms, loops:2, Concurrency:OFF | Column#4, convert_tz(test.test.dt, +08:00, US/Eastern)->Column#5 | 760 Bytes | N/A |
| └─Limit_10 | 10.00 | 1 | root | | time:1.21ms, loops:2 | offset:0, count:10 | N/A | N/A |
| └─HashAgg_11 | 10.00 | 1 | root | | time:1.21ms, loops:2, partial_worker:{wall_time:1.184821ms, concurrency:5, task_num:1, tot_wait:5.614396ms, tot_exec:25.904µs, tot_time:5.652699ms, max:1.139297ms, p95:1.139297ms}, final_worker:{wall_time:1.215565ms, concurrency:5, task_num:1, tot_wait:5.776252ms, tot_exec:23.307µs, tot_time:5.802778ms, max:1.17502ms, p95:1.17502ms} | group by:Column#8, funcs:count(1)->Column#4, funcs:firstrow(Column#7)->test.test.dt | 13.6 KB | N/A |
| └─Projection_18 | 10000.00 | 5 | root | | time:1.09ms, loops:2, Concurrency:5 | test.test.dt, convert_tz(test.test.dt, +08:00, US/Eastern)->Column#8 | 5.57 KB | N/A |
| └─TableReader_15 | 10000.00 | 5 | root | | time:854.1µs, loops:2, cop_task: {num: 1, max: 869.3µs, proc_keys: 5, rpc_num: 1, rpc_time: 798.7µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | data:TableFullScan_14 | 303 Bytes | N/A |
| └─TableFullScan_14 | 10000.00 | 5 | cop[tikv] | table:test | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 5, total_process_keys_size: 255, total_keys: 6, get_snapshot_time: 41.2µs, rocksdb: {key_skipped_count: 5, block: {}}} | keep order:false, stats:pseudo | N/A | N/A |
+--------------------------------+----------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+-----------+------+
6 rows in set, 2 warnings (0.01 sec)

MySQL [test]> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | Scalar function 'convert_tz'(signature: ConvertTz, return type: datetime(6)) is not supported to push down to tikv now. |
| Warning | 1105 | Aggregation can not be pushed to tikv because groupByItems contain unsupported exprs |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.