TiDB supports plan cache for the partitioned tables

[choudharypankaj]

File: /release-8.1/partitioned-table.md

This statement needs to be removed.:
Currently, static pruning mode does not support plan cache for both prepared and non-prepared statements.

From the DMR 8.0 release:
Remove some limitations on execution plan cache #49161 @mjonss @qw4990

TiDB supports plan cache, which can effectively reduce the latency of OLTP systems and is important for performance.

[qiancai]

Hi @choudharypankaj, thanks for the feedback!

According to the doc update PR https://github.com/pingcap/docs/pull/16359/files of this feature, before v8.0.0, neither static nor dynamic pruning mode supports prepared statements plan cache; after v8.0.0, static pruning mode does not support plan cache for both prepared and non-prepared statements.

@mjonss, please help confirm. Thanks.

[mjonss]

@qiancai, I confirm that static prune mode does not support plan cache for neither prepared nor non-prepared statements. That is tested here.

One of the reasons for introducing the dynamic prune mode was to allow caching plans for partitioning tables.

@choudharypankaj I do see that the statement seems to be a bit out of context, maybe we should move it further up under the Dynamic pruning mode section?

[choudharypankaj]

@mjonss Thanks for the update.
I've a quick question:
As per the doc, the dynamic pruning only takes effect when the stats are gather on the partitioned table.
What happens during a scenario where the user is adding and removing partitions hourly on the table, Is it still going to work since the stats are not gathered frequently on the table?

https://docs.pingcap.com/tidb/stable/system-variables#tidb_partition_prune_mode-new-in-v51

[mjonss]

@mjonss Thanks for the update. I've a quick question: As per the doc, the dynamic pruning only takes effect when the stats are gather on the partitioned table. What happens during a scenario where the user is adding and removing partitions hourly on the table, Is it still going to work since the stats are not gathered frequently on the table?

https://docs.pingcap.com/tidb/stable/system-variables#tidb_partition_prune_mode-new-in-v51

@choudharypankaj IIRC, once there are Global statistics on a partitioned table, it can use dynamic prune mode, even after add/drop/reorganize partitions, just that the statistics may be outdated in case of DROP PARTITION.