A database upgrade plan drift attribution and statistics information correction method and system

Abstract

The application provides a database upgrade plan drift attribution and statistical information correction method and system, belonging to the technical field of databases. The method generates a pre-upgrade query plan corresponding to a target query and constructs a statistical object dependency graph before database upgrade. After database upgrade, a post-upgrade query plan corresponding to the target query is generated, and a drift plan node is determined according to the pre-upgrade query plan and the post-upgrade query plan. In the case that the drift plan node meets a preset risk condition, the statistical objects associated with the drift plan node are determined as candidate statistical objects according to the statistical object dependency graph; the contribution degree and correction cost of each candidate statistical object are determined, and a statistical object to be corrected is determined from the candidate statistical objects; statistical information correction processing is performed on the statistical object to be corrected, so as to regenerate a query plan corresponding to the target query based on the corrected statistical information, and to judge whether the plan drift converges.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a method and system for attributing database upgrade plan drift and correcting statistical information. Background Technology

[0002] Currently, database query optimizers typically estimate the execution cost of a query plan and select an execution plan based on statistical information. However, during major database version upgrades, even if the data itself remains unchanged, the optimizer code, cost model, and way statistical information is interpreted may change. This can cause the execution plan for the same query statement to drift after the upgrade, resulting in performance regressions for critical queries.

[0003] Existing solutions typically involve collecting full database statistics before and after an upgrade to regenerate the statistics. However, this approach is costly for large databases, large tables, partitioned tables, and high-concurrency scenarios, and lacks specificity. Maintaining plan stability through plan baselines, hints, or plan reuse only constrains the optimizer to use known plans, failing to address the compatibility issue between statistics and the new optimizer version. Furthermore, having database administrators compare query plans before and after the upgrade line by line is inefficient and yields unstable results when there are many queries and complex plan nodes. Summary of the Invention

[0004] To address the aforementioned issues, this application provides a method and system for attributing database upgrade plan drift and correcting statistical information. After a database upgrade, the system automatically identifies the statistical objects that cause execution plan drift and corrects them accordingly, thereby restoring the stability of the query plan at a lower cost.

[0005] In a first aspect, embodiments of this application provide a method for attributing database upgrade plan drift and correcting statistical information, the method comprising: Before the database upgrade, a pre-upgrade query plan corresponding to the target query is generated, and a corresponding statistical object dependency graph is constructed based on the statistical objects associated with each plan node in the pre-upgrade query plan; the target query is the query statement for which plan drift is to be monitored. After the database upgrade, an upgraded query plan corresponding to the target query is generated, and a drift plan node is determined based on the pre-upgrade query plan and the upgraded query plan; wherein, the drift plan node is the plan node whose execution method changes between the pre-upgrade query plan and the upgraded query plan; If the drift plan node meets the preset risk conditions, the statistical objects associated with the drift plan node are determined as candidate statistical objects according to the statistical object dependency graph. The contribution and correction cost of each candidate statistical object are determined, and the statistical object to be corrected is determined from the candidate statistical objects based on the contribution and correction cost of each candidate statistical object. Perform statistical information correction processing on the statistical object to be corrected, and regenerate the query plan corresponding to the target query based on the corrected statistical information, and determine whether the plan drift has converged.

[0006] Secondly, embodiments of this application also provide a database upgrade plan drift attribution and statistical information correction system, the system comprising: The first generation module is used to generate a pre-upgrade query plan corresponding to the target query before the database upgrade, and to construct a corresponding statistical object dependency graph based on the statistical objects associated with each plan node in the pre-upgrade query plan; the target query is the query statement of the plan drift to be monitored. The second generation module is used to generate an upgraded query plan corresponding to the target query after the database upgrade, and to determine a drift plan node based on the pre-upgrade query plan and the upgraded query plan; wherein, the drift plan node is a plan node whose execution method changes between the pre-upgrade query plan and the upgraded query plan; The first determining module is used to determine, based on the statistical object dependency graph, a statistical object associated with the drift plan node as a candidate statistical object when the drift plan node meets the preset risk conditions. The second determining module is used to determine the contribution and correction cost of each candidate statistical object, and to determine the statistical object to be corrected from the candidate statistical objects based on the contribution and correction cost of each candidate statistical object. The correction module is used to perform statistical information correction processing on the statistical object to be corrected, so as to regenerate the query plan corresponding to the target query based on the corrected statistical information, and determine whether the plan drift has converged.

[0007] Compared with the prior art, the significant advantages of this application are as follows: (1) Before the database upgrade, this application constructs a statistical object dependency graph. After the upgrade, the drift plan node is automatically determined by comparing the query plan before and after the upgrade. The candidate statistical object that caused the drift is located in reverse along the dependency graph, realizing the automatic attribution of the plan drift to the statistical root cause. This solves the problem that after the upgrade, only statistical information is retained but the plan may still drift and the efficiency of manual comparison of plans is low.

[0008] (2) This application only performs selective statistical information correction within the upgrade window resource budget for statistical objects that are related to the drift plan node and have a high contribution, according to the ratio of contribution to correction cost, thus avoiding the high overhead of collecting statistical information from the entire database and shortening the upgrade window.

[0009] (3) This application enables the optimizer to choose a reasonable plan autonomously after the upgrade by modifying the statistical information itself, rather than forcing the use of the query plan before the upgrade. It also determines whether the drift has converged by regenerating the query plan to form a closed loop verification. If the drift has not converged, iterative correction or manual suggestions are output, which reduces the processing cost of key query performance rollback after database upgrade. Attached Figure Description

[0010] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings: Figure 1 This is a flowchart illustrating a method for attributing database upgrade plan drift and correcting statistical information in an embodiment of this application. Figure 2 This is a schematic diagram of a statistical object dependency graph in an embodiment of this application; Figure 3 This is a schematic diagram illustrating a joint ranking of contribution and correction cost in an embodiment of this application; Figure 4 This is a flowchart illustrating a closed-loop correction process in an embodiment of this application; Figure 5 This is a schematic diagram of the structure of a database upgrade plan drift attribution and statistical information correction system according to an embodiment of this application; Figure 6 This is a schematic diagram of another structure of a database upgrade plan drift attribution and statistical information correction system in an embodiment of this application. Detailed Implementation

[0011] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0012] Database query optimizers typically rely on statistical objects such as table row count, column cardinality, null value ratio, most common values, histograms, relevance, expression statistics, and extended statistics for multiple columns to estimate execution plan costs. During major database version upgrades, even if the data itself remains unchanged, the optimizer code, cost model, index selection rules, join order search strategy, parallel execution strategy, and statistical interpretation logic may change. This can lead to the same statistical information being interpreted differently before and after the upgrade, causing the execution plan for the same query to drift after the upgrade. For example, changes in scan method, index selection, join order, or join algorithm can cause performance regressions for critical queries.

[0013] In existing technologies, a full database statistics collection is typically performed before and after an upgrade to regenerate statistics. However, for large databases, large tables, partitioned tables, or high-concurrency scenarios, full database statistics collection consumes significant input / output and processor resources and may prolong the upgrade window. Furthermore, the statistical objects that actually cause execution plan drift are usually concentrated in a few predicate columns, join columns, partition keys, expressions, or extended statistical objects. Therefore, recollecting all statistics is costly and lacks specificity. In addition, existing technologies maintain execution plan stability through plan baselines, hints, or execution plan capture and reuse mechanisms. However, these solutions only constrain the optimizer to use known execution plans and do not correct the root cause of the estimation errors—the statistical information. This may introduce new performance risks when data distribution continues to change. Moreover, having database administrators compare query plans before and after the upgrade line by line is inefficient and yields unstable results when there are many queries, complex plan nodes, or multiple table joins.

[0014] Based on this, embodiments of this application provide a method and system for attributing database upgrade plan drift and correcting statistical information, which is used to automatically locate the statistical objects that cause execution plan drift after database upgrade and correct them in a targeted manner to restore the stability of the query plan at a lower cost.

[0015] The various embodiments of this application are described in detail below with reference to the accompanying drawings.

[0016] This application provides a method for attributing database upgrade plan drift and correcting statistical information, such as... Figure 1 As shown, the method may include steps S101-S105: S101. Before the database upgrade, generate the pre-upgrade query plan corresponding to the target query, and construct the corresponding statistical object dependency graph based on the statistical objects associated with each plan node in the pre-upgrade query plan.

[0017] The target query is the query statement for the plan drift to be monitored.

[0018] The target query can be a key query statement extracted from the database's historical load. This historical load can originate from statistical information collection views, slow query logs, application query statement whitelists, business load testing scripts, or manual input. The priority of each target query can be determined based on call frequency, average execution time, business level, resource consumption, and historical fluctuations. The modification method in this application can be executed by a database upgrade tool, database kernel extension, cloud database control plane component, or independent operation and maintenance tool. Its input can include a set of key query statements, the database version before the upgrade, the database version after the upgrade, the query plan, a statistical information directory, a system directory, extended statistical objects, partition information, expression index information, and resource budgets. Statistical objects are the carriers of statistical information, such as tables, columns, partitions, indexes, or extended statistical objects. Statistical information is the metadata carried by the statistical objects, such as the number of rows, column cardinality, number of deduplicated values, histograms, most common values, and correlations.

[0019] It should be noted that the server, as the implementing entity of the database upgrade plan drift attribution and statistical information correction method, exists only as an example, and the implementing entity is not limited to the server. This application does not make any specific limitation on this.

[0020] In one embodiment of this application, before generating the pre-upgrade query plan corresponding to the target query, the method further includes: The target query is normalized to determine the first query template identifier corresponding to the target query. Normalization includes determining the data type of each constant in the target query and replacing each constant with parameter placeholders, while keeping the table names, column names, join structures, sorting structures, and grouping structures of the target query unchanged.

[0021] Specifically, this application can first perform syntax and semantic analysis on the target query to determine the data type of the constants, and then replace the constants with parameter placeholders according to their order of appearance. For example, status='PAID' AND region_id=10 can be normalized to status=$1::text AND region_id=$2::integer, where the table name, column name, join, sorting, and grouping structure remain unchanged. For value lists (IN lists), the element type and list length can be preserved to avoid merging lists with excessive differences into the same template. The first query template identifier is used to characterize the query structure of the target query; that is, query statements with different constant values ​​but the same query structure correspond to the same first query template identifier. Through normalization, the impact of differences in constant values ​​on query plan comparison can be eliminated, ensuring the comparability of plans with the same query structure before and after the upgrade.

[0022] This application performs EXPLAIN or equivalent plan generation operations on the target query when generating the pre-upgrade query plan to obtain the pre-upgrade plan tree and extract structured plan node information. For example, EXPLAIN (FORMAT JSON, VERBOSE TRUE, COSTS TRUE) can be executed to obtain the plan tree. The native fields of EXPLAIN are used to record node type, relation name, index name, filtering or join conditions, estimated number of rows, cost, parallel information, and sub-plans; the node number, parent node number, and node path are generated by the system when parsing JSON. Objects are identified by schema name and object name, and the condition text follows the parameterization rules in the normalization process, without relying on object identifiers (OIDs) that may change across versions, thus ensuring cross-version comparability.

[0023] Furthermore, based on the statistical objects associated with each plan node in the pre-upgrade query plan, a corresponding statistical object dependency graph is constructed, specifically including: (1) Determine the relationship between the scan node or filter node and the table, partition, and predicate column accessed by the scan node or filter node; (2) Determine the relationship between the ordinary index scan node and the table and column accessed by the ordinary index, and the relationship between the expression index scan node and the expression statistics object corresponding to the expression index; (3) Determine the association between the grouping node or aggregation node and the grouping column corresponding to the grouping node or aggregation node; (4) Determine the relationships between the tables or subqueries on both sides of the join node, the join key column corresponding to the join node, and the columns involved in the filtering conditions on each side; Based on the aforementioned relationships, association edges are established between the planning nodes and statistical objects to construct a statistical object dependency graph. This graph includes planning nodes and statistical object nodes. Statistical objects are one or more of the following: tables, columns, partitions, indexes, or extended statistics corresponding to the planning nodes. Statistical objects can include at least one of the following: table-level statistical objects, column-level statistical objects, partition-level statistical objects, index-related statistical objects, expression-based statistical objects, multi-column extended statistical objects, ndistinct statistical objects, and dependency statistical objects. It should be noted that the join nodes associate the row counts of the relationships on both sides, single-column statistics of the join key, and same-table statistics involved in the filtering conditions on each side. Cross-table multi-column extended statistics are not established, thus limiting the attribution scope to the objects that the statistical information can actually cover.

[0024] Specifically, dependent objects can be extracted from the relations, predicate columns, join columns, sorted columns, grouping columns, expressions, index conditions, partition keys, and extended statistical objects involved in the plan node, forming a directed graph with query templates, plan nodes, and statistical objects as nodes. The structure of the statistical object dependency graph can be as follows: Figure 2 As shown, a target query (i.e., the target SQL template) is connected to multiple plan nodes, each of which is further connected to related tables, columns, indexes, expressions, partitions, and extended statistics objects. When a plan node drifts, the system reverses the dependency graph to locate candidate statistics objects. Specifically, scan or filter nodes are associated with row counts and predicate column counts for the table or partition they belong to; ordinary index scan nodes are associated with statistics for the accessed tables and columns, while expression index scan nodes are associated with expression statistics; grouping or aggregation nodes are associated with deduplication statistics for grouped columns; multiple related columns within the same table can be associated with dependency extended statistics objects, most common value extended statistics objects, or deduplication value extended statistics objects; join nodes are associated with row counts for the relationships (tables or subqueries) on both sides, single-column statistics for the join key column, and same-table statistics for columns involved in the filtering conditions on each side, without creating multi-column extended statistics objects across tables.

[0025] Furthermore, while constructing the dependency graph, the system also records the context information of each statistical object, including object type, table or partition to which it belongs, statistical objective, most recent sampling time, estimated row count, histogram summary, most common value summary, extended statistical type, and sampling cost estimate. This data can be divided into two categories: direct extraction and system calculation. Plan information is extracted from the EXPLAIN JSON output of the plan generation operation; the estimated number of pages and rows in a table is extracted from `pg_class` and used as estimates; single-column statistics are extracted from `pg_stats`; extended statistical definitions are extracted from `pg_statistic_ext`, and statistical results are extracted from `pg_stats_ext` and `pg_stats_ext_exprs`; the most recent analysis time and modification ratio are extracted from `pg_stat_all_tables`. Statistical staleness is calculated based on the most recent analysis time and modification ratio; correction cost is estimated based on the number of pages, statistical objective, historical analysis time, and current lock wait status. When the estimated number of rows is unknown or non-positive, the modification ratio is not directly calculated.

[0026] S102 After the database upgrade, generate the upgraded query plan corresponding to the target query, and determine the drift plan node based on the query plan before and after the upgrade.

[0027] Among them, the drift plan node is the plan node whose execution method changes between the query plan before the upgrade and the query plan after the upgrade.

[0028] Database upgrades can be performed using pg_upgrade or other database upgrade tools. If the upgrade tool retains statistics (for example, pg_upgrade in PostgreSQL 18 supports retaining optimizer statistics during the upgrade process), the system records the version, time, and scope of the retained statistics.

[0029] In this embodiment of the application, generating the upgraded query plan corresponding to the target query specifically includes: The query statements after the database upgrade are normalized to determine the second query template identifier for each query statement. From the upgraded query statements, those with the same second query template identifier as the first query template identifier of the target query are selected as the upgraded query statements corresponding to the target query. An upgraded query plan is then generated for each upgraded query statement.

[0030] Specifically, since the constant values ​​of the same query may change after a database upgrade, to avoid the inability to identify the same query after the upgrade due to constant differences, the system corresponding to the modified method of this application can also perform the above-mentioned normalization processing on the query statements run after the database upgrade, to obtain the second query template identifier corresponding to each query statement, and then compare the second query template identifier with the first query template identifier of the target query; when the two are the same, it indicates that the query statement has the same query structure as the target query, and the query statement is identified as the upgraded query statement corresponding to the target query, thereby generating the upgraded query plan corresponding to the upgraded query statement. Through the matching of the first query template identifier and the second query template identifier, it can be ensured that the query plan before the upgrade and the query plan after the upgrade are generated for the same query structure, making the determination of subsequent drift plan nodes comparable. Additionally, when generating the query plan before the upgrade, a plan generation operation can be performed on each target query (e.g., executing EXPLAIN (FORMAT JSON, VERBOSE TRUE, COSTS TRUE)) to obtain the query plan before the upgrade and extract structured plan node information such as plan node type, relation name, index name, filtering or join conditions, estimated number of rows, estimated cost, parallel information, and sub-plans. The node number, parent node number, and node path can be generated by the system during parsing. Objects are identified by schema name and object name, and the condition text follows the above parameterization rules, without relying on object identifiers that may change across versions.

[0031] Furthermore, based on the query plan before and after the upgrade, the drift plan nodes are determined, specifically including: In the upgraded query plan, plan nodes with the same parent-child relationship and predicate conditions as those in the original query plan are identified as matched plan node pairs. The predicate conditions refer to the filtering or join predicates executed by the plan nodes. Plan nodes whose execution methods have changed are identified as drifting plan nodes. Node execution methods include one or more of the following: scan method, index selection, join order, join algorithm, estimated row count, estimated cost, parallel strategy, and sorting / aggregation strategy.

[0032] Specifically, this application first filters candidate nodes from the upgraded query plan based on the parent-child relationship and predicate conditions between plan nodes. Then, it matches the candidate nodes with the nodes in the pre-upgrade query plan using the subtree structure, thereby determining matching plan node pairs between the pre-upgrade and upgraded query plans. Plan nodes whose execution methods change are identified as drifting plan nodes. Changes in node execution methods can include changes in scanning method, index selection, join order, join algorithm, estimated row count, estimated cost, parallel strategy, or sorting / aggregation strategy. Among these, changes in estimated row count and estimated cost are normalized based on the relative changes in values ​​before and after the upgrade. In the absence of actual row count data, changes in estimated row count are referred to as the degree of estimated row count drift and are not considered as actual estimation error. Therefore, drifting plan nodes can include nodes with changes in scanning method, index selection, join order, join algorithm, estimated row count mutation, estimated cost, parallel strategy, sorting strategy, or aggregation strategy.

[0033] S103, if the drift plan node meets the preset risk conditions, determine the statistical objects associated with the drift plan node as candidate statistical objects based on the statistical object dependency graph.

[0034] In one embodiment of this application, before determining the statistical objects associated with the drift plan nodes based on the statistical object dependency graph, the method further includes: Based on the node execution method of the drift plan node, the business level of the target query, and the historical resource consumption of the target query, the risk score of the drift plan node is determined. If the risk score is greater than the preset risk threshold, the drift plan node is determined to meet the preset risk condition.

[0035] Specifically, the risk score is obtained by weighting at least two of the following: changes in node execution method, drift in estimated row count, changes in estimated cost, business level, and impact of historical resources, and is converted to a score from 0 to 100. For example, the weight of changes in node execution method is 30%, drift in estimated row count is 25%, changes in estimated cost is 20%, business level is 15%, and impact of historical resources is 10%. The preset risk threshold can be the larger of the baseline median before the upgrade plus 3 times the median absolute deviation and the preset lower limit; for example, the preset lower limit is 60 points, and the target query for the highest business level can be lowered to 50 points, using 60 points when historical samples are lacking. Subsequent statistical attribution is triggered only for drifting plan nodes with risk scores greater than the preset risk threshold, thereby avoiding invalid processing of a large number of plan nodes that have no significant impact after the upgrade. The above preset threshold is only an example; in actual use, it can also be set based on expert experience, and this application does not impose specific limitations here.

[0036] In this embodiment of the application, statistical objects associated with drift plan nodes are determined as candidate statistical objects based on the statistical object dependency graph, specifically including: Starting from the drift plan node, perform a reverse traversal to determine the directly associated statistical objects of the drift plan node, and then along each sub-plan node of the drift plan node to determine the associated statistical objects of each sub-plan node, and use each determined statistical object as a candidate statistical object.

[0037] Specifically, the reverse traversal in this application adopts a weighted priority approach. Starting from the drift plan node that meets the preset risk conditions, it first collects the directly associated statistical objects of that drift plan node, and then collects the statistical objects corresponding to related tables, partitions, predicate columns, and expressions along the input sub-plans of that drift plan node (i.e., the lower-level plan nodes of that drift plan node in the plan tree structure). For example, the default traversal depth of the reverse traversal is 2. Thus, starting from the drift plan node, it can locate the set of predicate columns, join columns, partition keys, related multi-column combinations, expression statistical objects, and extended statistical objects that may trigger the drift, as candidate statistical objects.

[0038] S104, determine the contribution and correction cost of each candidate statistical object, and determine the statistical object to be corrected from the candidate statistical objects based on the contribution and correction cost of each candidate statistical object.

[0039] In this embodiment of the application, the contribution and correction cost of each candidate statistical object are determined, and based on the contribution and correction cost of each candidate statistical object, the statistical object to be corrected is determined from the candidate statistical objects, specifically including: Based on the risk score of the drift plan node, the association path between the candidate statistical object and the drift plan node, the estimated row drift degree, the degree of statistical information deviation, and the business level, the contribution of each candidate statistical object is determined. If at least two drift plan nodes determine the existence of the same candidate statistical object, the contribution of the candidate statistical objects is accumulated. The sampling time, read / write overhead, computational resource overhead, and lock risk of each candidate statistical object are determined, and the correction cost of the candidate statistical object is determined. The correction priority of each candidate statistical object is determined based on the ratio of contribution to correction cost. Based on the correction priority, within the remaining resource budget for database upgrade, statistical objects to be corrected are selected from the candidate statistical objects to correct the statistical information corresponding to the statistical objects to be corrected. Candidate statistical objects not identified as requiring correction are added to the audit report.

[0040] In other words, the contribution of candidate statistical objects is determined by a combination of the risk score of the drift plan nodes, the estimated row drift degree of the association path between the candidate statistical objects and the drift plan nodes, the degree of statistical information deviation, and the business level of the target query. The degree of statistical information deviation refers to the deviation between the statistical information and the actual data. When at least two drift plan nodes point to the same candidate statistical object through reverse traversal, it indicates that the candidate statistical object affects multiple plan nodes. In this case, the contribution of the candidate statistical object is accumulated to increase its correction priority. The correction cost of candidate statistical objects is determined based on sampling time, read / write overhead, computational resource overhead, and lock risk; that is, the estimated sampling time, input / output overhead, processor overhead, and lock risk are combined into the correction cost. Following the principle of high contribution and low correction cost, the correction priority of each candidate statistical object is determined based on the ratio of contribution to correction cost. Subsequently, according to the correction priority, within the remaining resource budget for database upgrades (e.g., remaining time, input / output, and concurrency budget), statistical objects to be corrected are sequentially selected from the candidate statistical objects. In cases where the correction priority is the same, candidate statistical objects with direct association, high business level, and low lock risk are prioritized. Candidate statistical objects not identified as requiring correction are added to the audit report and can be retained for the next round of the candidate queue for processing in subsequent rounds. A diagram illustrating the joint ranking of contribution and correction cost can be shown below. Figure 3 As shown, candidate statistical objects are divided into different priorities according to their contribution and correction cost.

[0041] S105, Perform statistical information correction processing on the statistical object to be corrected, and regenerate the query plan corresponding to the target query based on the corrected statistical information, and determine whether the plan drift has converged.

[0042] In this embodiment of the application, the statistical information correction process for the statistical object to be corrected specifically includes: If the deviation rate between the row count statistics of the table or partition corresponding to the statistical object to be corrected and the current row count of the table or partition meets the first preset condition, the statistical information of the table or partition is re-collected; wherein, the deviation rate is the ratio of the absolute value of the difference between the row count statistics and the current row count to the current row count; if the statistical target value of a single column corresponding to the statistical object to be corrected is less than the preset statistical target threshold, the statistical target value of the single column is adjusted according to the preset statistical target threshold, and the statistical information of the single column is re-collected; if there are multiple related filter columns within the same table, a dependency relationship is created to extend the statistical object or the most common value is created to extend the statistical object, and the statistical information is re-collected; if the deduplication statistics of the combined group and the combined group If the deviation between the actual number of deduplicated values ​​and the actual number of deduplicated values ​​meets the second preset condition, a deduplicated value extended statistical object is created, and statistical information is re-collected; where the deviation is the absolute value of the difference between the deduplicated value statistics and the actual number of deduplicated values; if no expression statistical object corresponding to the expression predicate is created, an expression statistical object corresponding to the expression predicate is created, and statistical information is re-collected; if the drift plan node is a join node, based on the join key column, join predicate, and the tables or partitions involved in the join, it is determined whether the multi-column extended statistical object corresponding to the join node is missing, outdated, or incompatible with the upgraded optimizer. If so, the corresponding multi-column extended statistical object is generated, and statistical information is re-collected.

[0043] In other words, this application selects a statistical information correction strategy based on the source of the problem. When the deviation rate between the row count statistics of the table or partition corresponding to the statistical object to be corrected and the actual row count meets the first preset condition, it indicates that the row count statistics of the table or partition can no longer reflect the actual row count. At this time, statistical information is re-collected for the table or partition (i.e., partial statistical information collection is performed), provided that the deviation rate is greater than the first preset deviation threshold. When the statistical target value of a single column corresponding to the statistical object to be corrected is less than the preset statistical target threshold, it indicates that the statistical accuracy of the single column is insufficient. At this time, the statistical target value of the single column is increased according to the preset statistical target threshold, and statistical information is re-collected for the single column. When there are multiple correlated filter columns within the same table, it indicates that the correlation between columns may affect the selection rate estimation. At this time, a dependency relationship is created to extend the statistical object or the most common value is created to extend the statistical object, and statistical information is re-collected. When the deviation between the deduplication statistics of the combined group and the actual deduplication count of the combined group meets the second preset condition, it indicates that the cardinality statistics of the combined group are abnormal. At this time, a deduplication extended statistical object is created, and statistical information is re-collected, provided that the deviation is greater than the second preset deviation threshold. When no expression statistics object corresponding to the expression predicate is created, it indicates that the expression predicate lacks statistical support. In this case, an expression statistics object corresponding to the expression predicate is created, and statistical information is re-collected. When the drift plan node is a join node, based on the join key column, join predicate, and the tables or partitions involved in the join, it is determined whether the multi-column extended statistics object corresponding to the join node is missing, outdated, or incompatible with the upgraded optimizer. If so, the corresponding multi-column extended statistics object is generated, and statistical information is re-collected. The refresh of the existing statistics objects mentioned above is uniformly completed by re-collecting statistical information. It should be noted that the statistical target value is a parameter that controls the sampling accuracy of single-column statistics; dependency extended statistics object, most common value extended statistics object, deduplicated value extended statistics object, and expression statistics object are all specific types of extended statistics objects. The thresholds mentioned above in this application can be set based on expert experience in actual scenarios, and this application does not impose specific limitations on them.

[0044] In this embodiment of the application, based on the corrected statistical information, a new query plan corresponding to the target query is generated, and it is determined whether the plan drift has converged. Specifically, this includes: Based on the corrected statistical information, a new query plan is generated for the target query. The query-level drift level is determined based on the degree of change in the execution method of each drift plan node. If the query-level drift level is less than a preset threshold, the plan drift is considered converged. If the plan drift has not converged, the reverse traversal depth of the candidate statistical objects is updated according to preset rules. The statistical objects obtained from the traversal are then identified as candidate statistical objects, and the next round of statistical information correction is performed. If convergence is still not achieved after multiple rounds of statistical information correction, correction suggestions are output. These suggestions include at least index adjustment suggestions, query rewriting suggestions, or manual review suggestions.

[0045] In other words, after the statistical information is corrected, this application regenerates the query plan corresponding to the target query based on the corrected statistical information, and summarizes the degree of change in the execution mode of each drift plan node by weighting the node risk to obtain the query-level drift degree value of the target query. When the query-level drift degree value is less than a preset threshold, the plan drift is determined to have converged; optionally, when the improvement of the query-level drift degree value relative to the original value reaches a preset proportion (e.g., 50%) and there are no high-risk nodes, the plan drift can also be determined to have converged. When the plan drift has not converged, the reverse traversal depth of the candidate statistical objects is updated according to preset rules, such as increasing the traversal depth, expanding the scope of indirect dependencies, or appropriately lowering the admission threshold, and the statistical objects obtained by traversal are re-determined as candidate statistical objects, and the next round of statistical information correction processing is performed. The closed-loop correction process can be as follows: Figure 4 As shown, the system starts from the attribution results of the drift plan nodes, selects statistical objects with high contribution and low correction cost for priority correction, regenerates the query plan and calculates the drift degree value. If convergence is achieved, a report is output; otherwise, it returns to the statistical information correction stage. During automatic correction, relevant statistical snapshots are saved before correction. If the plan deteriorates after correction, the statistical recovery interface or compensation statement supported by the target version is used first for recovery. If reliable recovery is not possible, automatic correction is stopped and transferred to manual review. Automatic correction is executed for a maximum of 3 rounds, subject to time and resource budget constraints. For scenarios where convergence is still not achieved after multiple rounds of statistical information correction, the system outputs correction suggestions. These suggestions include at least index adjustment suggestions, query rewriting suggestions, or manual review suggestions. These suggestions are output as auxiliary information and do not replace the above statistical information correction processing.

[0046] All the thresholds mentioned above in this application can be set based on expert experience in real-world scenarios, and this application does not impose any specific limitations on them.

[0047] In a specific embodiment of this application, the scenario of upgrading from PostgreSQL 17 to PostgreSQL 18 is taken as an example. The target query is a three-table join query involving the order table, user table, and region table. Before the upgrade, the execution plan of this query uses a hash join, and obtains a relatively accurate estimate of the number of intermediate result rows by using the partition key of the order table and the statistical information of the join column of the user table. After the upgrade, due to changes in the optimizer cost model and the interpretation of statistical information, the join nodes of the same query drift from hash join to nested loop join, and the estimated number of rows of intermediate join nodes differs from the baseline before the upgrade by more than a preset multiple. The system first performs normalization processing on the target query before the upgrade, determines the first query template identifier, generates the query plan before the upgrade, and constructs a statistical object dependency graph. After the upgrade, the query statement executed after the upgrade is normalized, and the upgraded query statement corresponding to the target query is determined by matching the second query template identifier with the first query template identifier, and the upgraded query plan is generated. The system compares the query plans before and after the upgrade, identifies the connection nodes as drift plan nodes, and calculates risk scores based on the degree of change in the node execution method of the connection nodes, the business level of the target query, and historical resource consumption. If the risk score exceeds a preset risk threshold, the system performs a reverse traversal along the statistical object dependency graph starting from the drifted connection nodes to locate the partitioned statistical objects in the order table, the single-column statistical objects in the connection columns of the user table, and the multi-column extended statistical objects within the order table composed of partition keys and filter columns. If multiple related filter columns exist in the user table, the corresponding multi-column extended statistical objects within the user table are then associated. Subsequently, the system calculates the contribution of each candidate statistical object based on the estimated row drift degree, the degree of change in node execution method, and the number of times multiple key queries point to the same statistical object. Combining this with the correction cost, the system determines the correction priority, prioritizing connection column statistical objects and multi-column extended statistical objects with high contribution and low cost for statistical information correction. If the statistical object dependency graph shows that multi-column extended statistical objects are missing, the system generates and creates extended statistical objects and performs resampling. After the statistical information is corrected, the system regenerates the query plan for the target query. If the connection node is restored to a hash connection, or the query-level drift value drops below the preset threshold, the plan drift is determined to have converged, and the drift plan node, candidate statistical object, contribution, correction action, and corrected query plan are recorded. If convergence is not achieved, the set of candidate statistical objects is expanded, and the next round of statistical object attribution and statistical information correction is performed.

[0048] In another specific embodiment of this application, a scenario is addressed regarding the drift caused by changes in the partition table scanning method and missing expression statistics. Taking a partition query targeting a partitioned table after a major database version upgrade as an example: Before the upgrade, the query accessed a single target partition through an index scan of the partition key, and the query included a lowercase conversion predicate for text columns (e.g., lower(name)='abc'). After the upgrade, due to changes in the optimizer's estimation methods for partition pruning and expression selectivity, the query's scanning method shifted from index scan to sequential scan. Simultaneously, the expression predicate lacked corresponding expression statistics, resulting in a significant deviation between the estimated number of rows and the actual number of rows. Before the upgrade, the system constructs a statistical object dependency graph, recording the relationships between the scan nodes of the partition query and the row count statistics and partition key column statistics of the target partition, as well as the relationships between the expression index scan nodes and expression statistical objects. After the upgrade, the system compares the query plans before and after the upgrade, identifies the scan nodes as drift plan nodes, and calculates their risk scores. If the risk scores meet preset risk conditions, the system traverses the statistical object dependency graph in reverse to locate the row count statistics, partition key column statistics, and expression statistical objects corresponding to the expression predicates of the target partition as candidate statistical objects. Since the deviation rate between the row count statistics of the target partition and the current row count meets the first preset condition, the system re-collects statistical information for that partition. Since no expression statistical object corresponding to the expression predicate has been created, the system creates the corresponding expression statistical object and re-collects statistical information. After the correction is completed, the system regenerates the query plan. If the scanning method is restored to index scan and the query-level drift value drops below the preset threshold, the plan drift is considered to have converged.

[0049] This application does not limit the specific database version. Other major version upgrades can reuse the same steps, simply mapping the query plan fields and plan node types of each version to a unified structure and selecting the correction action according to the statistical types supported by the target version. Cross-version objects are identified using schema names, relation names, column names, and object definition summaries, without relying on object identifiers or query identifiers. If the target version does not support a certain correction, the action is disabled; only when applicable is a supported single-column statistical refresh or partial statistical information collection used, with a reduced result confidence level; otherwise, manual processing is required. Furthermore, the system can set resource protection strategies, such as limiting the number of concurrent correction tasks, limiting single-round sampling time, avoiding peak business hours, and using sampling ratio control for large tables, to further reduce the impact of statistical information correction on database operation.

[0050] Through the above scheme, this application can prioritize correcting the statistical objects most likely to cause plan drift within the database upgrade window resource budget, reducing the input / output, processor, and lock waiting overhead caused by the collection of full database statistical information; at the same time, by forming a closed loop through the convergence verification of the corrected plan, the processing cost of critical query performance rollback after a major database version upgrade is reduced, and the degree of automation of version upgrade is improved, reducing manual intervention.

[0051] Figure 5 This application provides a schematic diagram of the structure of a database upgrade plan drift attribution and statistical information correction system, as shown in the embodiments of this application. Figure 5 As shown, the database upgrade plan drift attribution and statistical information correction system 500 includes: The first generation module 501 is used to generate a pre-upgrade query plan corresponding to the target query before the database upgrade, and construct a corresponding statistical object dependency graph based on the statistical objects associated with each plan node in the pre-upgrade query plan. The target query is the query statement whose plan drift is to be monitored. The second generation module 502 is used to generate a post-upgrade query plan corresponding to the target query after the database upgrade, and determine the drift plan node based on the pre-upgrade query plan and the post-upgrade query plan. The drift plan node is the plan node whose execution method changes between the pre-upgrade query plan and the post-upgrade query plan. The first determination module 503 is used to determine the statistical objects associated with the drift plan node as candidate statistical objects based on the statistical object dependency graph, provided that the drift plan node meets preset risk conditions. The second determination module 504 is used to determine the contribution and correction cost of each candidate statistical object, and determine the statistical object to be corrected from the candidate statistical objects based on the contribution and correction cost of each candidate statistical object. The correction module 505 is used to perform statistical information correction processing on the candidate statistical objects, so as to regenerate the query plan corresponding to the target query based on the corrected statistical information, and determine whether the plan drift has converged.

[0052] In one embodiment, reference Figure 6The aforementioned system can be further refined into an SQL acquisition module, a pre-upgrade plan parsing module, a statistical object dependency graph construction module, a drift plan node identification module, a drift attribution module, a statistical information correction decision module, a statistical information correction execution module, a convergence verification module, and an audit report module. The query acquisition module obtains the target query set from the database's historical load and determines the priority of each target query based on call frequency, average execution time, business level, resource consumption, and historical fluctuations. The pre-upgrade plan parsing module generates query plans for target queries before the database upgrade and extracts structured plan node information, including plan node type, scanning method, index name, join order, join algorithm, estimated number of rows, estimated cost, filtering conditions, sorting method, parallelism, and involved objects. The statistical object dependency graph construction module establishes a statistical object dependency graph based on plan nodes, system directory, statistical information directory, partition definition, expression definition, and extended statistical definition. The drift plan node identification module generates a post-upgrade query plan for the same target query after the database upgrade, compares the query plans before and after the upgrade, and locates plan nodes where the execution method has significantly changed. The drift attribution module performs a reverse traversal along the statistical object dependency graph for drift plan nodes that meet preset risk conditions, generating a set of candidate statistical objects. It then calculates the contribution of each candidate statistical object based on the node execution method, estimated deviation multiple, object participation frequency, and business weight. The statistical information correction decision module determines the order and method of statistical information correction by combining the contribution of candidate statistical objects, object size, sampling cost, lock risk, business level, and resource budget. The statistical information correction execution module performs operations such as local statistical information collection, partition-level statistical refresh, adjustment of specified column statistical targets, multi-column extended statistical object refresh or creation, expression statistical refresh, and statistical consistency verification. The convergence verification module regenerates the query plan after statistical information correction, compares whether the drift plan nodes and drift degree have converged, and expands the candidate statistical object set or outputs non-statistical correction suggestions if convergence fails. The audit report module records the pre-upgrade query plan, post-upgrade query plan, drift plan nodes, candidate statistical objects, contribution, correction actions, post-correction query plan, and risk level for each target query, generating an upgrade acceptance report.

[0053] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the system embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0054] The systems and methods provided in this application are one-to-one correspondences. Therefore, the system also has similar beneficial technical effects as its corresponding method. Since the beneficial technical effects of the method have been described in detail above, the beneficial technical effects of the system will not be repeated here.

[0055] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0056] The above description is merely an embodiment of this application and is not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of the claims of this application.

Claims

1. A method for attributing database upgrade plan drift and correcting statistical information, characterized in that, The method includes: Before the database upgrade, a pre-upgrade query plan corresponding to the target query is generated, and a corresponding statistical object dependency graph is constructed based on the statistical objects associated with each plan node in the pre-upgrade query plan; the target query is the query statement for which plan drift is to be monitored. After the database upgrade, an upgraded query plan corresponding to the target query is generated, and a drift plan node is determined based on the pre-upgrade query plan and the upgraded query plan; wherein, the drift plan node is the plan node whose execution method changes between the pre-upgrade query plan and the upgraded query plan; If the drift plan node meets the preset risk conditions, the statistical objects associated with the drift plan node are determined as candidate statistical objects according to the statistical object dependency graph. The contribution and correction cost of each candidate statistical object are determined, and the statistical object to be corrected is determined from the candidate statistical objects based on the contribution and correction cost of each candidate statistical object. Perform statistical information correction processing on the statistical object to be corrected, and regenerate the query plan corresponding to the target query based on the corrected statistical information, and determine whether the plan drift has converged.

2. The database upgrade plan drift attribution and statistical information correction method according to claim 1, characterized in that, Before generating the pre-upgrade query plan corresponding to the target query, the method further includes: The target query is normalized to determine the first query template identifier corresponding to the target query; wherein, the normalization process includes determining the data type of each constant in the target query and replacing each constant with a parameter placeholder, while the table name, column name, join structure, sorting structure and grouping structure in the target query remain unchanged; The process of generating the upgraded query plan corresponding to the target query specifically includes: The normalization process is performed on the query statements after the database upgrade to determine the second query template identifier corresponding to each query statement. From the query statements after the database upgrade, determine the query statement whose second query template identifier is the same as the first query template identifier of the target query, and use it as the upgraded query statement corresponding to the target query; Generate the upgraded query plan corresponding to the upgraded query statement.

3. The method for attributing database upgrade plan drift and correcting statistical information according to claim 1, characterized in that, The step of constructing a corresponding statistical object dependency graph based on the statistical objects associated with each plan node in the pre-upgrade query plan specifically includes: Determine the association between the scan node or filter node and the table, partition, and predicate column accessed by the scan node or filter node; Determine the association between the ordinary index scan node and the tables and columns accessed by the ordinary index scan node, and the association between the expression index scan node and the expression statistics object corresponding to the expression index; Determine the association between the grouping node or aggregation node and the grouping column corresponding to the grouping node or aggregation node; Determine the relationships between the tables or subqueries on both sides of the connection node, the connection key column corresponding to the connection node, and the columns involved in the filtering conditions on each side; Based on the aforementioned relationships, an association edge is established between the planning node and the statistical object to construct the statistical object dependency graph; wherein, the statistical object dependency graph includes planning nodes and statistical object nodes, and the statistical object is one or more of the following: table, column, partition, index, or extended statistics corresponding to the planning node.

4. The method for attributing database upgrade plan drift and correcting statistical information according to claim 1, characterized in that, The step of determining the drift plan node based on the pre-upgrade query plan and the post-upgrade query plan specifically includes: In the upgraded query plan, plan nodes that have the same parent-child relationship and the same predicate conditions as the plan nodes in the pre-upgrade query plan are identified as matching plan node pairs; wherein, the predicate conditions are the filtering predicates or join predicates executed by the plan nodes. Among the planned node pairs, the planned node whose node execution method changes is identified as the drifted planned node; the node execution method includes one or more of the following: scanning method, index selection, join order, join algorithm, estimated number of rows, estimated cost, parallel strategy, sorting and aggregation strategy.

5. The database upgrade plan drift attribution and statistical information correction method according to claim 4, characterized in that, Before determining the statistical objects associated with the drift plan node based on the statistical object dependency graph, the method further includes: The risk score of the drift plan node is determined based on the degree of change in the node execution method of the drift plan node, the business level of the target query, and the historical resource consumption of the target query. If the risk score is greater than a preset risk threshold, the drift plan node is determined to meet the preset risk condition.

6. The method for attributing database upgrade plan drift and correcting statistical information according to claim 1, characterized in that, The step of determining the statistical objects associated with the drift plan nodes as candidate statistical objects based on the statistical object dependency graph specifically includes: Starting from the drift plan node, perform a reverse traversal to determine the directly associated statistical objects of the drift plan node, and along each sub-plan node of the drift plan node, determine the statistical objects associated with each sub-plan node, and use each determined statistical object as the candidate statistical object.

7. A method for attributing database upgrade plan drift and correcting statistical information according to any one of claims 1-6, characterized in that, The step of determining the contribution and correction cost of each candidate statistical object, and determining the statistical object to be corrected from the candidate statistical objects based on the contribution and correction cost of each candidate statistical object, specifically includes: Based on the risk score of the drift plan node, the association path between the candidate statistical object and the drift plan node, the estimated row drift degree, the degree of statistical information deviation, and the business level, the contribution of each candidate statistical object is determined; If at least two of the drift plan nodes determine that the same candidate statistical object exists, the contribution of the candidate statistical object is accumulated. The correction cost of the candidate statistical objects is determined based on the sampling time, read / write overhead, computing resource overhead, and lock risk of the candidate statistical objects. The correction priority of each candidate statistical object is determined based on the ratio of the contribution to the correction cost. Based on the correction priority, within the remaining resource budget for database upgrade, statistical objects to be corrected are determined from the candidate statistical objects to correct the statistical information corresponding to the statistical objects to be corrected, and candidate statistical objects that are not determined to be statistical objects to be corrected are added to the audit report.

8. The method for attributing database upgrade plan drift and correcting statistical information according to claim 1, characterized in that, The statistical information correction process performed on the statistical object to be corrected specifically includes: If the deviation rate between the row count statistics of the table or partition corresponding to the statistical object to be corrected and the current row count of the table or partition meets a first preset condition, the statistical information of the table or partition is re-collected; wherein, the deviation rate is the ratio of the absolute value of the difference between the row count statistics and the current row count to the current row count; If the statistical target value of a single column corresponding to the statistical object to be corrected is less than a preset statistical target threshold, the statistical target value of the single column is adjusted according to the preset statistical target threshold, and statistical information is re-collected for the single column; When there are multiple related filter columns within the same table, create dependency extended statistics or most common value extended statistics and recollect statistics. If the deviation between the deduplication statistics of the combined group and the actual number of deduplication values ​​of the combined group meets the second preset condition, a deduplication extended statistical object is created, and statistical information is collected again; wherein, the deviation is the absolute value of the difference between the deduplication statistics and the actual number of deduplication values. If no expression statistics object corresponding to the expression predicate is created, an expression statistics object corresponding to the expression predicate is created, and statistical information is collected again. When the drift plan node is a join node, based on the join key column, join predicate, and the table or partition involved in the join corresponding to the join node, determine whether the multi-column extended statistics object corresponding to the join node is missing, outdated, or incompatible with the upgraded optimizer. If so, generate the corresponding multi-column extended statistics object and re-collect statistics information.

9. The method for attributing database upgrade plan drift and correcting statistical information according to claim 1, characterized in that, The process of regenerating the query plan corresponding to the target query based on the corrected statistical information and determining whether the plan drift has converged specifically includes: Based on the revised statistical information, the query plan corresponding to the target query is regenerated, and the query-level drift degree value of the target query is determined according to the degree of change in the execution mode of each drift plan node. If the query-level drift value is less than a preset threshold, the plan drift is determined to have converged. If the plan drift does not converge, the reverse traversal depth of the candidate statistical objects is updated according to preset rules. The statistical objects obtained by traversal are determined as candidate statistical objects, and the next round of statistical information correction processing is performed. If the plan still does not converge after multiple rounds of statistical information correction processing, correction suggestions are output. The correction suggestions include at least index adjustment suggestions, query rewriting suggestions, or manual review suggestions.

10. A database upgrade plan drift attribution and statistical information correction system, characterized in that, The system includes: The first generation module is used to generate a pre-upgrade query plan corresponding to the target query before the database upgrade, and to construct a corresponding statistical object dependency graph based on the statistical objects associated with each plan node in the pre-upgrade query plan; the target query is the query statement of the plan drift to be monitored. The second generation module is used to generate an upgraded query plan corresponding to the target query after the database upgrade, and to determine a drift plan node based on the pre-upgrade query plan and the upgraded query plan; wherein, the drift plan node is a plan node whose execution method changes between the pre-upgrade query plan and the upgraded query plan; The first determining module is used to determine, based on the statistical object dependency graph, a statistical object associated with the drift plan node as a candidate statistical object when the drift plan node meets the preset risk conditions. The second determining module is used to determine the contribution and correction cost of each candidate statistical object, and to determine the statistical object to be corrected from the candidate statistical objects based on the contribution and correction cost of each candidate statistical object. The correction module is used to perform statistical information correction processing on the statistical object to be corrected, so as to regenerate the query plan corresponding to the target query based on the corrected statistical information, and determine whether the plan drift has converged.

Images