[Feature Request]: Optimization of Partitioned Table

[florashi181]

Is there an existing issue for the same feature request?

• [X] I have checked the existing issues.

Is your feature request related to a problem?

The current table partitioning acceleration capabilities are not fully satisfactory.
There is a lack of implementation for range and list partition pruning, which limits the potential acceleration benefits.
On the other hand, continuous performance improvements are also needed for existing hash and key partitioned tables.

Describe the feature you'd like

1. Implement partition pruning for RANGE and LIST partitions to significantly accelerate partitioned tables.

2. Enhance ALTER TABLE syntax to modify partitions with the following options:

• ADD PARTITION (partition_definition) for adding partitions.
• DROP PARTITION partition_names for dropping partitions.
• TRUNCATE PARTITION {partition_names | ALL} for truncating partitions.

3. Performance Acceleration:

• Following a detailed performance analysis of current partitioned tables and addressing any related bug issues, establish a performance baseline for each partitioned table.
• This will help set a benchmark for partition performance improvements in the 1.2 iteration.
• PM will update this issue upon confirmation of the benchmarks.

Describe implementation you've considered

No response

Documentation, Adoption, Use Case, Migration Strategy

No response

Additional information

No response

[daviszhen]

分区表1.2 baseline 1.insert 100 key分区单行插入性能缩减到普通表的2倍左右 （目前是5倍左右） 2.基于range 100分区一亿行数据的范围查询性能缩减到普通表的2倍 3.基于分区表的tpcc测试 100仓性能差距缩减到3倍（目前是6.5倍左右）

[qingxinhome]

实现分区与索引和缓存策略结合使用，进一步提高数据处理效率。通过为不同的分区创建不同的索引和缓存策略，可以优化数据的访问速度，提高查询性能。 比如访问一个分区上的一个索引要比访问一个未分区的大索引要更高效。