International Business Machines Corporation

Vincent Corvinelli

Calisto Paul Zuzarte

John Frederick Hornibrook

Ziting Yu

Mingbin Xu

Huaxin Liu

Bingjie Miao

Mohamad F Kalil

Mohammed Fahd Alhamid

Tam Minh Dai Tran

Brandon Lewis Frendo

Seyed Mohammad Amin Kamali

Ning Wang

Yuri Deigin

Vasiliki Kantere

Diana Koval

Vinith Suriyakumar

Petrus Chan

Calisto Zuzarte

Mustafa Dawoud

Ronghao Yang

Wai Keat Tan

Joel Raymond Scarfone

Aspects of the disclosure include hybrid cost model-based techniques for evaluating query execution plans. A non-limiting example method includes inputting, to a plurality of base cost models including one or more learned cost models and a classic cost model, a query and a search space including a plurality of candidate query execution plans. Each base cost model outputs a predicted execution time or cost for each plan of the plurality of candidate query execution plans and a real execution time for each plan is determined. The method includes generating a training label including the query and a model of the base cost models having a highest correlation between the predicted and real execution times and training a query classifier on training data including the training label to predict which base cost model of the plurality of base cost models is a most suitable cost model for planning a given query.

Hybrid cost model for evaluating query execution plans

A computer implemented method for processing database queries includes receiving a query and a set of runtime metrics corresponding to the query, wherein the query includes a set of elements, generating a set of encoded elements corresponding to the set of elements, processing the set of encoded elements and the set of runtime metrics to identify one or more possibly query classifications, determining a query execution plan according to the identified one or more possible query classifications, and executing the query according to the determined query execution plan. The computer implemented method may additionally include providing one or more runtime metrics corresponding to the executed query. A computer program product and a computer system corresponding to the method are also disclosed.

Adaptive query optimization using machine learning

Aspects of the invention include techniques for robust query execution plan selection. A non-limiting example method includes training a model to estimate, for an input including a query and one or more plans, an execution time for each plan and a respective uncertainty of the execution time. A new query and a search space including a plurality of candidate plans are input to the model. Given an estimated distribution for the execution time of each candidate plan, a suboptimality risk is computed for each candidate plan. A plan of the plurality of candidate plans is selected according to a plan selection policy. The plan selection policy includes at least one of: selecting a plan by assuming that plans have higher costs proportional to an estimated standard deviation of their respective uncertainty; and selecting a plan with minimum risk using model uncertainty, data uncertainty, or a total uncertainty.

Robust query execution plan selection using machine learning with predictive uncertainties

Aspects of the invention include techniques for providing a learned join cardinality estimation using a join graph representation. A non-limiting example method includes building a join cardinality estimation model. The model can be built by generating a training query having a known join cardinality, generating an adjacency matrix encoding a join graph of the training query, encoding one side of a diagonal axis of the adjacency matrix, and training the join cardinality estimation model using the encoded adjacency matrix and the known join cardinality. The method includes performing an inference using the join cardinality estimation model. The inference includes a predicted join cardinality for a query. The method includes executing a query execution plan for the query using the predicted join cardinality.

Learned join cardinality estimation using a join graph representation

Examples described herein provide a computer-implemented method that includes training a machine learning model. The model is trained by generating a set of training queries using at least one of a query workload and relationships between tables in a database, building a query graph for each of the set of training queries, computing, for each training query of the set of training queries, a selectivity based at least in part on the query graph, and building, based at least in part on the set of training queries, an initial join result distribution as a collection of query graphs.

Automated query selectivity predictions using query graphs

A method, a computer system, and a computer program product for cardinality estimation is provided. Embodiments of the present invention includes accessing database relations. The database relations are utilized to collect a random sample from each of the database relations. Training data is then generated from the random sample. The training data is used to build a cumulative frequency function (CFF) model. The cumulative frequency function (CFF) model then provides a cardinality estimation for an output for SQL operators.

Automated query predicate selectivity prediction using machine learning models

In an approach to improve query optimization in a database management system, embodiments identify opportunities for improvement in a cardinality estimate using a workload feedback process using a query feedback performed during query compilation. Embodiments identify correlations and relationships based on the structure of the query feedback and the runtime feedback performed, and collects data from the execution of a query to identify errors in estimates of the query optimizer. Further, embodiments submit the query feedback and the runtime feedback to a machine learning engine to update a set of models. Additionally, embodiments update a set of models based on the submitted query feedback and runtime feedback, and output a new, updated, or re-trained model based on collected data from the execution of the query to identify the errors in estimates of the query optimizer, the submitted query feedback and the runtime feedback, or a trained generated mode.

Automated feedback and continuous learning for query optimization

A maintenance subsystem of a database-management system (DBMS) receives a database query that requests access to data stored in a database column. The subsystem retrieves or infers frequent-value statistics for that column, each of which specifies the number of times one distinct value is stored in the column. The statistics are partitioned into Keep and Discard clusters and, using statistical or other computational methods based on the column's data distribution, the subsystem determines an optimal number of the statistics that should be kept by the DBMS in order to minimize cost, errors, or other parameters desired by an implementer. The subsystem then directly or indirectly directs a query-optimizer component of the DBMS to consider the optimal number of frequent-value statistics when selecting an optimal data-access plan. The selected plan is then used by the DBMS's storage-manager component to access the column when servicing the received query.

Automated optimization of number-of-frequent-value database statistics

A database query comprising predicates may be received. Each predicate may operate on database columns. The database query may be determined to comprise strict operators. An upper bound neural network may be defined for calculating an adjacent upper bound and a lower bound neural network may be defined for calculating an adjacent lower bound. The upper bound neural network and the lower bound neural network may be trained using a selected value from data of a database table associated with the database query to be executed through the upper bound neural network and the lower bound neural network. The upper bound neural network and the lower bound neural network may be adjusted by passing in an expected value using an error found in expressions. The adjacent lower bound and the adjacent upper bound may be calculated in response to completion of initial training for the database columns.

Estimating cardinality selectivity utilizing artificial neural networks

In an approach for generating a selectivity estimation, one or more processors generate an artificial neural network and receive a DBMS query comprising one or more predicates. One or more processors replace one or more predicates in the one or more predicates that have strict operators with one or more predicates that have non-strict operators. One or more processors generate a selectivity function from the one or more predicates that has one or more arguments that are each comprised of an upper bound and a lower bound for a value in a predicate. One or more processors generate a training data set from a data distribution in the database and train the artificial neural network on the training data set to compute the selectivity function. One or more processors generate a selectivity estimation with the artificial neural network for one or more predicates in the DBMS query.

Cardinality estimation using artificial neural networks

Selectivity estimation using artificial neural networks

In accordance with aspects of the present invention, provided are systems and computer program products for incrementally estimating the cardinality of a derived relation including statistically correlated partially applicable predicates for a range-partitioned table. During the generation of a QEP a cardinality estimate is calculated in which one or more partially applicable predicates is correlated to another partially applicable predicate and/or to one or more fully applicable predicates. The cardinality includes a number of rows expected to be returned by the QEP and is computed in an incremental fashion for each operator of the QEP.

Systems and computer program product for cost estimation using partially applied predicates

In accordance with aspects of the present invention, provided are methods for incrementally estimating the cardinality of a derived relation including statistically correlated partially applicable predicates for a range-partitioned table. During the generation of a QEP a cardinality estimate is calculated in which one or more partially applicable predicates is correlated to another partially applicable predicate and/or to one or more fully applicable predicates. The cardinality includes a number of rows expected to be returned by the QEP and is computed in an incremental fashion for each operator of the QEP.

Methods of cost estimation using partially applied predicates

A system for estimating cardinalities for a plurality of columns in a database system is disclosed. The system include obtaining statistics collected for the plurality of columns. A first portion of the statistics indicates at least one relationship between at least a portion of the plurality of columns, while a second portion of the statistics includes single column statistics. The system also include utilizing the first portion and the second portion of the statistics to estimate the cardinality for the plurality of columns.

Growing community of inventors

Mississauga, Canada

Vincent Corvinelli