Online index tuning system with uncertainty perception

Abstract

The invention belongs to the technical field of database indexing, and particularly relates to an online index tuning system with uncertainty perception. The system comprises an operator-level learning model module which is used for dynamically correcting the estimation cost of a query optimizer by establishing lightweight learning models for operators in a query execution plan and learning cost adjustment factors of various operators by utilizing limited query execution feedback data; the model uncertainty quantification module is used for evaluating the prediction credibility of each operator model based on the joint measurement of the information entropy and Monte Carlo Dropout; in the index recommendation stage, an uncertainty weighted index value function is introduced, the instant performance income and the long-term learning value of candidate indexes are comprehensively evaluated, and self-adaptive balance of exploration and utilization is achieved. According to the method, index configuration can be dynamically adjusted under the continuously-changing query workload, high stability and self-learning ability are achieved, and the query performance is continuously optimized.

Description

Technical Field

[0001] This invention belongs to the field of database indexing technology, specifically relating to an online automatic index tuning system with uncertainty awareness. Background Technology

[0002] Indexes are fundamental data structures in database systems. They accelerate data retrieval by reducing random I / O to disk and storage layers and improving cache hit rates. Their quality directly impacts system response latency and throughput. Traditionally, index design and tuning rely primarily on manual configuration and iteration by database administrators (DBAs) based on experience. However, with the exponential growth of data volume and the rapid evolution of workloads, the challenges of manual tuning have become increasingly severe. Therefore, automated index recommendation has gradually become mainstream. Its goal is to provide near-optimal index configuration solutions without manual intervention, given available resources, workload, and database system configuration.

[0003] Finding the optimal index configuration within a given budget has been proven to be an NP-hard problem. Therefore, early methods often employed heuristic or decompositional strategies, enumerating candidate indexes, calling the optimizer's "What-if" interface to obtain cost estimates for specific configurations, and then using greedy algorithms or local search to obtain a set of acceptable configurations. These methods are simple to implement and highly interpretable, but are often limited by expensive and frequent "What-if" calls, and suboptimal solutions caused by interactions between indexes. In recent years, machine learning has been introduced into index recommendation to alleviate these bottlenecks. Generally, they can be divided into two categories: one uses strategies such as reinforcement learning / multi-armed gamblers / black-box Bayesian optimization to interact with the environment (query optimizer or real execution feedback) to explore better index combinations, focusing on reducing the number of "What-if" queries and approximating high-quality configurations in less time; the other uses neural networks to learn more accurate cost or benefit estimates, thereby building a fast evaluator offline to replace expensive "What-if" calls.

[0004] In real-world scenarios, data often changes dynamically, and models trained offline are prone to accuracy degradation and reduced exploration efficiency when data distribution, query templates, and other factors change. Despite some exploration of existing indexing and recommendation methods to address these complex execution scenarios, they still fall short of meeting the requirements. Summary of the Invention

[0005] The purpose of this invention is to propose an uncertainty-aware online index optimization system suitable for massive online data scenarios, in order to address the index optimization challenges in dynamically changing scenarios.

[0006] The uncertainty-aware online index optimization system proposed in this invention, applicable to massive online data scenarios, has the following overall architecture:Figure 1 As shown, through a closed-loop process of "candidate index generation—query cost estimation—uncertainty correction—index value evaluation—index selection—execution feedback update," adaptive index tuning is achieved under continuously changing query workloads. Specifically, it includes two core components: an operator-level learning model module and a model uncertainty quantification module, which together constitute... Figure 1 The central data pathway consists of "query plan - operator-level CAM prediction - uncertainty cost correction - index benefit estimation";

[0007] The system receives query requests from old workloads, current workloads, and future workloads, and continuously optimizes index configurations without affecting the normal operation of the database.

[0008] At the start of the tuning process, a candidate index set is first generated based on the old workload and the current workload. For queries within the current workload, the query and candidate index configurations are submitted to the database's built-in What-if optimizer to obtain the corresponding query execution plan and estimated cost. Based on this, an uncertainty-aware index benefit estimation process is initiated, where:

[0009] In the operator-level learning model module, "operator-level" indicates the modeling granularity, meaning that learning and prediction are performed using a single operator instance in the execution plan as the smallest unit, rather than modeling the operation type itself. A single operator in the execution plan (such as index scan, join, filtering, etc.) will form multiple specific operator instances under different query contexts. The module establishes corresponding learning models for these operator instances to predict their cost adjustment multiplier (CAM). This module uses the what-if cost estimation results generated by the query optimizer and combines them with the contextual features of the operator instance (including operator type, input column information, predicate conditions, join input features, etc.) to dynamically correct the estimated cost of the operator. The corrected operator cost is then propagated from bottom to top to generate the total query plan cost after uncertainty adjustment. This process... Figure 1 This corresponds to a continuous processing chain from "operator-level CAM prediction" and "uncertainty cost correction" to "index benefit estimation". By modeling at the operator instance level, compared with learning methods based on the entire query plan, the operator-level learning can more accurately capture local cost errors when execution feedback data is limited, thereby improving the generalization ability on unseen queries.

[0010] The model uncertainty quantification module, based on a joint measurement mechanism of information entropy and Monte Carlo Dropout, performs quantification on each operator model. The prediction confidence level is evaluated accordingly. Figure 1 The "Uncertainty" label next to the CAM predictor output indicates that when the uncertainty of an operator instance exceeds a set threshold, the system will retain its original What-if optimizer cost instead of using CAM correction, thus avoiding performance degradation due to insufficient modeling during cold starts or high-noise phases. Each operator... The formula for calculating uncertainty is:

[0011] ;

[0012] in, This represents the prediction variance obtained through multiple Dropout inferences. To predict the entropy of the distribution, For weighting coefficients. The system only uses the operator model prediction results to correct the optimizer's cost estimation when the uncertainty is below a set threshold, thereby avoiding performance degradation caused by unreliable corrections during cold starts or high-noise phases.

[0013] After obtaining the index gains adjusted for uncertainty, the system enters... Figure 1 The upper part shows the uncertainty-weighted index selection module. This uncertainty-weighted index selection introduces an uncertainty-weighted index value function during the index recommendation stage. It comprehensively evaluates the immediate performance gains and long-term learning value of candidate indexes, employing an improved traditional ε-greedy strategy to achieve an adaptive balance between exploration and utilization. This reduces the creation and deletion of invalid indexes, lowers tuning overhead, and accelerates system convergence. This invention can dynamically adjust index configuration under continuously changing query workloads, possessing strong stability and self-learning capabilities, and can continuously optimize query performance while ensuring stable system operation.

[0014] Specifically, in the index recommendation process, for each candidate index Define the uncertainty-weighted comprehensive value function:

[0015] ;

[0016] in, Indicates index For workload The immediate performance gains This represents the uncertainty-weighted exploration value of the operator set corresponding to the index. To explore the weighting coefficients. The index exploration value is defined as:

[0017] ;

[0018] in, Indicates indexed The set of operators that have an impact.

[0019] During the index selection process, the system employs an improved method. -Greedy strategy: compared with traditional The -greedy strategy differs from the equal-probability selection of non-greedy actions; the improved strategy is based on a comprehensive value function. The size assigns an exploration probability to each candidate index, i.e., according to its normalized value. Weights are selected accordingly. Therefore, on the one hand, indexes with high immediate benefits are prioritized; on the other hand, indexes more valuable to model learning are actively explored based on model uncertainty, thus achieving an adaptive balance between query performance gains and model learning efficiency. After recommending index configurations, the system sends them to the database for execution and collects real feedback data from actual query executions. Figure 1 The path on the right is "Execute Query - Execute Telemetry (Real Feedback)". This feedback data will be fed back to the operator-level learning model module and its uncertainty quantification module to update the CAM predictor, thus achieving... Figure 1 The online self-learning process shown at the bottom, "Update CAM Predictor," enables continuous learning and dynamic optimization.

[0020] The operator-level learning model predicts the discretized cost adjustment factor set through a multilayer perceptron (MLP) classification network. , It includes multiple sets of factor values ​​covering both underestimation and overestimation ranges, used to correct optimizer biases of varying degrees.

[0021] The weighting coefficients in the model uncertainty quantification are adaptively adjusted according to the system noise level. When the query execution feedback variance is large, the Monte Carlo Dropout weight is reduced and the proportion of the entropy term in the overall uncertainty assessment is increased.

[0022] In uncertainty-weighted index selection, when calculating the exploration value of an index, only the sum of uncertainties of the set of operators containing that index are considered, in order to focus on index regions with low model confidence, improve exploration efficiency and reduce redundant searches.

[0023] When the system detects workload drift, it dynamically adjusts the exploration weights in the index selection module based on the proportion of unseen queries. The exponential decay mechanism enables rapid adaptation, thereby maintaining tuning stability when query patterns change.

[0024] ;

[0025] in, This is the decay rate constant, ensuring that the system enhances exploratory behavior when new queries occur frequently and reduces exploratory behavior during the stable phase, thus enabling rapid adaptation to changes in query patterns.

[0026] This invention can be embedded in a database management system as an intelligent index tuning kernel, and is suitable for high concurrency, data drift and multi-tenant scenarios. It can optimize index configuration in real time without affecting system availability, thereby improving query performance and resource utilization. Attached Figure Description

[0027] Figure 1 This is a diagram of the uncertainty-aware online automatic indexing and optimization system architecture of the present invention.

[0028] Figure 2 An example of updating with an uncertain cost. Detailed Implementation

[0029] The present invention will be further described below with reference to the embodiments and accompanying drawings.

[0030] This invention can be deployed on relational database management systems to automatically generate and adjust database index configurations in scenarios where real-time query workloads continuously change, thereby reducing query execution time and minimizing index redundancy overhead. This embodiment uses PostgreSQL as an example database system, but is not limited to any specific database platform.

[0031] (I) Experimental Setup:

[0032] Database system environment:

[0033] The database system can be centralized or distributed, supporting SQL query parsing, optimization, and execution. The tuning process is implemented externally to the database using "online auxiliary methods," without modifying the database source code, and interacts with the database only through the following interfaces:

[0034] Database query optimizer cost estimation interface;

[0035] Index creation and deletion commands;

[0036] Query execution feedback interface;

[0037] This invention, as an independent tuning and execution process, periodically calls the aforementioned interface during system operation without affecting the stability of the core database execution process.

[0038] (ii) Workload:

[0039] This example uses the IMDB dataset, the dataset from the "Internet Movie Database" website, which contains information on millions of movies, TV shows, and TV movies, as well as related profiles, reviews, ratings, and awards of actors, directors, producers, and other staff. This dataset contains multiple tables, primarily the following seven:

[0040] title.basics: A table containing basic information about movies, TV shows, and TV movies, such as movie title, genre, release date, country / region of origin, etc.

[0041] title.ratings contains user rating data for movies, TV shows, and TV movies on the IMDb website, such as average rating and number of ratings.

[0042] title.crew contains information about the director and screenwriter of each film, as well as other production staff.

[0043] name.basics: Contains personal information of staff such as actors, directors, and producers, including their dates of birth and occupations.

[0044] title.principals: Contains information about the roles and positions of the actors and other staff in each film.

[0045] title.episode: Contains information on the number of episodes and seasons of TV shows and TV movies.

[0046] title.akas: Contains information such as the film's alternative title, translated title, and international standard film code.

[0047] This dataset has the following characteristics

[0048] The queries are mostly join-intensive.

[0049] The filtering predicates are complex and skewed in distribution.

[0050] Queries are highly sensitive to indexes.

[0051] (III) Implementation process:

[0052] Embedded Workflow

[0053] This invention does not directly replace or modify the optimizer, but rather superimposes it on top of the database optimization process in the form of a "decision layer + correction layer," with the logical intervention point being:

[0054] After the optimizer completes query rewriting and generates a preliminary execution plan;

[0055] Before the database execution plan triggers physical execution;

[0056] What-if: Before the cost estimate has been used or updated.

[0057] The present invention performs the following actions in sequence:

[0058] (1) Obtain the batch of queries to be executed (mini-workload) and generate candidate indexes;

[0059] In online scenarios, the system no longer assumes the query load is a static, complete set. Instead, it groups incoming queries into workloads in batches according to time windows or quantity thresholds. Common triggering methods include, but are not limited to, time-triggered (e.g., forming a batch every 30 seconds or 1 minute) and quantity-triggered (triggered when a batch accumulates to N queries). This invention adopts a quantity-triggered approach, using batched workloads as the basic decision-making unit, thereby better addressing workload drift.

[0060] In online index tuning scenarios, candidate indexes cannot be pre-set; instead, they must be dynamically extracted based on the structural information of queries in the workload. The system uses a predicate-scan-based index trigger point extraction method to perform syntactic and logical parsing on each query in the workload, identifying predicates that can improve selectivity. If a column appears in high-frequency filtering or join predicates, it is identified as a potential index key column.

[0061] (2) Use the query optimizer to estimate the basic cost;

[0062] This invention first uses the what-if interface provided by the query optimizer to generate the corresponding relational cost model output, including: the execution plan under the candidate index configuration, the original cost estimate of each operator, and the operator type, data source relation and predicate parameters in the plan tree.

[0063] (3) Correct the leaf operators in the basic cost estimation tree based on the learning correction coefficient;

[0064] The operator costs output by the query optimizer are not directly used as the final reference value. In this embodiment, a "correction trigger check" is first performed on each leaf operator: if there is no online feedback, it is determined that the system is in a cold start and no correction is performed; if there is a small amount of feedback, the current learning correction coefficients are unstable, and correction is performed but with weights; if the feedback is sufficient, the uncertainty is low, and the model has high acceptance, the basic cost estimate is completely replaced and applied. This mechanism ensures that the correction is a gradual trust-based approach rather than a one-time replacement, thus naturally adapting to online learning scenarios.

[0065] (4) Calculate the potential performance gains based on the correction results;

[0066] The leaf operator correction results are recursively pushed to the root node of the cost estimation tree to obtain the cost estimation correction results of the candidate index for the query. Compared with the case without an index, the potential performance benefits of the candidate index are obtained.

[0067] (5) Under the guidance of uncertainty, attempt to deploy indexes and observe actual execution feedback;

[0068] Select indexes with high potential performance gains (significant deployment benefits) and high model uncertainty (great exploratory value) for deployment, and collect real execution feedback data. Update the model with the real execution feedback data.

[0069] (6) Continuously adjust the index configuration until convergence;

[0070] Based on the updated model, the index configuration is continuously adjusted until the system reaches stability.

[0071] (iv) Effect demonstration:

[0072] Figure 2 illustrates a specific embodiment of the "cost update mechanism based on uncertainty constraints" in the index revenue estimation process of this invention. This embodiment reflects how the system dynamically corrects the optimizer cost using an operator-level learning model and an uncertainty quantification module after obtaining the query's what-if execution plan.

[0073] Specifically, for a query q to be optimized, the system first calls the database's what-if interface to obtain the execution plan under its candidate index configuration, as shown on the left side of Figure 2. The root node of the plan is the aggregation operator, under which are nested loop joins. The left and right subtrees of the join correspond to the sequential scan operator and the index scan operator, respectively. The optimizer estimates the cost of the sequential scan at 45962 and the cost of the index scan at 1.35. Because the optimizer considers the index scan cost to be extremely low, it carries a larger weight in the final plan.

[0074] Subsequently, the system inputs the leaf operators from the above plan into the operator-level CAM predictor, as shown in the middle of Figure 2. The model outputs the cost adjustment factor (CAM) and prediction uncertainty based on the operator characteristics. The CAM for the index scan operator is ×2, with an uncertainty of 0.06 (low); the CAM for the sequential scan operator is ×0.4, with an uncertainty of 0.53 (higher). According to the rules of the uncertainty quantification module, only operators with uncertainties below the threshold will trigger cost correction. Therefore, in this embodiment, only the cost update is performed on the index scan operator.

[0075] During the cost correction phase, as shown on the right side of Figure 2, the system adjusts the original cost of the index scan from 1.35 to 2.7 by multiplying CAM by 2. The sequential scan operator retains its original estimate due to high uncertainty. The updated cost is then re-aggregated from the bottom up to the join and aggregation operators, forming a query cost estimate that better reflects actual execution conditions. This cost will serve as the input to the index value calculation module of this invention, influencing the final selection of the index.

[0076] As can be seen from this embodiment, the present invention can make targeted corrections when the optimizer makes systematic errors in the estimation of some operators (such as the index scan being significantly underestimated), thereby improving the quality of index benefit estimation, reducing unnecessary index creation and deletion operations based on erroneous cost estimation, and providing a more reliable decision basis for the subsequent index selection process.

Claims

1. An uncertainty-aware online index optimization system for massive online data scenarios, characterized in that, The overall architecture achieves adaptive index tuning under continuously changing query workloads through a closed-loop process of "candidate index generation - query cost estimation - uncertainty correction - index value evaluation - index selection - execution feedback update". Specifically, it includes two core components: an operator-level learning model module and a model uncertainty quantification module. Together, they form a data path of "query plan - operator-level CAM prediction - uncertainty cost correction - index benefit estimation". The system receives query requests from old workloads, current workloads, and gradually arriving future workloads, and continuously optimizes index configurations without affecting the normal operation of the database; At the start of the tuning process, a candidate index set is first generated based on the old workload and the current workload. For queries within the current workload, the query and candidate index configurations are submitted to the database's built-in What-if optimizer to obtain the corresponding query execution plan and estimated cost. Based on this, an uncertainty-aware index benefit estimation process is initiated, where: In the operator-level learning model module, "operator-level" indicates the modeling granularity, meaning that learning and prediction are performed using a single operator instance in the execution plan as the smallest unit. A single operator in the execution plan forms multiple specific operator instances under different query contexts. This module establishes corresponding learning models for these operator instances to predict their cost adjustment factors (CAM). This module uses the what-if cost estimation results generated by the query optimizer and combines them with the contextual features of the operator instances, including operator type, input column information, predicate conditions, and connection input features, to dynamically correct the estimated cost of the operators. The corrected operator cost is then propagated from bottom to top to generate the total cost of the query plan after uncertainty control. By modeling at the operator instance level, the operator-level learning can more accurately capture local cost errors when execution feedback data is limited, thereby improving the generalization ability on unseen queries. The model uncertainty quantification module, based on a joint measurement mechanism of information entropy and Monte Carlo Dropout, performs quantification on each operator model. The prediction confidence is evaluated; when the uncertainty of an operator instance exceeds a set threshold, its original What-if optimizer cost is retained; each operator The formula for calculating uncertainty is: ； in, This represents the prediction variance obtained through multiple Dropout inferences. To predict the entropy of the distribution, The weighting coefficients are used; the system only uses the operator model prediction results to correct the optimizer's cost estimation when the uncertainty is below a set threshold.

2. The uncertainty-aware online index optimization system according to claim 1, characterized in that, After obtaining the index benefits corrected for uncertainty, uncertainty-weighted index selection is performed. The uncertainty-weighted index selection introduces an uncertainty-weighted index value function in the index recommendation stage to comprehensively evaluate the immediate performance benefits and long-term learning value of candidate indexes. It adopts an improved traditional ε-greedy strategy to achieve an adaptive balance between exploration and utilization, thereby reducing the creation and deletion of invalid indexes. Specifically, in the index recommendation process, for each candidate index Define the uncertainty-weighted comprehensive value function: ； in, Indicates index For workload The immediate performance gains This represents the uncertainty-weighted exploration value of the operator set corresponding to the index. To explore weighting coefficients; the index exploration value is defined as: ； in, Indicates indexed The set of operators that have an impact.

3. The uncertainty-aware online index optimization system according to claim 2, characterized in that, After the recommended index configuration is configured, it is sent to the database for execution, and real feedback data is collected from the actual executed queries. This feedback data is fed back to the operator-level learning model module and its uncertainty quantification module to update the CAM predictor, thereby achieving continuous learning and dynamic optimization.

4. The uncertainty-aware online index optimization system according to claim 3, characterized in that, The operator-level learning model predicts the discretized cost adjustment factor set through a multilayer perceptron (MLP) classification network. , It includes multiple sets of factor values ​​covering both underestimation and overestimation ranges, used to correct optimizer biases of different degrees; The weighting coefficients in the model uncertainty quantification are adaptively adjusted according to the system noise level. When the query execution feedback variance is large, the Monte Carlo Dropout weight is reduced and the proportion of the entropy term in the overall uncertainty assessment is increased.

5. The uncertainty-aware online index optimization system according to claim 3, characterized in that, In uncertainty-weighted index selection, when calculating the exploration value of an index, only the sum of uncertainties of the set of operators containing that index is considered, so as to focus on the index region with low model confidence, improve exploration efficiency and reduce redundant search; When the system detects workload drift, it dynamically adjusts the exploration weight in index selection based on the proportion of unseen queries. The exponential decay mechanism enables rapid adaptation, thereby maintaining tuning stability when query patterns change: ； in, This is the decay rate constant, ensuring that the system enhances exploratory behavior when new queries occur frequently and reduces exploratory behavior during the stable phase, thus enabling rapid adaptation to changes in query patterns.

Images