# Dynamic Pruning Mode - Example ``` tidb> SET @@tidb_partition_prune_mode = 'dynamic'; Query OK, 0 rows affected, 2 warnings (0.00 sec) tidb> SHOW STATS_META WHERE table_name LIKE 'User'; +---------+------------+----------------+---------------------+--------------+-----------+ | Db_name | Table_name | Partition_name | Update_time | Modify_count | Row_count | +---------+------------+----------------+---------------------+--------------+-----------+ | test | User | global | 2023-10-03 15:34:10 | 7 | 7 | | test | User | p0 | 2023-10-03 15:33:33 | 0 | 3 | | test | User | p1 | 2023-10-03 15:34:10 | 1 | 4 | | test | User | p2 | 2023-10-03 15:34:10 | 1 | 2 | +---------+------------+----------------+---------------------+--------------+-----------+ 4 rows in set (0.00 sec) tidb> EXPLAIN SELECT * FROM User WHERE city IN ('Seattle', 'Tokyo') AND login BETWEEN '2023-09-01' AND '2023-11-30'; +--------------------+---------+-----------+--------------------------+---------------------------------------------------------------------... + | id | estRows | task | access object | operator info ... | +--------------------+---------+-----------+--------------------------+---------------------------------------------------------------------... | | TableReader_8 | 0.00 | root | | data:Selection_7 ... | | └─Selection_7 | 0.00 | cop[tikv] | | ge(test.user.login, 2023-09-01 00:00:00.000000), in(test.user.city, ... | | └─TableFullScan_6 | 4.00 | cop[tikv] | table:User, partition:p1 | keep order:false ... | +--------------------+---------+-----------+--------------------------+---------------------------------------------------------------------... + 3 rows in set, 1 warning (0.00 sec) ``` ---------------------------------------------------- The effectiveness of `dynamic` partitioning depends on the collection of full table-level statistics, known as `GlobalStats`. Until `GlobalStats` are gathered, TiDB defaults to using static mode. For more details on `GlobalStats`, please refer to: [Collect Statistics of Partitioned Tables in Dynamic Pruning Mode](https://docs.pingcap.com/tidb/stable/statistics#collect-statistics-of-partitioned-tables-in-dynamic-pruning-mode). In `dynamic` pruning mode, TiDB collects table-level statistics, referred to as `GlobalStats`, which are currently aggregated from the statistics of all partitions. An update to `GlobalStats` may be triggered by a statistics update to any partitioned table. When `GlobalStats` updates are triggered, the following scenarios may occur: - If some partitions lack statistics, such as a new partition that has not yet been analyzed, the generation of `GlobalStats` is halted, and a warning indicating the absence of statistics on certain partitions is displayed. - If statistics for specific columns are missing in certain partitions (e.g., different columns are specified for analysis in these partitions), the generation of `GlobalStats` will be interrupted during the aggregation of these columns, and a warning will be issued indicating the absence of column statistics in specific partitions. For `dynamic` pruning mode, the `Analyze` configurations for both partitions and tables need to be consistent. Therefore, if you specify the `COLUMNS` configuration after the `ANALYZE TABLE TableName PARTITION PartitionNameList` statement or use the `OPTIONS` configuration with `WITH`, TiDB will disregard these configurations and issue a warning. In the slide, like: ``` tidb> SHOW STATS_META WHERE table_name LIKE 'User'; +---------+------------+----------------+---------------------+--------------+-----------+ | Db_name | Table_name | Partition_name | Update_time | Modify_count | Row_count | +---------+------------+----------------+---------------------+--------------+-----------+ | test | User | global | 2023-10-03 15:34:10 | 7 | 7 | | test | User | p0 | 2023-10-03 15:33:33 | 0 | 3 | | test | User | p1 | 2023-10-03 15:34:10 | 1 | 4 | | test | User | p2 | 2023-10-03 15:34:10 | 1 | 2 | +---------+------------+----------------+---------------------+--------------+-----------+ 4 rows in set (0.00 sec) ``` The first row, where `Partition_name` is labeled as `global`, represents the `GlobalStats`. `Dynamic` partitioning becomes effective only once the full table-level statistics, or `GlobalStats`, are collected, as exemplified by: ``` tidb> EXPLAIN SELECT * FROM User WHERE city IN ('Seattle', 'Tokyo') AND login BETWEEN '2023-09-01' AND '2024-11-30'; +--------------------+----------+-----------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------+----------+-----------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------+ | TableReader_7 | 0.50 | root | partition:p1,p2 | data:Selection_6 | | └─Selection_6 | 0.50 | cop[tikv] | | ge(test.user.login, 2023-09-01 00:00:00.000000), in(test.user.city, "Seattle", "Tokyo"), le(test.user.login, 2024-11-30 00:00:00.000000) | | └─TableFullScan_5 | 10000.00 | cop[tikv] | table:User | keep order:false, stats:pseudo | +--------------------+----------+-----------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.01 sec) ```