improve the query performance on partition table

[zz-jason]

Description

We have supported the range and hash partition table since TiDB 3.0. Except for other unsupported partition features, the performance on the current implementation of the partition table still has some space to improve.

Scenarios that the performance can be improved:

• support index join for range partition table #15612 Can not be chosen as the probe side of Index Hash/Merge Join.
• between .. and on int column can be used to prune hash partition #16845 between ... and on integer column can not be used to prune hash partition
• Optimize locatePartition() function and improve the write performance on partitioned table #16667 performance of load data into the partition table is poor compared with normal table
• Can not benefit from plan cache due to known issue: Query result on partition table wrong when using prepared-pan-cache #15720
• Can not benefit from PointGet/BatchPointGet operator on a range partitioned table

See this google doc(in Chinese) for more detail.

Category

• Performance

Value

Value Description

improve the query performance on both OLTP and OLAP queries.

Value Score

Optional value: 1~5

• 3

Workload Estimation

Workload contains coding, documenting, and testing.
1 Point for 1 Person/Work Day

• 90 Points

[zz-jason]

#18030 is going to support geo-partition for partition tables. IMO, we'd better higher the priority of this issue for these reasons:

• Users may load data into a partition in a region, so the performance of load data into the partition table should be improved Optimize locatePartition() function and improve the write performance on partitioned table #16667
• Users may query the partition data in a region, so it's better to support Index Hash/Merge Join for partitioned table support index join for range partition table #15612 and utilize the PointGet operator and plan cache for prepare statements.
• To avoid insufficient partition pruning which causes retrieving data from a remote region, it's better to take between .. and and like into consideration.

---

Updated in 2020-07-10

From #18016, partition pruning and data access performance are P0 tasks.

[zz-jason]

close since all the issue except for #16845 listed in the doc are fixed.