Automated feedback and continuous learning for query optimization

Abstract

This provides a method for improving query optimization in database management systems. [Solution] The method uses a workload feedback process that uses query feedback performed during query compilation to identify correlations and relationships based on the structure of the query feedback and the runtime feedback performed, submits the query feedback and runtime feedback to a machine learning engine to update a set of models from the collected data, updates the set of models based on the submitted query feedback and runtime feedback, and outputs a new, updated, or retrained model based on data collected from the execution of the query, the submitted query feedback and runtime feedback, or the trained generated modes to identify errors.

Description

Technical Field

[0001] The present invention generally relates to the field of query optimization, and more particularly to query optimization executed in a distributed computing environment.

Background Art

[0002] Query optimization is a function of many relational database management systems. The query optimizer attempts to determine the most efficient form for executing a given query by considering possible query plans. Generally, once a query is submitted to a database server and parsed by a parser, the query optimizer cannot be directly accessed by the user. Once the query is submitted and parsed, it is then passed to the query optimizer for optimization. Note that some database engines allow the query optimizer to be guided using hints or instructions from the user or source code.

[0003] A query is a request for information from a database. Query results are generated by accessing relevant database data and manipulating it in a way that yields the requested information. In many cases, database structures are complex, and the data required for a query is retrieved from the database by accessing it in various forms, through various access paths, and with various join methods and join sequences. Typically, each of the different paths and / or methods requires a different processing time. Even the processing time for the same query can vary greatly, from less than a second to several hours, depending on the form the query is executed in. Query optimization is an automated process whose purpose is to find the form in which a given query is processed in the shortest possible time. While the wide range in query processing times justifies query optimization, finding the strictly optimal form of query execution from all possibilities is typically too complex, time-consuming, costly, and often practically impossible. [Overview of the project]

[0004] Embodiments of the present invention disclose methods, computer program products, and systems for query optimization, the embodiments comprising: using a workload feedback process including query feedback performed during query compilation to identify opportunities for improvement in cardinality estimation by a query optimizer; identifying correlations and relationships based on the structure of the query feedback and runtime feedback performed; collecting data from query execution by the query optimizer to identify errors in the query optimizer's estimation; submitting the query feedback and runtime feedback to a machine learning engine by the query optimizer to update a set of models; updating the set of models by the machine learning engine based on the submitted query feedback and runtime feedback; and outputting new, updated, or retrained models based on data collected from query execution, submitted query feedback and runtime feedback, or trained generated models to identify errors in the query optimizer's estimation.

[0005] The embodiments of the present invention will be described below as mere examples with reference to the accompanying drawings. [Brief explanation of the drawing]

[0006] [Figure 1] This is a functional block diagram showing a distributed data processing environment according to an embodiment of the present invention. [Figure 2] Figure 1 shows a block diagram illustrating an automated feedback and continuous learning component for automated feedback and continuous learning for query optimization, which runs on a server computer in the distributed data processing environment of the present invention. [Figure 3]The operational steps of an automated feedback and continuous learning component on a computing device in the distributed data processing environment of Figure 1 for automated feedback and continuous learning for query optimization, according to an embodiment of the present invention, are shown. [Figure 4] The operational steps of an automated feedback and continuous learning component on a computing device in the distributed data processing environment of Figure 1 for automated feedback and continuous learning for query optimization, according to an embodiment of the present invention, are shown. [Figure 5] Figure 1 shows a block diagram of the components of a server computer that executes the automated feedback and continuous learning components in the distributed data processing environment according to an embodiment of the present invention. [Modes for carrying out the invention]

[0007] Embodiments of the present invention recognize that current techniques (e.g., cumulative frequency function (CFF) models) have shown that estimations with deviations exceeding one order of magnitude may lead to suboptimal plans (e.g., plans that fall below a given threshold, fall outside a given range, or both), while estimations within one order of magnitude generally provide reasonable plans (e.g., plans that fall above a given threshold, fall within a given range, or both). Therefore, embodiments of the present invention recognize that models need to learn from their errors and respond to critical data points that may be missed during the sampling step. Embodiments of the present invention improve this technique by using machine learning (ML), enabling adaptability to changes that accumulate in the database relations over time.

[0008] Embodiments of the present invention recognize that current technology can be configured to work seamlessly with any model well known in the art (e.g., statistical models or small-sample models). Furthermore, methodologies related to embodiments of the present invention are not limited to cardinality estimation and are considered applicable to any aspect of query optimization. For example, methodologies related to embodiments of the present invention are considered easily configurable to estimate various costs (e.g., input / output (I / O), central processing unit (CPU), or network or memory usage of a particular operation, or a combination thereof), such as performance cost, economic cost, or both. Furthermore, embodiments of the present invention recognize that while various solutions involving the use of statistics as feedback have been implemented by various database vendors in the past, none of the implemented solutions have introduced machine learning capabilities into query optimization.

[0009] Furthermore, embodiments of the present invention recognize that current techniques construct query execution plans by using deep neural networks to continuously learn from previously executed queries. Embodiments of the present invention recognize that a major drawback of currently used methods known in the art is that they rely on learning correlations through word similarity in the data, which can result in models that are sometimes larger than the size of the data itself. Furthermore, embodiments of the present invention recognize that applying currently known methods in the art may produce and yield inaccurate results.

[0010] Embodiments of the present invention recognize that current database systems can collect column and multi-column statistics in automatic or manual modes to become more robust to changes in data by considering statistical correlations between multiple values. Similarly, the CFF model proposed in embodiments of the present invention can be fully retrained when larger changes in data occur and the model's predictions begin to accumulate larger errors. Automatic feedback is useful at this point to trigger the model retraining action. Embodiments of the present invention measure the performance of the model's estimated cardinality against the actual cardinality collected from each individual component of the executed access plan, and the feedback received from this measurement of performance is called observational feedback. Furthermore, if the optimizer identifies that there is no pre-built model for a relation or group of columns, the observational feedback can determine and identify which relation or individual columns or groups of columns have the potential to build a new model.

[0011] For example, a strong statistical correlation between values ​​in two columns could be a candidate (i.e., potentially a new model) because the combination of predicates on those columns would yield a more accurate estimate compared to the default independence assumption, which can lead to underestimation. Suppose the predicate pair TREE='APPLE' AND TYPE='RED APPLE' stores 40 different values ​​for TREE and 2000 different values ​​for TYPE in a table of 10000 rows. Assume that the same type of apple can occur in two different trees, resulting in 2500 unique combinations of TREE and TYPE values, meaning an average of 4 rows have unique combinations. The database optimizer might underestimate cardinality if probabilities are used for individual columns (i.e., simply (1 / 40)*(1 / 2000)*10000=0.125 rows). On the other hand, using a different number of combinations, embodiments of the present invention produce a more realistic estimate of (1 / 2500)*10000=4 Rows. Embodiments of the present invention maintain a list of models and a list of columns used to construct the models within a database table. In various embodiments of the present invention, the list of models and columns maintained may consist of new and past (e.g., previously used or generated) models and columns, or new and past columns with the potential to construct new models, or both. Furthermore, embodiments of the present invention enable maintainability aspects in cases where a database user needs to report query performance issues to a database vendor.

[0012] The maintainability aspect allows for testing after retraining or fallback to a previous model, and the model version and training timestamp are maintained (e.g., saved), allowing the model to revert from its current model version to a previous model version based on the maintained model version and training timestamp. For example, if a model's current model version is 2.2, its current training timestamp is May 1, 2020, and the most recently maintained model version is 2.1 with a training timestamp of April 29, 2020, the model would fall back to model version 2.1. Alternatively, in another embodiment, embodiments of the present invention allow the model to revert from a previous model version to the most recent model version.

[0013] Embodiments of the present invention recognize that current technology requires the implementation of a feedback loop from the runtime component to the optimizer component, or from the feedback loop to the optimizer component itself, in order to consider retraining an ML model. Furthermore, embodiments of the present invention recognize that current technology is costly to train compared to the proposed invention. Embodiments of the present invention recognize that in order to train a system in current technology, the system in current technology must either wait for user queries (for example, training the model may take longer depending on the query rate and data diversity (i.e., how representative the data is of actual variance)) or by randomly generating and executing queries, which would result in significant overhead. Embodiments of the present invention improve upon current technology by solving the aforementioned training and overhead problems by training one or more samples, discovering (i.e., identifying) candidate columns without querying the complete data (i.e., the entire set of data) through a given database interface, and using feedback from actual query execution to improve the retraining work so that the retrained model is more strongly related to the workload and ML model retraining is more efficient.

[0014] Furthermore, embodiments of the present invention propose the use of ML to have adaptability in accumulating changes to database relations over time. In various embodiments of the present invention, changes to the data involved in the model may trigger retraining if the estimation exceeds or falls within a predetermined range of actual query execution, actual cardinality, actual variance, or any combination thereof. Embodiments of the present invention improve current techniques by presenting a query optimization system for building, efficiently training, maintaining, evaluating, and using trained or other models. Furthermore, embodiments of the present invention improve the technique by enabling the invention to be applied to any statistical / trained model known in the art, and further embodiments of the present invention may bridge the gap between data discovery and feedback. Embodiments of the present invention recognize that loopback of error points will complicate the model and increase model size and inference time.

[0015] Embodiments of the present invention recognize that continuous learning is affected by model retraining using new data points or by model adjustments to internal weights. Furthermore, embodiments of the present invention recognize that looping through all queries or randomly selected queries from runtime feedback can bias the model's predictions. For example, embodiments of the present invention selectively learn through a predicate logic model that the majority of model errors have lower selectivity. In this example, looping through such points would bias the model toward lower selectivity. Various embodiments of the present invention may use stratified sampling in which each class represents a group of selectivity values ​​(e.g., values ​​from 0.0 to 1.0) to improve the technique and mitigate model bias. For example, using stratified sampling for all queries or randomly selected queries from runtime feedback improved the model's performance on the same test queries without stratified sampling.

[0016] The embodiments of the present invention can be implemented in various forms, and details of exemplary implementations will be described later with reference to the drawings (i.e., Figures 1 to 5).

[0017] Figure 1 is a functional block diagram showing a distributed data processing environment, generally indicated as 100, according to one embodiment of the present invention. As used herein, the term “distributed” refers to a computer system comprising several physically distinct devices operating together as a single computer system. Figure 1 shows only one implementation and does not imply any limitation on the environment in which various embodiments may be implemented. Numerous modifications to the shown environment can be made by those skilled in the art without departing from the scope of the invention as enumerated by the claims. The distributed data processing environment 100 includes computing devices 110 and a server computer 120 interconnected on a network 130.

[0018] Network 130 may, for example, be a storage area network (SAN), a telecommunications network, a local area network (LAN), a wide area network (WAN) such as the Internet, or wireless technology for short-range data exchange (using short-wavelength ultra-high frequency (UHF) radio waves in the 2.4 to 2.485 GHz industrial, scientific and medical (ISM) band from fixed and mobile devices to construct a personal area network (PAN) or a combination of the three), and may also include wired, wireless, or fiber optic connections. Network 130 may include one or more wired, wireless, or both networks capable of receiving and transmitting data, voice, or video signals, or a combination of any of these, including multimedia signals including voice, data, text, or video data. In general, the network 130 may be any combination of connections and protocols that support communication between computing devices 110 and server computers 120, as well as between computing devices or storage devices or both (not shown in Figure 1) within any other distributed data processing environment 100.

[0019] In some embodiments of the present invention, the computing device 110 may be, but is not limited to, any programmable electronic computing device, or any combination thereof, that can communicate via the network 130 with various components and devices in a distributed data processing environment 100, including, standalone devices, clients, servers, laptop computers, tablet computers, netbook computers, personal computers (PCs), smartphones, desktop computers, smart TVs, smartwatches, radios, stereo systems, cloud-based services (e.g., cognitive cloud-based services), AR glasses, virtual reality headsets, any HUD known in the art, and / or a distributed data processing environment 100. Generally, the computing device 110 may represent any programmable computing device or a combination of multiple programmable computing devices that can execute machine-readable program instructions and communicate with users of other computing devices via the network 130, or can execute machine-readable program instructions and communicate with a server computer 120, or both. In some embodiments, the computing device 110 may represent multiple computing devices.

[0020] In some embodiments of the present invention, the computing device 110 may represent any programmable electronic computing device or a combination of multiple programmable electronic computing devices that can execute machine-readable program instructions, manipulate executable machine-readable instructions, and communicate with server computers 120 and other computing devices (not shown) in a distributed data processing environment 100 via a network such as network 130. The computing device 110 may include an instance of a user interface (interface) 106 and local storage 104. In various embodiments not shown in Figure 1, the computing device 110 may have multiple user interfaces. In other embodiments not shown in Figure 1, the distributed data processing environment 100 may include multiple computing devices, multiple server computers, or multiple networks, or any combination thereof. The computing device 110 may include internal and external hardware components, as further illustrated and described with respect to Figure 5.

[0021] The user interface (interface) 106 provides an interface to the automatic feedback and continuous learning component (component) 112. The computing device 110 may enable a user or a client or both to interact with component 112 or server computer 120 or both through the user interface 106 in various ways, such as sending program instructions, receiving program instructions, sending or receiving messages or both, updating data, sending data, inputting data, editing data, collecting data or receiving data, or any combination thereof. In one embodiment, interface 106 may be a graphical user interface (GUI) or a web user interface (WUI), and may further display at least text, documents, web browser windows, user options, application interfaces, and instructions for action. Interface 106 may include data presented to the user (graphics, text, and sound) and control sequences used by the user to control actions. In another embodiment, interface 106 may be mobile application software that provides an interface between a user of computing device 110 and server computer 120. The mobile application software, or “app,” may be designed to run on smartphones, tablet computers, and other computing devices. In one embodiment, interface 106 may enable a user of computing device 110 to at least transmit data, input data, edit data (annotations), collect data, or receive data, or a combination thereof.

[0022] The server computer 120 may be a standalone computing device, a management server, a web server, a mobile computing device, one or more client servers, or any other electronic device or computing system capable of receiving, transmitting, and processing data. In other embodiments, the server computer 120 may represent a server computing system that utilizes multiple computers, such as a server system in a cloud computing environment, but is not limited to these. In another embodiment, the server computer 120 may represent a computing system that utilizes clustered computers and components (e.g., database server computers, application server computers, etc.) that, when accessed within a distributed data processing environment 100, function as a single pool of seamless resources. The server computer 120 may include internal and external hardware components, which are shown and described in more detail with respect to Figure 5. In some embodiments, the server computer 120 may represent multiple server computers.

[0023] Shared storage 124 and local storage 104 can each be a data / knowledge repository or database or both, which can be written to, read from, or both, by one or a combination of components 112, server computer 120, and computing device 110. In the illustrated embodiment, shared storage 124 exists on server computer 120, and local storage 104 exists on computing device 110. In another embodiment, shared storage 124 or local storage 104 or both can exist elsewhere within distributed data processing environment 100, provided that each can access and is accessible by computing device 110 and server computer 120. Each of shared storage 124 or local storage 104 or both can be implemented using any type of storage device that can store data and configuration files that can be accessed and utilized, by server computer 120, such as, but not limited to, a database server, hard disk drive, or flash memory.

[0024] In some embodiments of the present invention, each of the shared storage 124 or the local storage 104 or both may be a computer output to a hard drive, memory card, laser disk (cloud storage), or any form of data storage known in the art, or a combination thereof. In some embodiments, each of the shared storage 124 or the local storage 104 or both may be a database linked to one or more cloud storage systems or cloud networks, or both. In various embodiments, the shared storage 124 or the local storage 104 or both may have access to, store, or contain user data, physical room data, and meeting data, or data shared across the entire distributed data processing environment 100, or any combination thereof, or a combination thereof.

[0025] In the illustrated embodiment, database engine 140 is executed on server computer 120. In other embodiments, database engine 140 may be executed on computing device 110. In various embodiments of the invention not shown in FIG. 1, database engine 140 may be executed on multiple server computers 120 or multiple computing devices 110 or both. Database engine 140 is a fundamental software component used by a database management system (DBMS) to create, read, update, and delete (CRUD) database data, as understood in the art. In various embodiments of the invention, database engine 140 (i.e., the database management system) includes an application programming interface (API) that enables a user to interact with the underlying engine without going through a DBMS user interface.

[0026] In the embodiments shown, component 112 runs on a database engine 140 on a server computer 120. In other embodiments, component 112 may run on a computing device 110. In various embodiments of the invention not shown in Figure 1, component 112 may run on multiple database engines 140, multiple server computers 120, or multiple computing devices 110, or any combination thereof. In some embodiments, the location where component 112 resides or runs within the distributed data processing environment 100 is arbitrary, as long as component 112 is connected to or communicates with a database engine 140, a computing device 110, or a server computer 120, or any combination thereof, via the network 130, or both.

[0027] In various embodiments of the present invention, component 112 enables an automated feedback technique, and once the query has finished executing, component 112 may identify or retrieve the actual number of rows for each relation in the query or operator in the execution plan, or both, and compare the number of retrieved rows to one or more rows based on predicted cardinality. Cardinality estimates that are above or below a predetermined threshold may be held in a separate table. A table containing estimated cardinality anomalies against actual cardinality anomalies is aggregated, and rare and secondary feedback data is extracted by various algorithms (for example, the extracted rare and secondary feedback data may include relevance, frequency of use, and impact on performance in order to keep the data compact, and is supplied to a component that determines when and how to retrain the model). In various embodiments of the present invention, component 112 may extract rare and predetermined feedback, which may include relevance, frequency of use, and impact on performance in order to keep the data compact, and is supplied to a component that determines when and how to retrain the model. In various embodiments of the present invention, component 112 may hold only predicate literals. The next time the model is retrained, component 112 may add the retained literals to the materialized sample or to a new sample randomly collected from the original relationship.

[0028] In various embodiments of the present invention, depending on the actual implementation of the proposed model (e.g., the CFF model), collected points with prediction errors may also be used to adjust model parameters rather than for complete retraining. In various embodiments of the present invention, component 112 may collect column and multi-column statistics in automatic or manual mode to become more robust to changes in data by considering statistical correlations between multiple values. In various embodiments of the present invention, the proposed model may be completely retrained when larger changes in data occur and the model's predictions begin to accumulate larger errors, and larger data changes include changes in data that require more memory or computing power or both. In another embodiment of the present invention, when the ML model is used to predict operating costs or memory, actual operating costs or memory usage may be taken and used for comparison with estimated costs or memory or both to determine the accuracy of the estimated costs or memory or both, and / or to identify whether there are cost or memory savings, or areas that can be reduced to demonstrate cost or memory savings.

[0029] In various embodiments of the present invention, component 112 may identify when the model's predictions begin to accumulate errors, and may further activate automatic feedback that triggers a model retraining action. Component 112 may measure the model's performance against actual cardinality collected from each individual component of the executed access plan. Furthermore, in various embodiments of the present invention, component 112 may use observational feedback to determine which relationships or groups of columns have the potential to build a new model when the optimizer component identifies that there are no pre-built models based on relationships or groups of columns. Component 112 may store lists of models and lists of columns used to build models in a database table on local storage 104, shared storage 124, or both, and the stored lists of models and columns can be retrieved, referenced, compared against future lists of models and lists of columns used to build models, or any combination thereof. In various embodiments of the present invention, component 112 may, during database service, test a new or retrained model, or fall back to a previous model after retraining, or both, while maintaining (e.g., saving) the model version and training timestamp, and enabling the model to revert from its current model version to a previous model version based on the maintained model version and training timestamp.

[0030] Figure 2 shows a block diagram of a component 112 that communicates with a computing device 110 or a server computer 120 or both within the distributed data processing environment 100 of Figure 1 for automatic feedback and continuous learning for query optimization, according to an embodiment of the present invention. Figure 2 shows one implementation and does not imply any limitation on the environment in which various embodiments may be implemented. Numerous modifications to the shown environment can be made by those skilled in the art without departing from the scope of the present invention as enumerated by the claims.

[0031] In various embodiments of the present invention, component 112 may be adaptive and continuously learning, and further, component 112 may automate performance feedback of cardinality estimation (e.g., the predicted number of rows that will be input and output through various actions in the query execution plan). In various embodiments of the present invention, component 112 consists of two main processes: data discovery and workload feedback. The data discovery process is performed by discovery component 142, which performs an analysis of data and metadata (catalog 210) that is stored in the database or received from data input or both, in order to identify relationships or correlations or both within each table and across multiple tables. In various embodiments of the prior invention, component 112 may search and retrieve catalog 210 from an online search engine or an online database or both. The data discovery process pre-prepares the system with existing models (e.g., model 212) that may be available during query compilation and optimization.

[0032] The workload feedback process identifies opportunities for improvement in cardinality estimation through both query optimizer feedback and runtime feedback. Query optimizer feedback is performed by the query optimizer component (optimizer component) 148 during query compilation, and the optimizer component 148 may identify correlations and relationships based on the query structure. For example, if a model exists on table "T" with columns C1, C2, and C3, but multiple queries contain predicates that reference expressions on C2 and C3, for example C2='xyz' AND C4=10 AND MOD(C3,5)=0, then query feedback would identify that C4 and the expression MOD(C3,5) are missing and should be added to the model when it is retrained and evaluated. In the demonstrated embodiment, runtime feedback is performed by the runtime engine 146, which collects data from query execution to identify critical performance errors (e.g., points where the optimizer's estimation mispredicted the actual execution feedback). These misprediction points can also be used to tune model parameters rather than retraining the entire model.

[0033] In the embodiments shown, the machine learning engine (engine) 144 may perform the steps of evaluation 144a, generation or modification of one or more models 144b, and training of one or more models 144c. During the evaluation step (i.e., evaluation 144a), input data is accumulated from data of both discovery and feedback processes to identify a set of models (e.g., model 212) to build or modify from. Retraining of one or more models may be necessary if the data or workload has changed by a certain amount, or if model 212 lacks coverage of attributes (i.e., columns) relevant to the workload. The evaluation step (i.e., evaluation 144a) may include identifying whether a retraining operation may be necessary if an existing or functional / working model (i.e., a suitable model) already exists but the model no longer achieves the required level of performance, or automatically reverting to the previous model if the model has recently been updated. Evaluation 144a may identify whether a retraining operation is necessary if the model no longer achieves the required level of performance. In various embodiments of the present invention, during evaluation 144a, the engine 144 may determine whether the recently updated model achieves lower performance than the previous one, and if the updated model achieves lower performance, the engine 144 may automatically revert to the previous model.

[0034] In various embodiments of the present invention, during evaluation 144a, the engine 144 may determine whether an existing model that can be expanded with additional predetermined columns may require more time to retrain, and further may determine whether an existing model may need to be appropriately split into two or more models to reduce training time or model size. In various embodiments of the present invention, during the generation step (generating or modifying a model 144b), the engine 144 builds or updates a model (i.e., model 212) based on the input requirements from the evaluation step. Model 212 may be one or more models or a set of models in a database management system. In various embodiments of the present invention, during the training step 144c, the engine 144 may initiate full initial training of a new model, or initiate retraining of an existing model, or perform an incremental update of a model, depending on the evaluation performed in the evaluation step.

[0035] In various embodiments of the present invention, during query compilation, the query compiler may retrieve one or more available models by looking up models stored in a database (shared storage, local storage, or both) or online, and make them available for use during query optimization. Naturally, this system is not limited to CFF models. This is one embodiment of the present invention, and the system may be configured to work seamlessly with any model (e.g., a statistical model or a small sample) that has been trained, developed, acquired, or instructed, or any combination thereof. Furthermore, some embodiments of the present invention are not limited to cardinality estimation and are considered applicable for any aspect of query optimization. As an example, this methodology could easily be configured to estimate various costs, such as the usage of I / O, CPU, network, or memory, or any combination thereof, for a particular operation.

[0036] In various embodiments of the present invention, the runtime engine 146 may implement runtime feedback. In various embodiments of the present invention, in the context of cardinality estimation, the runtime engine 146 may identify and retrieve the actual number of rows for each relation in a query or operator in the execution plan, the number of actual rows may be predetermined. In various embodiments of the present invention, the runtime engine 146 may compare the retrieved actual number of rows to the predicted cardinality, and cardinality estimations that are above or below a predetermined threshold are stored as points in one or more separate tables, while cardinality estimations that are within or equal to the predetermined threshold are stored and maintained in the current table(s). In various embodiments of the present invention, the next time the model is retrained, the stored points may be added to the materialized sample or to a new randomly collected sample. A list of models and a list of columns used to build one or more models in a database table are stored on local storage 104, shared storage 124, or both, and the stored list of models and column lists can be retrieved and used to address future queries, model retraining, model building, or any other action known in the art, or any combination thereof. In various embodiments of the present invention, component 112 may store, maintain, or both, the model version and training timestamp of a new or retrained model. In various embodiments of the present invention, component 112 may, based on the stored model version and training timestamp, execute a fallback command (i.e., fallback) to the previous model (i.e., the stored model) after retraining.

[0037] In various embodiments of the present invention, the optimizer component 148 can perform an implementation of continuous learning. In various embodiments of the present invention, in the context of cardinality estimation, component 112 can determine and identify whether one or more queries are within an error threshold (for example, a single-digit error width in one or more queries is considered acceptable) before recognizing any plan changes selected by the optimizer component 148. Queries that are not within the error threshold are considered candidates for continuous learning.

[0038] Figure 3 shows, generally speaking, the operational steps of a component 112 communicating with a computing device 110 in the distributed data processing environment 100 of Figure 1 for automatic feedback and continuous learning for query optimization, according to an embodiment of the present invention. Figure 3 shows one implementation and does not imply any limitations with respect to the environment in which various embodiments may be implemented. Numerous modifications to the shown environment can be made by those skilled in the art without departing from the scope of the invention as enumerated by the claims.

[0039] In step 302, component 112 performs data discovery on the data and catalog. In various embodiments of the present invention, component 112 performs data discovery on the catalog 210 which has been received or retrieved or both via discovery component 142, and the data discovery process includes discovery component 142 performing analysis of the data and metadata in the database (i.e., catalog 210) to identify relationships, skews, or correlations, or any combination thereof, within and across multiple tables. This prepares the system in advance with models that may be available during query compilation and optimization. In various embodiments of the present invention, discovery component 142 may retrieve or receive or both the catalog 210 from local storage 104, shared storage 124, or an online search engine, or any combination thereof.

[0040] In step 304, component 112 identifies a set of relationships and correlations of interest within each table in the database and across multiple tables. In various embodiments of the present invention, component 112 may also identify one or more sets of relationships and correlations of interest within one or more tables in the database and across multiple tables using a data discovery process (similar to Figure 2) via discovery component 142, the data discovery process performing an analysis of the data and metadata in the database using predetermined criteria. In various embodiments of the present invention, one or more sets of relationships and correlations of interest may be identified based on predetermined data, parameters, or criteria, or a combination thereof.

[0041] In step 306, component 112 generates a model or a set of models. In various embodiments of the present invention, component 112 may generate a set of models using a set of relationships and correlations of interest identified using predetermined criteria via the machine learning engine 144. In some embodiments, the set of models may be a set of potentially useful models; that is, the generated models may be useful or can be used with some compactness at some point. In various embodiments of the present invention, component 112 may build or update one or more models via the machine learning engine 144 (for example, based on input requests from the evaluation step).

[0042] In step 308, component 112 performs runtime feedback. In various embodiments of the present invention, component 112 performs runtime feedback via runtime engine 146. In various embodiments of the present invention, component 112 may collect data from query execution via runtime engine 146 to identify errors in the optimizer's estimation. Points where prediction errors occurred may be used to tune model parameters rather than completely retraining the entire model.

[0043] In step 310, component 112 performs query feedback. In various embodiments, component 112 performs query feedback during query compilation via optimizer component 148. In various embodiments, component 112 may identify correlations and relationships via optimizer component 148 based on one or more query optimizer feedback structures and runtime feedback performed by runtime engine 146.

[0044] In step 312, component 112 identifies opportunities for improvement in cardinality estimation. In various embodiments of the present invention, component 112 may identify opportunities for improvement in cardinality estimation using a workload feedback process (as described above), which includes query feedback performed by a query optimizer (e.g., optimizer component 148) during query compilation, via optimizer component 148. In various embodiments of the present invention, component 112 may identify opportunities for improvement in cardinality estimation based on query feedback and runtime feedback, via optimizer component 148. In various embodiments of the present invention, component 112 may accumulate and evaluate input data from both discovery and feedback processes via machine learning engine 144 (similar to the evaluation step (evaluation 144a) in Figure 2), from which a set of models to build or modify is identified. The evaluation step (i.e., evaluation 144a) includes identifying whether a retraining operation may be necessary if a suitable model already exists but the model no longer achieves the required level of performance, or automatically reverting to the previous model if the model has been recently updated. Evaluation 144a may identify whether a retraining operation is necessary if the model no longer achieves the required level of performance.

[0045] In step 314, component 112 identifies correlations and relationships based on the query structure and runtime feedback. In various embodiments of the present invention, component 112 may, via the runtime engine 146, identify correlations and relationships of interest based on the query structure and runtime feedback.

[0046] In step 316, component 112 collects data from the query execution to identify errors in the query optimizer estimation. In various embodiments of the present invention, component 112 may also collect data from the query execution via optimizer component 148 to identify errors in the query optimizer estimation (i.e., optimizer component 148), and further submit query feedback and runtime feedback to machine learning engine 144 to update a potentially useful set of models.

[0047] In step 318, component 112 trains the generated model. In various embodiments of the present invention, component 112 may train one or more generated models via the machine learning engine 144. In various embodiments of the present invention, component 112 may, via the machine learning engine 144, initiate full initial training of a new model, initiate retraining of an existing model, or perform incremental updates of the model, depending on the evaluation performed in the evaluation step of Figure 2.

[0048] In step 320, component 112 outputs a new, updated, or retrained model. In various embodiments of the present invention, component 112 may output a new, updated, or retrained model via the machine learning engine 144 based on data collected from query execution to identify errors in the query optimizer estimation, submitted query feedback and runtime feedback, or one or more trained generated models, or any combination thereof.

[0049] Figure 4 shows the generally indicated operational steps of a component 112 communicating with a computing device 110 in the distributed data processing environment 100 of Figure 1 for automatic feedback and continuous learning for query optimization, according to an embodiment of the present invention. Figure 4 shows one implementation and does not imply any limitations with respect to the environment in which various embodiments may be implemented. Numerous modifications to the shown environment can be made by those skilled in the art without departing from the scope of the invention as enumerated by the claims.

[0050] In step 402, component 112 retrieves the actual number of rows for each relation in the query or operator in the execution plan. In various embodiments of the present invention, component 112 may retrieve the number of rows for one or more relations in the query or operator or both in the execution plan, and the actual number of rows may be predetermined for one or more relations in the query or operator or both in the execution plan.

[0051] In step 404, component 112 identifies cardinality estimates that are within a predetermined threshold. In various embodiments of the present invention, component 112 may determine and identify whether a cardinality estimate is within a predetermined threshold by comparing the number of acquired rows to the predicted cardinality.

[0052] In step 406, component 112 stores cardinality estimates in a separate table. In the embodiment shown, component 112 may store cardinality estimates that are above or below a certain threshold (e.g., within a certain predetermined threshold) in a separate compressed structure in memory. In various embodiments of the present invention, component 112 may hold only predicate literals and predicate information.

[0053] In step 408, component 112 adds the stored predicate literals to the materialized sample. In various embodiments of the present invention, component 112 may add one or more stored literals to the materialized sample or to a novel sample randomly collected from the original relationship.

[0054] In step 410, component 112 identifies the accumulation of errors. In various embodiments of the present invention, component 112 may identify when the model's predictions begin to accumulate errors. In various embodiments of the present invention, depending on the actual implementation of the proposed model, the collected points where prediction errors occurred may also be used to adjust the model parameters rather than completely retraining the model again. In various embodiments of the present invention, component 112 may collect column and multi-column statistics in automatic or manual mode to make them more robust to changes in the data by considering statistical correlations between multiple values. In various embodiments of the present invention, the proposed CFF model may be completely retrained when larger changes in the data occur and the model's predictions begin to accumulate larger errors.

[0055] In step 412, component 112 activates automatic feedback. In various embodiments of the present invention, component 112 may activate automatic feedback that triggers a model retraining action.

[0056] In step 414, component 112 measures the performance of the model. In various embodiments of the present invention, component 112 may measure the performance of one or more models against the actual cardinality collected from one or more individual components of the executed access plan.

[0057] In step 416, component 112 identifies which group of relationships in the columns has the potential to build a new model. In various embodiments of the present invention, if the optimizer knows that there is no pre-built model for a group of relationships or columns, component 112 may use observational feedback to determine and identify which group of relationships or columns has the potential to build a new model.

[0058] In step 418, component 112 stores a list of models in a database. In various embodiments of the present invention, component 112 may store a list of one or more models and a list of columns used to construct the models in a database table on local storage 104, shared storage 124, or both.

[0059] In step 420, component 112 tests the model. In various embodiments of the present invention, component 112 may test one or more new or retrained models, or fall back to a previous model after retraining, or both, in order to avoid database service calls to the database vendor as much as possible. Furthermore, in various embodiments, the model version and training timestamp are maintained to automatically revert to a previous model version, or as instructed by the database vendor, in case of potential problems.

[0060] In step 422, component 112 outputs a model. In various embodiments of the present invention, component 112 may output and run one or more models based on a list of saved models and tested models, or output and run a previously run model (i.e., fall back to a previous model), or both.

[0061] Figure 5 shows a block diagram of the components of the server computer 120 in the distributed data processing environment 100 of Figure 1, according to an embodiment of the present invention. Naturally, Figure 5 shows only one implementation and does not imply any limitations regarding the environment in which various embodiments may be implemented. Numerous modifications can be made to the shown environment.

[0062] Figure 5 shows a computer system 500, where server computing 120 represents an example of the computer system 500 including component 112. The computer system includes a processor 501, a cache 503, memory 502, persistent storage 505, a communication unit 507, one or more input / output (I / O) interfaces 506, a display 509, one or more external devices 508, and a communication fabric 504. The communication fabric 504 provides communication between the cache 503, memory 502, persistent storage 505, communication unit 507, and one or more input / output (I / O) interfaces 506. The communication fabric 504 can be implemented using any architecture designed to pass data or control information, or both, between processors (e.g., microprocessors, communications, and network processors), system memory, peripheral devices, and any other hardware components in the system. For example, the communication fabric 504 can be implemented using one or more buses or crossbar switches.

[0063] Memory 502 and persistent storage 505 are computer-readable storage media. In this embodiment, memory 502 includes random access memory (RAM). Generally, memory 502 may include any suitable volatile or non-volatile computer-readable storage media. Cache 503 is a high-speed memory that enhances the performance of processor 501 by holding recently accessed data and data close to recently accessed data from memory 502.

[0064] Program instructions and data used to implement embodiments of the present invention may be stored in persistent storage 505 and memory 502 so as to be executed by one or more of the individual processors 501 via cache 503. In some embodiments, persistent storage 505 includes a magnetic hard disk drive. Instead of, or in addition to, a magnetic hard disk drive, persistent storage 505 may include a solid-state hard drive, a semiconductor storage device, read-only memory (ROM), erasable programmable read-only memory (EPROM), flash memory, or any other computer-readable storage medium capable of storing program instructions or digital information.

[0065] Furthermore, the media used by persistent storage 505 may be removable. For example, a removable hard drive may be used for persistent storage 505. Other examples include optical and magnetic disks, thumb drives, and smart cards, which are inserted into the drive for transfer to another computer-readable storage medium, also part of persistent storage 505.

[0066] In these examples, the communication unit 507 provides communication with other data processing systems or devices. In these examples, the communication unit 507 includes one or more network interface cards. The communication unit 507 may provide communication using either or both physical communication links and / or wireless communication links. Program instructions and data used to implement embodiments of the present invention can be downloaded to persistent storage 505 through the communication unit 507.

[0067] The I / O interface(s) 506 enables the input and output of data to and from other devices that may be connected to each computer system. For example, the I / O interface(s) 506 may provide connection to an external device(s) 508 such as a keyboard, keypad, touchscreen, or any other suitable input device, or any combination thereof. Furthermore, the external device(s) 508 may include, for example, a thumb drive, a portable optical or magnetic disk, and a portable computer-readable storage medium such as a memory card. Software and data used to practice embodiments of the present invention may be stored on such portable computer-readable storage medium and may also be loaded onto persistent storage(s) 505 via the I / O interface(s) 506. The I / O interface(s) 506 also connects to a display(s) 509.

[0068] The display 509 provides a mechanism for displaying data to the user and may be, for example, a computer monitor.

[0069] The programs described in this specification are identified based on the applications in which the programs are implemented in particular embodiments of the present invention. However, naturally, any specific program term used in this specification is for convenience only, and therefore the present invention should not be limited solely to use in any specific application identified, implied, or both by such terminology.

[0070] The present invention may be a system, a method, a computer program product, or a combination thereof. The computer program product may include one or more computer-readable storage media having computer-readable program instructions that cause a processor to execute various aspects of the present invention.

[0071] A computer-readable storage medium can be any tangible device capable of holding and storing instructions used by an instruction execution device. Examples of computer-readable storage media include, but are not limited to, electronic storage devices, magnetic storage devices, optical storage devices, electromagnetic storage devices, semiconductor storage devices, or any suitable combination of the foregoing. A non-exclusive list of more specific examples of computer-readable storage media includes portable computer diskettes, hard disks, random-access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random-access memory (SRAM), compact disc read-only memory (CD-ROM), digital versatile disk (DVD), memory sticks, flexible disks, mechanically encoded devices such as punch cards or raised structures in grooves on which instructions are recorded, and any suitable combination of the foregoing. When used in this specification, computer-readable storage media shall not be construed as essentially transient signals such as radio waves or other freely propagating electromagnetic waves, electromagnetic waves propagating through waveguides or other transmission media (e.g., light pulses passing through optical fiber cables), or electrical signals transmitted through wires.

[0072] The computer-readable program instructions described in this specification may be downloaded from a computer-readable storage medium to individual computing / processing devices, or to an external computer or external storage device via a network such as the Internet, a local area network, a wide area network, or a wireless network, or any combination thereof. The network may include copper transmission cables, optical fiber, wireless transmission, routers, firewalls, switches, gateway computers, or edge servers, or any combination thereof. The network adapter card or network interface of each computing / processing device receives the computer-readable program instructions from the network and transfers the computer-readable program instructions for storage on a computer-readable storage medium within the individual computing / processing device.

[0073] The computer-readable program instructions that perform the operation of the present invention may be assembler instructions, instruction-set-architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state-setting data, or source code or object code written in any combination of one or more programming languages, including object-oriented programming languages ​​such as Smalltalk(R), C++, or similar, and conventional procedural programming languages ​​such as the C programming language or similar. The computer-readable program instructions may run entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter scenario, the remote computer may be connected to the user's computer via any type of network, including a local area network (LAN) or wide area network (WAN), or it may be connected to an external computer (for example, via the Internet using an Internet service provider). In some embodiments, to carry out each aspect of the present invention, an electronic circuit configuration, including, for example, a programmable logic circuit configuration, a field-programmable gate array (FPGA), or a programmable logic array (PLA), can execute computer-readable program instructions by utilizing state information of computer-readable program instructions to personalize the electronic circuit configuration.

[0074] Each aspect of the present invention is described in this specification with reference to flowcharts or block diagrams, or both, of methods, apparatus (systems), and computer program products according to embodiments of the present invention. Naturally, each block in a flowchart or block diagram, or both, and any combination of multiple blocks in a flowchart or block diagram, or both, can be implemented using computer-readable program instructions.

[0075] Such computer-readable program instructions may also be provided to the processor of a general-purpose computer, a dedicated computer, or other programmable data processing device to bring about a machine, so that the instructions are executed by the processor of the computer or other programmable data processing device to create means for implementing a function / operation specified in one or more blocks of a flowchart or block diagram, or both. Such computer-readable program instructions, which can instruct a computer, a programmable data processing device, or other device, or any combination thereof, to function in a particular way, may also be stored on a computer-readable storage medium so that the computer-readable storage medium on which the instructions are stored contains a product containing instructions that implement each aspect of a function / operation specified in one or more blocks of a flowchart or block diagram, or both.

[0076] Furthermore, computer-readable program instructions can be loaded into a computer, other programmable data processing device, or other device to result in a process implemented in the computer, such that a series of operational steps are performed on the computer, other programmable device, or other device, and instructions executed on the computer, other programmable device, or other device can implement a function / operation specified in one or more blocks of a flowchart or block diagram, or both.

[0077] The flowcharts and block diagrams in each drawing illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of the present invention. In this context, each block in a flowchart or block diagram may represent a module, segment, or portion of instructions containing one or more executable instructions that implement a specified logical function(s). In some alternative implementations, the functions shown within a block may occur in a different order than shown in the drawings. For example, depending on the relevant functionality, two consecutively shown blocks may actually be executed virtually simultaneously, or each block may be executed in reverse order. Furthermore, it should be noted that each block in a block diagram or flowchart, or both, and any combination of multiple blocks in a block diagram or flowchart, or both, can be implemented by a dedicated hardware-based system that performs a specified function or operation, or a combination of dedicated hardware and computer instructions.

[0078] The various embodiments of the present invention are provided for illustrative purposes only and are not intended to be comprehensive or limitful to the disclosed embodiments. A number of modifications and variations will be apparent to those skilled in the art without departing from the scope and intent of the invention. The terminology used in this specification has been chosen to best describe the principles of the embodiments, their practical applications, or the technical improvements over existing technologies on the market, or to enable those skilled in the art to understand the embodiments disclosed herein. One embodiment of the present invention is as follows: [Section 1] A computer-implemented method for query optimization, wherein the method is The process involves a workload feedback process, including query feedback performed during query compilation, to allow the query optimizer to identify opportunities for improvement in cardinality estimation. Based on the structure of the query feedback and the runtime feedback that is executed, the runtime engine identifies correlations and relationships. In order to identify errors in the estimation of the query optimizer, the query optimizer collects data from the execution of the query. The steps include: submitting the query feedback and runtime feedback to the machine learning engine via the query optimizer in order to update the set of models; The steps include updating the set of models using a machine learning engine based on the submitted query feedback and runtime feedback, A step of outputting a new, updated, or retrained model based on data collected from the execution of the query, the submitted query feedback and the runtime feedback, or the trained generated model, in order to identify the error in the estimation of the query optimizer. A method implemented in a computer, including [this]. [Section 2] The steps include generating a set of models using the machine learning engine with a set of relationships or correlations of columns of interest identified using a predetermined first criterion, A step of performing data discovery on received data and metadata using a discovery component, wherein the data discovery includes performing an analysis of the data and metadata to identify relationships and correlations between columns in a table. The data discovery process involves identifying a set of columns with relationships or correlations of interest across multiple tables in the database, and across multiple tables. The computer implementation described in Section 1, further including the method described in Section 1. [Section 3] A step of performing runtime feedback by the runtime engine, wherein the runtime engine collects data from the execution of the query to identify errors in the estimation of the query optimizer, and the points where prediction errors occurred are used to adjust one or more model parameters rather than retraining the entire model. The steps include: executing query feedback by the query optimizer during the compilation of the query; The computer implementation described in Section 1, further including the method described in Section 1. [Section 4] A step of training the set of models using a machine learning engine, wherein the machine learning engine may, depending on the evaluation, start full initial training of a new model, start retraining of an existing model, or perform incremental updates of a model. The computer implementation described in Section 1, further including the method described in Section 1. [Section 5] The steps include determining that the existing model will be extended with additional predetermined columns that would take more time to retrain, The steps include dividing the existing model into two or more models in order to reduce training time or model size, The computer implementation described in Section 1, further including the method described in Section 1. [Section 6] The training is, A step to identify candidate columns without querying the entire set of data through a given database interface. The computer implementation described in Section 1, including the method described in Section 1. [Section 7] A step of falling back to a previous model after retraining, wherein the model version and training timestamp are maintained in order to revert to a previous version of the current model. The computer implementation described in Section 1, further including the method described in Section 1. [Section 8] A computer-implemented method for query optimization, wherein the method is The steps include identifying a cardinality estimate within a predetermined threshold by comparing the number of obtained rows with the number of rows based on the predicted cardinality, The steps include adding one or more retained literals to a materialized sample or to a randomly collected new sample, A step of identifying when the model's predictions begin to accumulate errors, wherein the collected points where prediction errors occurred are used to adjust the model parameters rather than completely retraining the model, The process involves collecting column and multi-column statistics in automatic or manual mode to make the data more robust to changes by considering statistical correlations between multiple values, To trigger a retraining action in the aforementioned model, the steps include activating automatic feedback, A step of measuring the performance of the estimated cardinality of one or more models against the actual cardinality collected from one or more individual components of the executed access plan, A step of identifying a group of relations or columns that have the potential to build a new model, based on observational feedback from the measured performance, wherein the observational feedback is used to identify which group of relations or columns has the potential to build the new model when the optimizer identifies that there is no pre-built model for the group of relations or columns. Steps to test a newly built or retrained model, A step of running a model based on a list of saved models and the tested model, A method implemented in a computer, including [this]. [Section 9] The aforementioned method, Steps to obtain the number of rows for one or more relations in a query or operator within the execution plan, A step of identifying a cardinality estimate within a predetermined threshold, The step of identifying the cardinality estimate within the predetermined threshold further includes: Steps to compare the obtained rows with one or more of the predicted cardinalities. The computer implementation methods described in Section 8, including the method described in Section 8. [Section 10] The steps include storing the cardinality estimate, which is above or below a certain threshold, in a separate compression structure in memory, The steps include outputting one or more models based on the list of saved models and the tested models, The computer implementation described in Section 8, further including the method described in Section 8. [Section 11] A step of falling back to a previous model after retraining the aforementioned model, wherein the model version and training timestamp are maintained in order to revert to the previous version of the current model. The computer implementation described in Section 8, further including the method described in Section 8. [Section 12] The steps include determining that the existing model will be extended with additional predetermined columns that would take more time to retrain, The steps include dividing the existing model into two or more models in order to reduce training time or model size, The computer implementation described in Section 8, further including the method described in Section 8. [Section 13] A computer program product for query optimization, wherein the computer program product is One or more computer-readable storage devices and program instructions stored on said one or more computer-readable storage devices The stored program instructions include, A program instruction that uses a workload feedback process, including query feedback executed during query compilation, to allow the query optimizer to identify opportunities for improvement in cardinality estimation, A program instruction that, based on the structure of the query feedback and the runtime feedback that is executed, allows the runtime engine to identify correlations and relationships, In order to identify errors in the estimation of the query optimizer, a program instruction is provided for the query optimizer to collect data from the execution of the query, A program instruction to update the set of models, which submits the query feedback and runtime feedback to the machine learning engine via the query optimizer, A program instruction for updating the set of models by the machine learning engine based on the submitted query feedback and runtime feedback, A program instruction that outputs a new, updated, or retrained model based on data collected from the execution of the query, submitted query feedback and runtime feedback, or a trained generated model, in order to identify the error in the estimation of the query optimizer, Computer program products, including [this]. [Section 14] A program instruction that performs runtime feedback by the runtime engine, wherein the runtime engine collects data from the execution of the query to identify errors in the estimation of the query optimizer, and the points where prediction errors occurred are used to adjust one or more model parameters rather than retraining the entire model; A program instruction that executes query feedback by the query optimizer during the compilation of the aforementioned query, The computer program products described in item 13, further including the following. [Section 15] A program instruction that determines that an existing model will be extended with additional predetermined columns that would take more time to retrain, Program instructions to divide the existing model into two or more models in order to reduce training time or model size, The computer program products described in item 13, further including the following. [Section 16] A program instruction in a database service that falls back to a previous model after retraining, wherein the model version and training timestamp are maintained in order to revert to the previous version of the current model. The computer program products described in item 13, further including the following. [Section 17] A computer system for query optimization, wherein the computer system is One or more computer processors, One or more computer-readable storage devices, Program instructions stored on one or more computer-readable storage devices for execution by at least one of the one or more computer processors The stored program instructions include, A program instruction that uses a workload feedback process, including query feedback executed during query compilation, to allow the query optimizer to identify opportunities for improvement in cardinality estimation, A program instruction that, based on the structure of the query feedback and the runtime feedback that is executed, allows the runtime engine to identify correlations and relationships, In order to identify errors in the estimation of the query optimizer, a program instruction is provided for the query optimizer to collect data from the execution of the query, A program instruction to update the set of models, which submits the query feedback and runtime feedback to the machine learning engine via the query optimizer, A program instruction for updating the set of models by the machine learning engine based on the submitted query feedback and runtime feedback, A program instruction that outputs a new, updated, or retrained model based on data collected from the execution of the query, submitted query feedback and runtime feedback, or a trained generated model, in order to identify the error in the estimation of the query optimizer, Computer systems, including [specific components / features]. [Section 18] A program instruction for the machine learning engine to generate a set of models using a set of relationships or correlations of interest identified using a predetermined first criterion, A program instruction for performing data discovery on received data and metadata using a discovery component, wherein the data discovery includes performing an analysis of the data and metadata to identify relationships and correlations between tables. A program instruction that uses a data discovery process to identify a set of relationships or correlations of interest within and across multiple tables in a database. The computer systems described in paragraph 17, further including the following: [Section 19] A program instruction that performs runtime feedback by the runtime engine, wherein the runtime engine collects data from the execution of the query to identify errors in the estimation of the query optimizer, and the points where prediction errors occurred are used to adjust one or more model parameters rather than retraining the entire model; A program instruction that executes query feedback by the query optimizer during the compilation of the aforementioned query, The computer systems described in paragraph 17, further including the following: [Section 20] A program instruction that determines that an existing model will be extended with additional predetermined columns that would take more time to retrain, Program instructions to divide the existing model into two or more models in order to reduce training time or model size, The computer systems described in paragraph 17, further including the following: [Section 21] A program instruction in a database service that falls back to a previous model after retraining, wherein the model version and training timestamp are maintained in order to revert to the previous version of the current model. The computer systems described in paragraph 17, further including the following: [Section 22] A computer system for query optimization, wherein the computer system is One or more computer processors, One or more computer-readable storage devices, Program instructions stored on one or more computer-readable storage devices for execution by at least one of the one or more computer processors The stored program instructions include, A program instruction that identifies a cardinality estimate within a predetermined threshold by comparing the number of retrieved rows with the number of rows based on the predicted cardinality of one or more rows, Program instructions for adding one or more retained literals to a materialized sample or to a newly collected random sample, A program instruction that identifies when a model's predictions begin to accumulate errors, wherein collected points where prediction errors occurred are used to adjust model parameters rather than completely retraining the model, Program instructions for collecting column and multi-column statistics in automatic or manual mode to make the data more robust to changes by considering statistical correlations between multiple values, A program instruction to activate automatic feedback in order to trigger a retraining action in the aforementioned model, A program instruction that measures the performance of the estimated cardinality of one or more models against the actual cardinality collected from one or more individual components of the executed access plan, A program instruction for identifying a group of relations or columns that have the potential to build a new model, based on observational feedback from the measured performance, wherein the observational feedback is used to identify which group of relations or columns has the potential to build the new model when the optimizer identifies that there is no pre-built model for the relations or group of columns; Program instructions for testing newly constructed or retrained models, A list of saved models and program instructions to execute a model based on the tested model, Computer systems, including [specific components / features]. [Section 23] The aforementioned computer system, To get the number of rows for one or more relations in a query or operator within the execution plan, Identifying a cardinality estimate within a predetermined threshold, wherein identifying the cardinality estimate within the predetermined threshold is Comparing the number of one or more rows obtained to the predicted cardinality. Identifying the cardinality estimate within the predetermined threshold, including, The cardinality estimates, which are above or below a certain threshold, are stored in a separate compression structure within memory. Outputting one or more models based on the saved list of models and the tested models, The computer systems described in paragraph 22, further including the following: [Section 24] The aforementioned computer system, The fallback involves falling back to a previous model after the aforementioned retraining of the aforementioned model, wherein the model version and training timestamp are maintained in order to revert to the previous version of the current model. The computer systems described in paragraph 22, further including the following: [Section 25] The aforementioned computer system, The decision to extend an existing model with additional predetermined columns, which would require more time for retraining, In order to reduce training time or model size, the existing model may be split into two or more models, The computer systems described in paragraph 22, further including the following:

Claims

1. A computer-implemented method for query optimization, the method being performed using a workload feedback process, the workload feedback process including query feedback and runtime feedback, the query feedback being performed by a query optimizer during query compilation, the runtime feedback being performed by a runtime engine, and the runtime engine collecting data from query execution. The method uses a workload feedback process, including query feedback performed during the compilation of the query, in response to the query optimizer identifying opportunities for improvement in cardinality estimation. Based on the query optimizer feedback structure included in the query feedback and the runtime feedback to be executed, the runtime engine identifies the correlations and relationships between columns of interest in a table in the database. In order to identify errors in the cardinality estimation by the query optimizer, the query optimizer collects data from the execution of the query. A step of submitting query feedback and runtime feedback to a machine learning engine by the query optimizer in order to update the set of models available during the compilation and optimization of the query by identifying the correlations and relationships between the columns of interest, wherein the set of models in the machine learning engine is built or updated using the identified correlations and relationships between the columns of interest, The steps include updating the set of models using a machine learning engine based on the submitted query feedback and runtime feedback, A step of outputting a new model or an updated model, i.e., a retrained model, based on data collected from the execution of the query, the submitted query feedback and the runtime feedback, or based on the updated set of models, in order to identify the error in the cardinality estimation by the query optimizer. During the database service, a step of falling back to a previous model after retraining the model, wherein the model version and training timestamp are maintained in order to revert to the previous version of the current model; A method implemented in a computer, including [this].

2. The steps include generating a set of models using the machine learning engine with a set of relationships or correlations between the columns of interest identified using a predetermined first criterion, A step of performing data discovery using a discovery component on the collected data and metadata, i.e., a catalog, using a predetermined second criterion, wherein the data discovery includes performing an analysis of the data and metadata to identify relationships and correlations between the columns, The data discovery process involves identifying relationships or correlations between columns of interest in multiple tables within a database, or sets of columns that have relationships or correlations between columns of interest across multiple tables within a database. A computer-implemented method according to claim 1, further comprising:

3. A step of performing runtime feedback by the runtime engine, wherein the runtime engine collects data from the execution of the query to identify errors in the cardinality estimation by the query optimizer, and the points where prediction errors occurred are used to adjust one or more model parameters rather than retraining the entire model; The steps include: executing query feedback by the query optimizer during the compilation of the aforementioned query; A computer-implemented method according to claim 1, further comprising:

4. A step of training the set of models using a machine learning engine, wherein the machine learning engine may, depending on the evaluation, start full initial training of a new model, start retraining of an existing model, or perform incremental updates of a model. A computer-implemented method according to claim 1, further comprising:

5. The steps include determining that the existing model will be extended with additional predetermined columns that would take time to retrain, The steps include dividing the existing model into two or more models in order to reduce training time or model size, A computer-implemented method according to claim 1, further comprising:

6. The training is, A step to identify candidate columns without querying the entire set of data through a given database interface. A computer-implemented method according to claim 1, including the following:

7. A computer program for causing a computer to perform the method described in any one of claims 1 to 6.

8. A computer-readable storage medium recording the computer program described in claim 7.

9. A computer system for query optimization, wherein the computer system is One or more computer processors, One or more computer-readable storage devices, Program instructions stored on one or more computer-readable storage devices for execution by at least one of the one or more computer processors The stored program instructions include, and the workload feedback process includes query feedback and runtime feedback, the query feedback is performed by the query optimizer during query compilation, and the runtime feedback is performed by the runtime engine, and the runtime engine collects data from query execution. The saved program instructions, using a workload feedback process that includes query feedback executed during the compilation of the query, are used in response to the query optimizer identifying opportunities for improvement in cardinality estimation. A program instruction that, based on the query optimizer feedback structure included in the query feedback and the runtime feedback that is executed, allows the runtime engine to identify the correlations and relationships between columns of interest in a table in the database, A program instruction for the query optimizer to collect data from the execution of a query in order to identify errors in the cardinality estimation by the query optimizer, A program instruction submitted by the query optimizer to a machine learning engine, wherein the set of models in the machine learning engine is built or updated using the identified correlations and relationships between the columns of interest, in order to update the set of models available during the compilation and optimization of the query by identifying the correlations and relationships between the columns of interest, the submitted program instruction, A program instruction for updating the set of models by the machine learning engine based on the submitted query feedback and runtime feedback, A program instruction to output a new model or an updated model, i.e., a retrained model, based on data collected from the execution of the query, the submitted query feedback and runtime feedback, or the updated set of models, in order to identify the error in the cardinality estimation by the query optimizer, During a database service, a program instruction to fall back to a previous model after retraining the model, wherein the model version and training timestamp are maintained in order to revert to the previous version of the current model. A computer system that includes [a certain feature].

10. A program instruction for the machine learning engine to generate a set of models using a set of relationships or correlations between the columns of interest identified using a predetermined first criterion, A program instruction for performing data discovery by a discovery component on the collected data and metadata, i.e., a catalog, using a predetermined second criterion, wherein the data discovery includes performing an analysis of the data and metadata to identify relationships and correlations between the columns, A program instruction that uses a data discovery process to identify relationships or correlations between columns of interest in multiple tables within a database, or sets of columns that have relationships or correlations between columns of interest across multiple tables within a database, The computer system according to claim 9, further comprising:

11. A program instruction that performs runtime feedback by the runtime engine, wherein the runtime engine collects data from the execution of the query to identify errors in the cardinality estimation by the query optimizer, and the points where prediction errors occurred are used to adjust one or more model parameters rather than retraining the entire model; A program instruction that executes query feedback by the query optimizer during the compilation of the aforementioned query, The computer system according to claim 9, further comprising:

12. A program instruction for training the set of models using a machine learning engine, wherein the machine learning engine may, depending on the evaluation, initiate full initial training of a new model, initiate retraining of an existing model, or perform incremental updates of a model. The computer system according to claim 9, further comprising:

13. A program instruction that determines that an existing model will be extended with additional predetermined columns that would take time to retrain, A program instruction to divide the existing model into two or more models in order to reduce training time or model size, The computer system according to claim 9, further comprising:

14. The training is, Identifying candidate columns through a given database interface without querying the entire given set of data. The computer system according to claim 9, including the following:

Images