In database optimization, statistics are typically collected before the query plan is generated. This means that if a significant number of insert, update, or delete operations occur after statistics are collected but the statistics are not updated in time, outdated information may be used during plan generation. This can lead to inaccurate row count estimates and result in suboptimal execution plans. To address this issue, the OceanBase Database optimizer introduces a real-time storage-layer row estimation mechanism, which provides more up-to-date and accurate statistics.
Storage-layer row estimation mechanism
When generating a query plan, the optimizer creates multiple base table scan paths, referred to as "base table paths," based on the table's indexes. OceanBase Database stores these indexes in a tree structure. During plan generation, for each base table path, the optimizer generates multiple query range regions (referred to as "QUERY RANGES") based on the table's predicate conditions. This design enables efficient scanning of specific data regions while avoiding unnecessary data scans.
To improve accuracy, the optimizer sends the relevant QUERY RANGES for each base table path to the storage layer in advance. The storage layer then quickly estimates the total number of rows for these QUERY RANGES and returns the results to the optimizer. This process allows the optimizer to obtain real-time and accurate row count estimates, which is known as storage-layer row estimation.
The storage layer collects real-time statistics and uses a hidden configuration option, _enable_filter_reordering, to control whether the filter reordering feature is enabled. By default, this feature is enabled to enhance query performance.
Limitations of row estimation
However, not all QUERY RANGES and partitions are used for storage-layer row estimation. Since row estimation at the storage layer can be time-consuming, the following limitations are in place to maintain the overall performance of plan generation:
QUERY RANGE limitation: The number of QUERY RANGES on a base table path cannot exceed the limit set by the system variable
range_index_dive_limit. If the number exceeds this limit, the system randomly selects a subset of QUERY RANGES up to the specified limit for storage-layer row estimation. If the number of QUERY RANGES is below this limit, all QUERY RANGES are used for estimation. If this variable is set to 0, the system always selects all QUERY RANGES without restrictions.Partition limitation: For partitioned tables, if the number of pruned partitions exceeds the limit set by the system variable
partition_index_dive_limit, the system randomly selects a subset of partitions up to the specified limit for storage-layer row estimation. If the number of partitions is below this limit, all partitions are used for estimation. If this variable is set to 0, the system always selects all partitions.
It’s important to note that storage-layer row estimation applies only to base table paths and does not extend to other operators such as JOIN or GROUP BY.
In previous versions, multi-column IN predicates were treated as imprecise predicates. However, in the current version, storage-layer row estimation has been enhanced to handle multi-column IN predicates more effectively. Even when multiple QUERY RANGES exist, accurate row count estimates can now be performed, significantly improving query plan optimization.