Automatic Query Selectivity Prediction Using Query Graphs

Abstract

Examples described herein provide a computer-implemented method that includes training a machine learning model, wherein 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, constructing a query graph for each of the set of training queries, calculating a selectivity for each training query in the set of training queries based at least in part on the query graph, and constructing an initial join result distribution as a collection of query graphs based at least in part on the set of training queries.

Description

[Technical Field]

[0001] FIELD OF THE INVENTION The embodiments described herein relate generally to processing systems, and more particularly to automatic query selectivity prediction using query graphs.

[0002] Query optimization is an efficient feature in executing queries by creating management systems in relational databases.

[0003] Structured query language (SQL) is a programming language created and designed to manage data stored in relational databases. Structured query language (SQL) may include the specific programming language used to manage data stored in a relational database management system (RDBMS).

[0004] A relational database management system (RDBMS) may typically include single-column statistics, typically collected on individual columns in a relation. A relation may include tuples or attributes that describe relationships or defining characteristics in tables, or relationships between tables. For example, a relation may include data values ​​in a table, and a relational database may store data values ​​as relations or tables. A collection of relations or tables may be stored in a database as a relational model. Summary of the Invention

[0005] In one exemplary embodiment, a computer-implemented method is provided 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, constructing a query graph for each of the set of training queries, calculating a selectivity for each training query in the set of training queries based at least in part on the query graph, and constructing an initial join result distribution as a collection of query graphs based at least in part on the set of training queries.

[0006] In another exemplary embodiment, a computer-implemented method is provided. The method 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, constructing a query graph for each of the set of training queries, calculating a selectivity for each training query in the set of training queries based at least in part on the query graph, and constructing an initial join result distribution as a collection of query graphs based at least in part on the set of training queries. The method further includes, following training the machine learning model, performing inference using the machine learning model. Performing inference includes calculating a join cardinality for each join statement of a query and identifying a best fit among the collection of query graphs to generate an associated cardinality estimate. The method further includes performing continuous training on the machine learning model. Performing continuous training includes compiling new queries in a new query workload using input including at least one change selected from the group consisting of a workload, a new table, and a new query based on changes to data, and updating the collection of query graphs.

[0007] Other embodiments described herein implement features of the above methods in computer systems and computer program products.

[0008] The above and other features and advantages of the present disclosure will become readily apparent from the following detailed description when considered in conjunction with the accompanying drawings. [Brief explanation of the drawings]

[0009] The details of the exclusive rights set forth herein are particularly pointed out and distinctly claimed in the claims at the conclusion of the specification. The foregoing and other features and advantages of embodiments of the present invention will become apparent from the following detailed description taken in conjunction with the accompanying drawings.

[0010] [Figure 1] 1 illustrates a block diagram of a processing system for implementing one or more embodiments described herein.

[0011] [Figure 2] FIG. 1 illustrates a block diagram of components of a machine learning training and inference system in accordance with one or more embodiments described herein.

[0012] [Figure 3] 1 illustrates a method for estimating the cardinality of a query using a query graph in accordance with one or more embodiments described herein.

[0013] [Figure 4] 1 illustrates a query graph in accordance with one or more embodiments described herein.

[0014] [Figure 5] 1 illustrates a spatial graph showing two tables with their join columns in accordance with one or more embodiments described herein.

[0015] [Figure 6] We show the characteristics of each node, which represent the initial coordinates in the graph space.

[0016] [Figure 7] 10 illustrates a resulting query graph with optimized node positioning for a three table query in accordance with one or more embodiments described herein.

[0017] [Figure 8] 10 illustrates a resulting query graph with updated positions after optimization in accordance with one or more embodiments described herein.

[0018] [Figure 9-1] 9A and 9B together illustrate an example of constructing a query graph for a given query in accordance with one or more embodiments described herein. [Figure 9-2] FIG. 9C shows together an example of constructing a query graph for a given query in accordance with one or more embodiments described herein.

[0019] [Figure 10] 1 illustrates a flow diagram of a method for training a machine learning model to estimate the cardinality of a query using a query graph in accordance with one or more embodiments described herein.

[0020] The diagrams shown herein are exemplary. There may be many variations on the diagrams or operations described therein without departing from the scope of the invention. For example, actions may be performed in a different order, or actions may be added, deleted, or modified. Also, the term "coupled" and variations thereof describe the existence of a communication path between two elements, and do not imply a direct connection between the elements where there are no intervening elements / connections between them. All of these variations are considered part of the specification. DETAILED DESCRIPTION OF THE INVENTION

[0021] One or more embodiments described herein provide for estimating the cardinality of a query using a query graph.

[0022] Various aspects of the present disclosure are described through text, flowcharts, block diagrams of computer systems, and / or block diagrams of machine logic included in embodiments of a computer program product (CPP). For any flowchart, depending on the technology involved, operations may be performed in an order different from that shown in a given flowchart. For example, again depending on the technology involved, two operations shown in successive flowchart blocks may be performed in the reverse order, as a single integrated step, simultaneously, or in an at least partially overlapping manner.

[0023] A computer program product embodiment (“CPP embodiment” or “CPP”) is a term used in this disclosure to describe any set of one or more storage media (also referred to as “media”) collectively included in one or more storage devices that collectively contain machine-readable code corresponding to instructions and / or data for performing the computer operations specified in a given CPP claim. A “storage device” is any tangible device that can hold and store instructions for use by a computer processor. The computer-readable storage medium may be, but is not limited to, an electronic storage medium, a magnetic storage medium, an optical storage medium, an electromagnetic storage medium, a semiconductor storage medium, a mechanical storage medium, or any suitable combination of the foregoing. Some known types of storage devices that include these media include 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 disk read-only memory (CD-ROM), digital versatile disk (DVD), memory stick, floppy disk, mechanically encoded device (such as pits / lands formed on the major surface of a punch card or disk), or any suitable combination of the foregoing. Computer-readable storage media, as the term is used in this disclosure, is not to be construed as storage in the form of a transitory signal per se, such as radio waves or other freely propagating electromagnetic waves, electromagnetic waves propagating through a waveguide, light pulses passing through fiber optic cables, electrical signals communicated through wires, and / or other transmission media. As will be appreciated by those skilled in the art, data is typically moved at some infrequent time during the normal operation of a storage device, such as during access, defragmentation, or garbage collection, but the foregoing does not qualify a storage device as transitory because the data is not transitory while it is stored.

[0024] Computing environment 100 includes an example environment for execution of at least some of the computer code involved in performing the inventive methods, such as estimating query cardinality using query graph 150. In addition to block 150, computing environment 100 includes, for example, computer 101, wide area network (WAN) 102, end user device (EUD) 103, remote server 104, public cloud 105, and private cloud 106. In this embodiment, computer 101 has a processor set 110 (including processing circuitry 120 and cache 121), a communications fabric 111, volatile memory 112, persistent storage 113 (including operating system 122 and block 150, as identified above), a peripheral device set 114 (including a user interface (UI), a device set 123, storage 124, and an Internet of Things (IoT) sensor set 125), and a network module 115. Remote server 104 includes a remote database 130. The public cloud 105 includes a gateway 140, a cloud orchestration module 141, a set of host physical machines 142, a set of virtual machines 143, and a set of containers 144.

[0025] Computer 101 may take the form of a desktop computer, a laptop computer, a tablet computer, a smartphone, a smartwatch or other wearable computer, a mainframe computer, a quantum computer, or any other form of computer or mobile device now known or later developed that is capable of executing programs, accessing a network, or querying a database, such as remote database 130. As is well understood in the field of computer technology, and depending on the technology, execution of a computer-implemented method may be distributed among multiple computers and / or among multiple locations. However, in this description of computing environment 100, for purposes of brevity, the detailed discussion focuses on a single computer, specifically computer 101. While computer 101 is not shown in FIG. 1 within the cloud, it may be located within the cloud. However, computer 101 is not required to reside within the cloud except to any extent that may be expressly indicated.

[0026] Processor set 110 includes one or more computer processors of any type now known or later developed. Processing circuitry 120 may be distributed across multiple packages, e.g., multiple linked integrated circuit chips. Processing circuitry 120 may implement multiple processor threads and / or multiple processor cores. Cache 121 is memory located within the processor chip package and is typically used for data or code that should be available for fast access by threads or cores executing on processor set 110. Cache memory is typically organized into multiple levels depending on relative proximity to the processing circuitry. Alternatively, some or all caches for a processor set may be located “off-chip.” In some computing environments, processor set 110 may be designed to operate with qubits and perform quantum computing.

[0027] Computer-readable program instructions are typically loaded onto computer 101 and cause processor set 110 of computer 101 to perform a series of operational steps, thereby implementing a computer-implemented method. As a result, the instructions so executed instantiate the method set forth in the flowcharts and / or descriptions of the computer-implemented method (collectively, the "methods of the present invention") contained herein. These computer-readable program instructions are stored in various types of computer-readable storage media, such as cache 121 and other storage media discussed below. The program instructions and associated data are accessed by processor set 110 to control and direct the execution of the methods of the present invention. In computing environment 100, at least some of the instructions for executing the methods of the present invention may be stored in block 150 within persistent storage 113.

[0028] Communications fabric 111 is the signal-conducting pathway that allows various components of computer 101 to communicate with one another. Typically, this fabric is made up of switches and conductive pathways, such as switches and conductive pathways that make up buses, bridges, physical input / output ports, etc. Other types of signal communication pathways may be used, such as fiber optic and / or wireless communication pathways.

[0029] Volatile memory 112 may be any type of volatile memory now known or later developed. Examples include dynamic random access memory (RAM) or static RAM. Typically, volatile memory is characterized by random access, although this is not required unless expressly indicated. In computer 101, volatile memory 112 is located in a single package and is internal to computer 101; however, alternatively or additionally, volatile memory may be distributed across multiple packages and / or located external to computer 101.

[0030] Persistent storage 113 is any form of non-volatile storage for a computer, now known or later developed. The non-volatility of this storage means that stored data remains regardless of whether power is supplied to computer 101 and / or directly to persistent storage 113. While persistent storage 113 may be read-only memory (ROM), typically at least a portion of persistent storage allows data to be written, data to be deleted, and data to be rewritten. Some well-known forms of persistent storage include magnetic disks and solid-state storage devices. Operating system 122 may take several forms, such as various known proprietary operating systems or open-source Portable Operating System Interface-type operating systems that utilize a kernel. The code contained in block 150 typically includes at least some of the computer code involved in performing the methods of the present invention.

[0031] Peripheral device set 114 includes a set of peripheral devices of computer 101. Data communication connections between peripheral devices and other components of computer 101 may be implemented in various ways, such as Bluetooth® connections, near field communication (NFC) connections, connections formed by cables (such as universal serial bus (USB)-type cables), insertion-type connections (e.g., Secure Digital (SD) cards), connections formed through local area communication networks, and even connections formed through wide area networks such as the Internet. In various embodiments, UI device set 123 may include components such as display screens, speakers, microphones, wearable devices (such as goggles and smartwatches), keyboards, mice, printers, touchpads, game controllers, and haptic devices. Storage 124 may be external storage, such as an external hard drive, or insertable storage, such as an SD card. Storage 124 may be persistent and / or volatile. In some embodiments, storage 124 may take the form of a quantum computing storage device for storing data in the form of qubits. In embodiments where computer 101 is required to have a large amount of storage (e.g., where computer 101 stores and manages large databases locally), this storage may be provided by peripheral storage devices designed to store very large amounts of data, such as a storage area network (SAN) shared by multiple, geographically distributed computers. IoT sensor set 125 consists of sensors that may be used in Internet of Things applications. For example, one sensor may be a thermometer and another sensor may be a motion detector.

[0032] Network module 115 is a collection of computer software, hardware, and firmware that enables computer 101 to interact with other computers over WAN 102. Network module 115 may include hardware such as a modem or Wi-Fi® signal transceiver, software for packetizing and / or depacketizing data for communication network transmission, and / or web browser software for communicating data over the Internet. In some embodiments, the network control and network forwarding functions of network module 115 are performed on the same physical hardware device. In other embodiments (e.g., embodiments utilizing Software-Defined Networking (SDN)), the control and forwarding functions of network module 115 are performed on physically separate devices, such that the control function manages several different network hardware devices. Computer-readable program instructions for implementing the methods of the present invention may be downloaded to computer 101 from an external computer or external storage device, typically through a network adapter card or network interface included in network module 115.

[0033] WAN 102 is any wide area network (e.g., the Internet) capable of communicating computer data over non-local distances using any technology for communicating computer data now known or later developed. In some embodiments, a WAN may be replaced and / or supplemented by a local area network (LAN) designed to communicate data between devices located in a local area, such as a Wi-Fi® network. WANs and / or LANs typically include copper transmission cables, optical fiber transmissions, wireless transmissions, and computer hardware such as routers, firewalls, switches, gateway computers, and edge servers.

[0034] End-user device (EUD) 103 is any computer system used and controlled by an end user (e.g., a customer of the enterprise operating computer 101) and may take any of the forms described above in connection with computer 101. EUD 103 typically receives useful and useful data from the operation of computer 101. For example, in the hypothetical case where computer 101 is designed to provide recommendations to the end user, the recommendations would typically be communicated from network module 115 of computer 101 over WAN 102 to EUD 103. In this manner, EUD 103 can display or otherwise present the recommendations to the end user. In some embodiments, EUD 103 may be a client device such as a thin client, a heavy client, a mainframe computer, a desktop computer, and the like.

[0035] Remote server 104 is any computer system that provides at least some data and / or functionality to computer 101. Remote server 104 may be controlled and used by the same entity that operates computer 101. Remote server 104 represents a machine that collects and stores useful and useful data for use by other computers, such as computer 101. For example, in the hypothetical case where computer 101 is designed and programmed to provide recommendations based on historical data, then this historical data may be provided to computer 101 from remote database 130 of remote server 104.

[0036] A public cloud 105 is any computer system available for use by multiple entities that provides on-demand availability of computer system resources and / or other computer capabilities, particularly data storage (cloud storage) and computing capacity, without requiring direct, active management by users. Cloud computing typically leverages resource sharing to achieve coherence and economies of scale. Direct, active management of the computing resources of the public cloud 105 is performed by computer hardware and / or software in a cloud orchestration module 141. The computing resources provided by the public cloud 105 are typically implemented by virtual computing environments running on various computers comprising a host physical machine set 142, which is the universe of physical computers within and / or available to the public cloud 105. The virtual computing environments (VCEs) typically take the form of virtual machines from a virtual machine set 143 and / or containers from a container set 144. It is understood that these VCEs may be stored as images and transferred among and between various hosts of physical machines either as images or after instantiation of the VCE. Cloud orchestration module 141 manages the transfer and storage of images, deploys new instantiations of VCEs, and manages the active instantiation of VCE deployments. Gateway 140 is a collection of computer software, hardware, and firmware that enables public cloud 105 to communicate over WAN 102.

[0037] We now provide some further explanation of virtual computing environments (VCEs). A VCE can be stored as an "image." A new, active instance of a VCE can be instantiated from the image. Two well-known types of VCEs are virtual machines and containers. A container is a VCE that uses operating system-level virtualization. This refers to a feature of an operating system in which the kernel allows the existence of multiple isolated user space instances called containers. These isolated user space instances typically behave as actual computers from the perspective of the programs running within them. A computer program running on a typical operating system can utilize all of the computer's resources, such as connected devices, files and folders, network shares, CPU power, and quantifiable hardware capabilities. However, a program running inside a container can only use the contents of the container and of the devices assigned to the container; this feature is known as containerization.

[0038] A private cloud 106 is similar to a public cloud 105, except that the computing resources are available only for use by a single enterprise. While the private cloud 106 is shown in communication with the WAN 102, in other embodiments, the private cloud may be completely disconnected from the Internet and accessible only through a local / private network. A hybrid cloud is a composite of multiple clouds of different types (e.g., private, community, or public cloud types), often implemented by different vendors. While each of the multiple clouds remains a separate, discrete entity, the larger hybrid cloud architecture is bound together by standardized or proprietary technologies that enable orchestration, management, and / or data / application portability between the constituent clouds. In this embodiment, both the public cloud 105 and the private cloud 106 are part of a larger hybrid cloud.

[0039] The computing environment 100 may access or manage a database, such as a remote database 130. In some embodiments, the remote database 130 may implement a relational model that may be based on predicate logic, which may include target conditions applied to tables or relationships. In a relational model, if statistics are not collected for multiple columns in a table, predicates may be assumed to be independent when calculating cardinality. Predicates may typically be expressed in the form of ranges, requiring lower and upper bounds. BETWEEN and range predicates may be formed into separate ranges of values ​​for each predicate. An equal predicate may have equal lower and upper bounds. This may provide some improvement in cardinality estimation; however, the computational power required to achieve improved cardinality estimation is large, costly, and time-consuming.

[0040] Cardinality estimation plays an important role in Structured Query Language (SQL) optimization. Cardinality in terms of Structured Query Language (SQL) refers to the number of rows processed by various operators in a query execution plan. The number of rows entering an operator may decrease or increase as the number of rows is processed by different operators until the final result is returned to the application or user that issued the query. This is distinct from column cardinality, which is a statistic that refers to the number of distinct values ​​in a column of a database table. A scan of a database table may apply a predicate that reads all rows in the table; however, the output may only include rows that qualify the predicate condition. Cardinality estimation is a prediction of the number of rows that qualify the condition versus the number of rows input to the scan operator.

[0041] Cost-based optimizers may rely on cardinality estimates to calculate access plan execution costs and select optimal access plans. Cost-based optimizers may use statistics based on table data values ​​in database tables to identify efficient plans for query execution for structured queries. Therefore, the accuracy of cardinality for structured queries may have an impact on the cost-based optimizer. Cardinality accuracy contributes to the overall goal of selecting the best execution plan through cardinality estimates. Accurate cardinality estimation may be difficult, or the information needed to make the process easier may be expensive to collect, or both. Cardinality accuracy is particularly difficult to achieve for tables with interrelated or skewed columns. Inaccurate cardinality estimates may result in slow access plan selection, unpredictable query performance, slow or unstable query performance, and / or the like, including a combination and / or combination thereof.

[0042] Query optimization relies on the evaluation of query access plans that predict the optimal access of each table and the join method and join order within a query based on several cost estimates. The selection of an optimal query access plan is highly dependent on the accuracy of operator cost estimates. Cardinality estimation is a factor in predicting such costs. In some cases, optimization depends on the accuracy of such estimates. The application of predicates in a query affects cardinality estimation and can be divided into two folds: estimating cardinality after applying local predicates on base tables, and estimating join cardinality. Performing these estimates is a difficult task requiring a certain degree of complexity, which typically requires considering evenness, skewness, correlation, and predicate type (e.g., evenness, range, negation, conjunction, disjunction, and / or the like, including combinations and / or combinations thereof). Inaccuracies in such estimates can result in suboptimal query access plan selection, resulting in slow or unpredictable query performance.

[0043] Traditional optimizer techniques for optimizing query execution rely on a limited number of collected statistics. Collected statistics decompose large distributions with multiple columns into fewer parameters that describe the number of distinct values, the top n most frequently occurring values, and histograms of individual columns. In these cases, independence between column values ​​of columns involved in multiple predicates is assumed, which is called the independence assumption. The independence assumption is useful for overcoming missing correlation information. However, when individual columns are correlated with each other, estimation errors in local predicates propagate and accumulate through joins. Collecting column group statistics or detailed statistics on join results, for example, using statistical views, can help solve estimation problems, but it involves very high tuning costs. It is difficult to determine which columns are potentially useful for statistically combining together. Because table data changes, it is also difficult to continuously collect and frequently store such data. For example, for every n columns, covering all possible combinations requires 2 n-1 A group of individuals is needed.

[0044] Tuning to obtain better query execution plans is often difficult because it relies on deep expertise. Traditional approaches to improving cardinality estimates range from manually running advisors to collect advanced statistics to just-in-time targeted statistics collection on a sample of data prior to query optimization. On-the-fly statistics collection can add performance overhead. Automatic column group statistics (CGS) collection, performed by a background daemon, can help obtain better estimates using multiple predicates over statistically correlated columns. In such cases, the daemon wakes up periodically (every two hours); however, this may be too slow, as queries requiring this information may be executed interim. Additionally, not all issues, such as skew or inconsistency in join column value domains, are addressed using CGS, where minimum statistics may not overcome assumptions made when computing join predicate cardinality estimates.

[0045] Relying on several assumptions to predict the joint distribution of multiple relations often results in significant cardinality estimation errors. Assumptions of independence, uniformity, and containment are just three examples that contribute to these errors. This can result in very poor query performance. Additionally, sampling on join columns can quickly end the known problem of 0-tuple join results (e.g., collecting statistics by combining independently collected samples that are not correlated in the join predicate column).

[0046] Efforts have been made to address some of these challenges using machine learning (ML). ML shows promise for outperforming statistically based estimators to estimate predicates / joins / query cardinalities. Learned models can achieve better performance over statistically based methods.

[0047] One such approach proposed a learning model for training single and join intersection tables. This approach incorporates interrelated columns in single or multiple tables. The learning model used is a deep learning neural network constructed in the form of a multiset convolutional network. The learning model uses materialized examples to encode each query. Each query is represented as a feature according to three main sets. This approach is provided to estimate the cardinality of the entire query. The query is executed against the database to obtain the actual cardinality. However, this approach relies heavily on materialized examples from each table in the database and requires the identification of columns and tables through encoding. This makes the training very specific to named tables and columns and cannot be generalized to unseen tables or columns.

[0048] Another approach is to reduce the overhead of estimating range predicates by sampling the data and approximating the joint data distribution. However, sampling the data on the fly for each range predicate and feeding them into a model to estimate their density and summing the results is prohibitively expensive in terms of processing and memory load, making it impractical.

[0049] Another approach proposes using machine learning to estimate query cardinality. This approach relies on statistics from previously executed queries. The actual cardinality is learned from the query structure, where predicates are expressed by participating columns and associated operators. However, this approach assumes that the workload is static and that there is only one correct answer per query structure.

[0050] Another approach proposes the use of machine learning for cardinality estimation through the use of basic neural networks, recurrent neural networks, and random forests. The proposed models use one-dimensional vectors, namely: relations, predicates, and join predicates. Literals found in the query are normalized to values ​​between 0 and 1. Join predicates are encoded with one-hot encoding. The overall goal is to have the model learn the selectivity of the join operations. However, this approach incurs significant overhead when building the model, as building the model is computationally expensive in terms of processing and memory load, and takes time.

[0051] Another approach proposes generating smaller models for a subset of joins, where only 2- and 3-way joins are trained on those specific joins to capture different combinations of local predicates. This approach does not generalize to arbitrary 2-, 3-, or n-way joins.

[0052] Yet another approach proposes using deep neural networks to build query execution plans by continuously learning from previously executed queries. This method learns correlations through word similarities in the data from which a model can be generated. This approach is limited to generalizing to columns not seen during training.

[0053] In an effort to remedy these and other shortcomings, one or more embodiments described herein provide for using a machine learning model to learn the selectivity of a complete query. Learning the selectivity of a complete query can then be used to predict more accurate cardinality estimates for any access plan, for subpartial plans, and / or for any query applied to join predicates. The ML model learns from the training workload and improves its predictions accordingly. One or more embodiments described herein provide improved robustness for learning from examples represented in different distributions and generalizing predictions across unseen tables / queries. This approach provides for handling multiple tables with multiple join columns and multiple local predicates.

[0054] One or more embodiments described herein use machine learning to approximate the multivariate resulting query cardinality after applying local and join predicates. To accomplish this, a query graph representation of query characteristics is generated. A query graph representation (or simply a "query graph") is an n-dimensional representation of nodes, where the nodes represent relational database tables. By representing queries in the form of a query graph, a machine learning model can learn the query and generate associated predicted cardinalities. The resulting query graph is robust to handle local and join predicates, normalized to represent tables, and join characteristics independent of table and column names. One or more embodiments described herein provide retraining, for example, by providing the ability to incrementally learn with the addition of new tables and workloads. One or more embodiments described herein provide retraining, for example, by running a cumulative training workload for full model retraining.

[0055] One or more embodiments described herein for learning join cardinalities provide one or more advantages over existing machine learning models. One or more embodiments described herein provide the ability to learn join result distributions. For example, one or more embodiments enable a learning model to understand the distributional properties of multiple relationships in a given query graph, avoiding the need for leveraging multi-column statistics or collecting statistical views or materialized query tables (also known as materialized views), and / or providing additional benefits for pairwise join correlations with any local predicates and with other pairwise joins.

[0056] One or more embodiments described herein provide generalization. For example, one or more embodiments using a query graph provide for avoiding encoding tables, joins, or columns with local or join predicates. In this way, the model learns various characteristics of queries that join multiple relations while generalizing to new queries on newly generated tables or unseen join columns. Additionally or alternatively, if the underlying distribution of relations changes over time, the learning model does not associate the specific table, column, and join encodings as used during training, but rather associates the characteristics of the new distribution.

[0057] One or more embodiments described herein provide feature decomposition. For example, a query graph provides defining a nomenclature for how to plot nodes in a multidimensional graph (query graph). A query graph provides encoding a query with multiple features rather than sequentially lining the query as input to a model, which can significantly grow the number of features for an n-table query.

[0058] One or more embodiments described herein provide for enabling ensemble architectures. For example, in accordance with one or more embodiments, a graph-based approach provides for multiple models to work collaboratively to improve the accuracy of model usage.

[0059] Referring now to FIG. 2 , one or more embodiments described herein may utilize machine learning techniques to perform tasks such as estimating the cardinality of a query using a query graph. More specifically, one or more embodiments described herein may incorporate and utilize rule-based decisions and artificial intelligence (AI) inference to accomplish various operations described herein, i.e., estimating the cardinality of a query using a query graph. The phrase “machine learning” broadly refers to the ability of an electronic system to learn from data. A machine learning system, engine, or module may include a trainable machine learning algorithm that can be trained, for example, in an external cloud environment, to learn functional relationships between inputs and outputs, and the resulting model (sometimes referred to as a “trained neural network,” “trained model,” and / or “trained machine learning model”) can be used, for example, to estimate the cardinality of a query using a query graph. In one or more embodiments, the machine learning function may be implemented using an artificial neural network (ANN) capable of being trained to perform a function. In machine learning and cognitive science, ANNs are a family of statistical learning models inspired by biological neural networks, particularly the brains of animals. ANNs can be used to estimate or approximate systems and functions that depend on a large number of inputs. Convolutional neural networks (CNNs) are a class of deep, feed-forward ANNs that are particularly useful in tasks such as, but not limited to, analyzing visual images and natural language processing (NLP). Recurrent neural networks (RNNs) are another type of deep, feed-forward ANNs that are particularly useful for tasks such as, but not limited to, unsegmented, connected handwriting recognition and speech recognition. Other types of neural networks are known and can be used in accordance with one or more embodiments described herein.

[0060] An ANN can be embodied as a so-called "neuromorphic" system of interconnected processor elements that act as simulated "neurons" and exchange messages between each other in the form of electronic signals. Similar to the so-called "plasticity" of synaptic neurotransmitter connections that carry messages between biological neurons, connections in an ANN that carry electronic messages between simulated neurons are provided with numerical weights that correspond to the strength or weakness of a given connection. The weights can be adjusted and tuned based on experience to allow the ANN to adapt to the input and learn. For example, an ANN for handwritten character recognition is defined by a set of input neurons that can be activated by pixels in an input image. After being weighted and transformed by a function determined by the network designer, the activations of these input neurons are passed on to other downstream neurons, often referred to as "hidden" neurons. This process is repeated until an output neuron is activated. The activated output neuron determines which character has been input. It should be understood that these same techniques can be applied when estimating the cardinality of a query using a query graph as described herein.

[0061] A system for training and using machine learning models is now described in more detail with reference to FIG. 2. Specifically, FIG. 2 shows a block diagram of components of a machine learning training and inference system 200 in accordance with one or more embodiments described herein. The machine learning training and inference system 200 performs training 202 and inference 204. During training 202, a training engine 216 trains a model (e.g., trained model 218) to perform a task, such as estimating the cardinality of a query using a query graph. Inference 204 is a process that implements the trained model 218 to perform a task, such as estimating the cardinality of a query using a query graph, in the context of a larger system (e.g., system 226). All or a portion of the machine learning training and inference system 200 shown in FIG. 2 may be implemented by, for example, all or a subset of the computing environment 100 of FIG. 1.

[0062] Training 202 begins with training data 212, which may be structured or unstructured data. According to one or more embodiments described herein, training data 212 includes queries from a query workload. Training engine 216 receives training data 212 and model format 214. Model format 214 represents an untrained base model. Model format 214 may have preset weights and biases that may be adjusted during training. It should be understood that model format 214 may be selected from many different model formats depending on the task to be performed. For example, if training 202 is to train a model to perform image classification, model format 214 may be a CNN model format. Training 202 may be supervised learning, semi-supervised learning, unsupervised learning, reinforcement learning, and / or the like, including combinations and / or multiples thereof. For example, supervised learning may be used to train a machine learning model to classify objects of interest in images. To do this, training data 212 includes labeled images, including images of objects of interest with associated labels (ground truth), and other images that do not include the objects of interest with associated labels. In this example, training engine 216 takes training images from training data 212 as input, makes a prediction to classify the image, and compares the prediction to the known label. Training engine 216 then adjusts the weights and / or biases of the model based on the results of the comparison, such as by using backpropagation. Training 202 may be performed multiple times (called "epochs") until a suitable model is trained (e.g., trained model 218).

[0063] Once trained, the trained model 218 can be used to perform inference 204 to perform tasks such as estimating the cardinality of a query using the query graph. The inference engine 220 applies the trained model 218 to new data 222 (e.g., real-world, non-training data). For example, if the trained model 218 is trained to classify images of a particular object, such as a chair, the new data 222 can be images of chairs that were not part of the training data 212. In this manner, the new data 222 represents data to which the trained model 218 has not been exposed. The inference engine 220 makes a prediction 224 (e.g., classification of an object in an image of the new data 222) and passes the prediction 224 to a system 226 (e.g., the computing environment 100 of FIG. 1). The system 226 can take an action, perform an operation, perform an analysis, and / or the like, including a combination and / or a plurality of these, based on the prediction 224. In some embodiments, the system 226 may add and / or modify new data 222 based on the prediction 224 .

[0064] According to one or more embodiments, the predictions 224 generated by the inference engine 220 are periodically monitored and validated to ensure that the inference engine 220 is performing as expected. Based on the validation, additional training 202 may occur using the trained model 218 as a starting point. The additional training 202 may include all or a subset of the original training data 212 and / or new training data 212. According to one or more embodiments, training 202 includes updating the trained model 218 to account for changes in expected input data.

[0065] 3 illustrates a method 300 for estimating the cardinality of a query using a query graph in accordance with one or more embodiments described herein. Method 300 may be performed by any suitable system, environment, and / or device, such as computing environment 100, machine learning training and inference system 200, and / or the like, including combinations and / or multiples thereof. Method 300 is described in more detail herein with at least reference to FIG. 2, but is not so limited.

[0066] In block 302, a processing system (e.g., computing environment 100, machine learning training and inference system 200, and / or the like including a combination thereof and / or a plurality thereof) uses the query graph to train a machine learning model (e.g., trained model 218). To train the machine learning model, the processing system generates training queries from a query workload, or generates and / or receives training queries based on relationships between tables in a database. Using queries from the training queries, the processing system constructs a query graph using input vectors that anonymize tables in the database by using a representation of the database features, and provides selectivity as a desired output (e.g., by using the query representation as a graph). The processing system then constructs an initial join result distribution from the training queries as a collection of query graphs.

[0067] Training a machine learning model (e.g., trained model 218) is described in more detail herein. Training data (e.g., training data 212) may be generated incrementally using a collected query workload. If a collected query workload is not initially available, a synthetic workload for the database may be generated and used. The generated query may be a joint query based on a defined relationship in the database with a random local predicate. The workload query, when collected, may be simplified to the join portion of the query with the local predicate.

[0068] The training engine 216 takes as input a vector containing the dimensions of each node in the query graph. According to an embodiment, node positioning is based on geometric rules established in the graph representation described herein. Randomly collected samples may be scanned for encoding of node characteristics, such as base table selectivity, join sample selectivity, and top-frequent value selectivity. Training does not require any non-random sampling techniques to maintain high-quality estimates. However, according to one or more embodiments described herein, selectivity may be obtained from traces, if they are available, from previously executed workload queries. For a well-defined star schema, a sample of a fact table joined to all dimension tables produces improved results compared to a separate sample. Accurate results may be achieved without a sample of a fact table joined to all dimension tables, at the expense of increased training time.

[0069] According to one or more embodiments described herein, any suitable number of training data may be used to train the trained model 218. According to an embodiment, the number of training data may depend on the relevance to the workload. Implementing a training accuracy threshold, along with model size or training time, may guide the amount of training data needed according to an embodiment. Additionally and / or alternatively, as a workload is executed, run-time feedback of predicted estimates versus actual cardinality may be used to continuously train or retrain the models described herein.

[0070] Once training 202 is performed (e.g., once trained model 218 is trained by training engine 216), inference 204 can begin. At block 304, the processing system estimates the cardinality of the query using the trained machine learning model from block 302. In other words, the processing system performs inference using the trained machine learning from block 302. For example, for each join enumeration, the optimizer invokes the trained machine learning model to calculate the join cardinality. The optimizer enumerates different permutations of the same set (or subset) of N tables. According to the example, the cardinality is the same, so the prediction is calculated once, cached, and reused as needed, resulting in improved performance of the processing system. The trained machine learning model identifies the best fit among the set of query graphs and generates an associated cardinality estimate of the best fit, which is returned to the optimizer.

[0071] Inference 204 begins when a query is received by inference engine 220 (also called "optimizer" or "database optimizer"). Once a query is received by the database optimizer, the optimizer enumerates various permutations of join orders. For each of these join orders, the optimizer constructs an input vector that describes a join graph, along with one or more features further described herein. Inference engine 220 matches patterns among the available patterns of the input vector and the query graph and applies the learned model to generate an associated predicted cardinality (e.g., prediction 224).

[0072] In accordance with one or more embodiments described herein, the optimizer may enumerate different permutations of join orders for the same set (or subset) of N tables, but the resulting cardinality for each join order is the same. To minimize any computational load (e.g., processing and / or memory) overhead of constructing input vectors and / or computing predictions using the model, prediction results may be computed once for the set of N tables and cached for later reuse.

[0073] To calculate the intermediate cardinality for a subset of tables (e.g., for each enumerated join order), the inference engine 220 may apply the trained model 218 using the subset of tables and their local predicates to generate improved cardinalities for joins of these tables. For example, a query may have five tables, where two of the five tables are lookup joins (N:1). In this example, the trained model 218 predicts cardinalities for only the joins of the other three tables, which primarily filter the join. After joining the lookup table, the cardinality remains unchanged. This reduces the size and complexity of the query graph and also reduces the training queries required to train the machine learning model.

[0074] It should be understood that during inference 204, the distributional characteristics of tables and columns may not exactly match those in the queries used during training 202. If there are a large enough number of tables and training scenarios, there will be enough tables with different distributions and predicates that the trained model 218 will still work for new tables and columns not seen exactly before.

[0075] In block 306, the processing system performs ongoing training and / or completely retrains the trained machine learning model from block 302. For example, new queries in a new query load may be compiled based on a new workload, new tables, changed data in the tables, and differences in estimates predicted by the trained model regarding actual cardinality from runtime feedback and / or combinations thereof and / or the like, including multiples thereof. The trained machine learning model may be retrained by adding or modifying the set of query graphs with the new query workload.

[0076] Because databases are typically dynamic, the information stored in the database changes over time. Thus, if the underlying distributions of some of the tables change significantly over time, a full model retraining may be necessary (e.g., retraining the trained model 218 by running training 202 again). A threshold may be implemented to indicate when the underlying distributions have changed significantly (e.g., enough to warrant retraining). For example, the threshold may be a percentage difference between the frequency and / or histogram statistics collected for a table compared to the same when the trained model 218 was last fully trained. In accordance with one or more embodiments described herein, retraining is performed based on the extent of change (e.g., the number of rows updated, deleted, and / or inserted relative to table size, and / or the like, including a combination thereof and / or more than one of them). In accordance with one or more embodiments described herein, retraining is performed based on the number of tables created, dropped, or altered in a way that affects the model (e.g., drop column, add column, and / or the like, including a combination thereof and / or more than one of them).

[0077] The trained model 218 may also be continuously updated / trained. With continuous training, labels in the query graph may become outdated and no longer reflect the current reality. Therefore, queries previously used in training may be one or more of the following scenarios: First, queries in the training set that have not been executed recently. These queries still retain the correct labels. Second, queries that have been executed recently. These queries are added to the new query workload if they match any entries in the training set. Any hash function may be used to hash the queries in the training data 212 and discard redundant queries. Third, queries that are not in the training set and model estimation are higher / lower (e.g., different in order of magnitude) than the actual cardinality by a certain amount (e.g., a threshold number, a percentage difference in thresholds, and / or the like, including a combination and / or multiple thereof). These queries are added to the new query workload. Any other metric may be used to trigger this scenario as well. According to one or more embodiments described herein, the next time the model is retrained, some or all of those queries are added to a new set of observed examples to retrain the trained model 218.

[0078] In accordance with one or more embodiments described herein, a runtime feedback component that indicates the difference between the predicted cardinality and the actual cardinality may be used to indicate when the trained model 218 needs to add queries for additional ongoing training or add training workload for a complete retraining.

[0079] According to one or more embodiments, the trained machine learning model provides the ability to learn joint result distributions, provides generalization to handle unseen tables and columns, provides feature decomposition, enables ensemble architecture, and / or the like, including combinations and / or multiples thereof. Additional processes may also be included. According to one or more embodiments described herein, method 300 is applicable to other database technologies, such as relational databases, graph databases, extensible markup language (XML) databases, and / or the like, including combinations and / or multiples thereof. It should be understood that the processes shown in FIG. 3 represent an example, and that other processes may be added or existing processes may be removed, modified, or rearranged without departing from the scope of the present disclosure.

[0080] 4 illustrates query graphs 400, 410 according to one or more embodiments described herein. The query graphs 400, 410 may be used during training 202 to train the trained model 218 and / or to perform inference 204 using the trained model 218. Training 202 and inference 204 are described in more detail herein with reference to the query graphs 400, 410, but are not limited to such.

[0081] According to one or more embodiments described herein, estimating the cardinality of a query is a regression problem in which the cardinality of a query graph (e.g., one or more of query graphs 400, 410) is predicted. The query graph may represent the complete query or a subportion of the query, such as different permutations of joins as enumerated by a query optimizer. According to one or more embodiments described herein, any regression machine learning model may be used. The query graph is what the trained model 218 learns from, along with the true cardinality of the query. The target variable of the trained model 218 may be the selectivity of the Cartesian join. Learning the selectivity rather than the actual cardinality provides the trained model 218 with a normalized variable, which may accelerate training 202 and increase the accuracy of the trained model 218. The selectivity may be converted back to a cardinality for output. The query graph may represent a subportion of the query plan. The target variable is the selectivity of the Cartesian join.

[0082] In order for the trained model 218 to learn how to estimate the cardinality of a query, a list of useful features is defined to represent the characteristics of the query. A query graph (e.g., one or more of the query graphs 400, 410) includes a graph, a vertex (or vertices), an edge (or edges), and a vertex degree.

[0083] Each query can be modeled as a graph G according to the following: G={V,E,D} V: A finite set of vertices. E ⊆ V × V is the set of edges. D: A set of multidimensional features is shown.

[0084] A vertex is a graph node that represents a database relational table. Adjacent vertices are tables connected by an edge. An edge is an undirected line that represents a binary join connecting two tables. The degree of a vertex represents the number of tables that join a given vertex.

[0085] Join operations are binary in nature (e.g., only two tables join at a time). Each join may be represented by two vertices connected by an undirected edge. For example, query graph 400 represents the first query. In this example, query graph 400 includes three vertices 401, 402, and 403, which are connected by edges as shown. The three vertices 401-403 indicate that the query references three tables. Query graph 410 represents the second query and includes six vertices 411, 412, 413, 414, 415, and 416, which are connected by edges as shown. The six vertices 411-406 indicate that the query references six tables. If the query has only one table, a self-join node may be used to represent the graph with a single table.

[0086] According to one or more embodiments described herein, for any given query graph G, at least |T|-1 edges, where |T| represents the number of tables. The query space is bounded between +1 and -1 values. Bounding the space helps train a trained model 218 with an already normalized range of values ​​that can be adjusted to accommodate the large number of tables in each query. How to arrange nodes in such a graph space is now described.

[0087] The generation of a query graph (e.g., one or more of query graphs 400, 410) is based on the number of tables and join columns provided in the query. The coordinates of each node may represent some features that describe the base table. The characteristics of the edges may be used to indicate the aspect of the join. For example, the line weight and length of the edges may describe the join columns for each of the binary nodes and may represent other characteristics such as the type of join. For example, the direction of the edges may represent the type of join, where no direction indicates an inner join, a single direction represents a left outer join or a right outer join with an arrow pointing to the null producing side (e.g., T1 → T2 describes T1 LOJ T2), and a bidirectional edge represents a full outer join (e.g., T1 ←→ T2 describes T1 FOJ T2). In accordance with one or more embodiments described herein, the feature of each node may be the base table selectivity after applying the local predicate, the total selectivity of the top frequent values, and / or any other feature that demonstrates the impact of applying the local predicate on the base table cardinality.

[0088] Constructing a query graph (e.g., one or more of query graphs 400, 410) is now described. To construct a query graph, nodes and edges are created, initial positions for each of the nodes are determined, and the node positions are then refined / optimized.

[0089] The generation of nodes and edges will now be described with reference to Figure 5, which shows a spatial graph 500 illustrating two tables with their join columns according to one or more embodiments described herein. Each group of join columns between any two nodes is treated as one entity of the join feature. For example, the following columns: 1.c1 =T 2.c1 and T 1.c5 =T 2.c55, if a join exists between a first table T1 and a second table T2, a node in the graph is generated as shown in FIG. 5. The edge between the first table T1 and the second table T2 represents the join column. According to an example, the line weight or style (e.g., dashed line) of the edge may represent the operator between the join columns, i.e., inner join, outer join, etc., where different line weights or styles represent different types of joins.

[0090] Determining the initial position for each of the nodes will now be described with reference to FIG. 6, which shows the characteristics of each node (from FIG. 5) representing its initial coordinates in graph space. Each node has at least two dimensions representing the node's coordinates in the graph. The base table cardinality, along with the total selectivity of the most frequently occurring values, are useful for use as the initial position for each node in the graph. For example, FIG. 6 shows the initial positions in two dimensions for each node in the first table T1 and the second table T2 from FIG. 5. The initial positions of the nodes in the first table T1 are 0.33 and 0.01...0.001. The initial positions of the nodes in the second table T2 are 0.51 and 0.03...0.01. The first value (0.33 for T1 and 0.51 for T2) represents the selectivity of the base table cardinality after applying the local predicate. The second value (0.01...0.001 for T1 and 0.03...0.01 for T2) represents the total selectivity of the most frequently occurring values. The second value is useful for determining whether the application of local predicates has changed the selectivity of any of the highly skewed values ​​(if any). According to one or more embodiments described herein, the total selectivity of the top x values ​​is calculated from randomly collected samples. According to one or more embodiments described herein, the total selectivity of the top x values ​​is calculated from several predetermined materialized samples or previously collected statistical tables. According to an example, the sample size may be between 2000 and 5000 rows for each of the tables. In some cases, such as for smaller tables, sampling may be skipped.

[0091] Optimizing node locations will now be described with reference to FIG. 7 , which illustrates a resulting query graph 700 with optimized node positioning for a three-table query in accordance with one or more embodiments described herein. Relying on a query graph to represent a query and then using the underlying distributional properties introduces a number of challenges. First, ensuring that the graphs have the same representation given the same situation (e.g., the same tables, the same joins, and the same local predicates that do not change in the actual distribution). Second, ensuring that nodes do not overlap locationally. Third, encoding table names, predicates, and joins to be able to handle unseen tables, modifying data in tables and queries with unseen predicates, and / or generalizing the query graph to avoid combinations and / or combinations thereof. To address these challenges, one or more embodiments described herein provide for optimizing node positioning in a query graph given the initial locations described herein. One possible approach to optimizing the positioning of nodes in a query graph is to use the optimization algorithm described in "Graph drawing by force-directed placement." Software: Practice and experience 21.11 (1991): 1129-1164 by Fruchterman, Thomas MJ, and Edward M. Reingold. This approach calculates the attractive forces between adjacent vertices as well as the repulsive forces of vertices in the query graph. FIG. 7 shows a query graph 700 resulting from a query of three tables using optimization. In accordance with one or more embodiments described herein, additional dimensions can be added to the query graph representing join selectivity, or features can be included in the node coordinates generated by the optimization algorithm. Expressions for columns may also be encoded and added to the properties.

[0092] In accordance with one or more embodiments described herein, additional dimensions may be considered to further optimize node positioning: the percentage of table size without any predicates, the percentage of distinct values, and the selectivity of local predicates in actual join samples.

[0093] To train the trained model 218 to generalize on unseen tables, the ratio of the table sizes of the child and parent nodes can be added. This ratio provides evidence of how relatively large the tables were initially before the join.

[0094] The proportion of distinct values ​​helps determine the underlying distribution of the join. The distinct values ​​between nodes helps determine whether the relationship is a primary-foreign key or whether the join is between two primary keys.

[0095] The selectivity of a join sample determines the impact of a predicate on the actual join sample. For example, for each unique join, the selectivity of the predicate can be measured on a collected sample of 2000 rows. The sample is unjoined, which can result in the 0-tuple problem for actual node joins.

[0096] 8 illustrates a resultant query graph 800 with updated positions according to one or more embodiments described herein. In this example, the resultant query graph 800 is an updated version of the resultant query graph 700 of FIG. 7. The resultant query graph 800 includes updated positions on the y-axis with the selectivity of the predicates in the join samples. Specifically, the resultant query graph 700 is generated after using the optimization algorithm described herein, and the resultant query graph 800 shows updated positions of nodes using the selectivity of the join samples.

[0097] 9A, 9B, and 9C together illustrate an example of constructing a query graph for a given query according to one or more embodiments described herein. In this example, the input to the trained model 218 is features for each query. Example features include the base table selectivity of each node and the coordinates (x, y) of each node, which are generated using techniques described herein. FIG. 9A illustrates a sample query 900 according to one or more embodiments described herein. The sample query 900 uses IMDb for illustrative purposes. FIG. 9B illustrates features for each node (from FIG. 9C) that represent their initial coordinates in the graph space. In this example, the base table attributes (BTAs) include the base table selectivity (e.g., considering local predicates) and the selectivity of the top frequent values ​​(e.g., representing data distribution). The processing system may normalize the coordinates to generalize the graph, such as by using the Fruchterman and Reingold algorithm as described herein. The join characteristics utilize the proportion of table sizes, the proportion of distinct values, and / or the selectivity of predicates in the join sample, as described herein. Figure 9C shows a query graph representation 902 of a sample query 900 performed on the Internet Movie Database (IMDb).

[0098] 10 shows a flow diagram of a method for training a machine learning model to estimate the cardinality of a query using a query graph according to one or more embodiments described herein. Method 1000 may be performed by any suitable system, environment, and / or device, such as computing environment 100, machine learning training and inference system 200, and / or the like, including combinations and / or multiples thereof. Method 1000 is described in more detail herein with at least reference to FIG. 2, but is not so limited.

[0099] At block 1002, the processing system generates training queries from a query workload or generates training queries based on relationships between tables in a database (e.g., referential integrity constraints can be predetermined or discovered) and / or receives such training queries. At block 1004, using queries from the training queries, the processing system constructs a query graph using input vectors that anonymize tables in the database by using a representation of features of the database. For example, constructing the query graph representation for each of the training queries includes using input vectors that anonymize tables by using a representation of features and providing the selectivity as a desired output (e.g., node positions may be based on geometric rules established in the query graph representation). At block 1006, the processing system provides (e.g., calculates) the selectivity as a desired output (e.g., using the representation of the query as a graph). At block 1008, the processing system constructs an initial join result distribution from the training queries as a collection of query graphs.

[0100] The flowcharts and block diagrams in the figures 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 regard, each block in a flowchart or block diagram may represent a module, segment, or portion of instructions, including one or more executable instructions for implementing a specified logical function. In some alternative implementations, the functions noted in the blocks may occur out of the order noted in the figures. For example, two blocks shown in succession may in fact be executed substantially concurrently, or the blocks may be executed in the reverse order, depending on the functionality involved. It should also be noted that each block of the block diagrams and / or flowchart diagrams, and combinations of blocks in the block diagrams and / or flowchart diagrams, may be implemented by a dedicated hardware-based system that performs the specified functions or actions or executes a combination of dedicated hardware and computer instructions.

[0101] The description of various embodiments of the present invention has been presented for purposes of illustration and is not intended to be exhaustive or limited to the disclosed embodiments. Many modifications and variations will be apparent to those skilled in the art without departing from the scope of the described embodiments. The terminology used herein has been selected to best explain the principles of the embodiments, practical applications, or technical improvements over technologies found in the market, or to enable others skilled in the art to understand the embodiments described herein.

Claims

1. generating a set of training queries using at least one of a query workload and relationships between tables in the database; constructing a query graph for each of the set of training queries; computing, for each training query in the set of training queries, a selectivity based at least in part on the query graph; and constructing an initial joint result distribution as a collection of query graphs based at least in part on the set of training queries. Training a machine learning model by A computer-implemented method comprising:

2. 2. The computer-implemented method of claim 1, wherein constructing the query graph for each of the training queries comprises using an input vector anonymizing table by using a feature representation, and providing the selectivity as a desired output.

3. The computer-implemented method of claim 1 , further comprising, subsequent to training the machine learning model, performing inference using the machine learning model.

4. The step of performing the inference comprises: constructing a query graph for each combination of joins in the query; identifying a best match among the set of query graphs to generate an associated cardinality estimate; The computer-implemented method of claim 3 , comprising:

5. The computer-implemented method of claim 4 , wherein calculating the join cardinality comprises enumerating different permutations for the same set of N tables.

6. 6. The computer-implemented method of claim 5, wherein computing the join cardinalities comprises computing intermediate cardinalities on a subset of tables for each enumerated join order, wherein the machine learning model is invoked with the subset of tables and a local predicate.

7. The computer-implemented method of claim 1 , further comprising performing continuous training on the machine learning model.

8. The step of performing the continuous training on the machine learning model comprises: compiling new queries in the new query workload using inputs including at least one change selected from the group consisting of the workload, the new table, and a new query based on the change to the data; and updating the set of query graphs The computer-implemented method of claim 7, comprising:

9. compiling new queries in the new query workload using inputs including at least one change selected from the group consisting of the workload, the new table, and a new query based on the change to the data; and generating updates by retraining the machine learning model using the new query workload and updating the set of query graphs using the updates; The computer-implemented method of claim 1 further comprising:

10. a memory having computer-readable instructions; and a processing device for executing said computer-readable instructions; Equipped with The computer readable instructions include: generating a set of training queries using at least one of a query workload and relationships between tables in the database; constructing a query graph for each of the set of training queries; computing, for each training query in the set of training queries, a selectivity based at least in part on the query graph; and constructing an initial joint result distribution as a collection of query graphs based at least in part on the set of training queries. Training a machine learning model by controlling the processing device to perform operations comprising: system.

11. 11. The system of claim 10, wherein constructing the query graph for each of the training queries comprises using an input vector anonymizing table by using a feature representation and providing the selectivity as a desired output.

12. 11. The system of claim 10, wherein the operations further comprise, following training the machine learning model, performing inference using the machine learning model.

13. The step of performing the inference comprises: constructing a query graph for each combination of joins in the query; identifying a best match among the set of query graphs to generate an associated cardinality estimate; The system of claim 12, comprising:

14. 14. The system of claim 13, wherein calculating the join cardinality comprises enumerating different permutations for the same set of N tables.

15. 15. The system of claim 14, wherein computing the join cardinalities comprises computing intermediate cardinalities on a subset of tables for each enumerated join order, wherein the machine learning model is invoked with the subset of tables and local predicates.

16. The system of claim 10 , wherein the operations further comprise performing continuous training on the machine learning model.

17. The step of performing the continuous training on the machine learning model comprises: compiling new queries in the new query workload using inputs including at least one change selected from the group consisting of the workload, the new table, and a new query based on the change to the data; and updating the set of query graphs 17. The system of claim 16, comprising:

18. The operations are: compiling new queries in the new query workload using inputs including at least one change selected from the group consisting of the workload, the new table, and a new query based on the change to the data; and generating updates by retraining the machine learning model using the new query workload and updating the set of query graphs using the updates; The system of claim 10 further comprising:

19. 1. A computer program product comprising a computer-readable storage medium having program instructions embodied thereon, the program instructions comprising: generating a set of training queries using at least one of a query workload and relationships between tables in the database; constructing a query graph for each of the set of training queries; computing, for each training query in the set of training queries, a selectivity based at least in part on the query graph; and constructing an initial joint result distribution as a collection of query graphs based at least in part on the set of training queries. Training a machine learning model by 1. A computer program product executable by a processor to cause the processor to perform operations comprising:

20. 20. The computer program product of claim 19, wherein constructing the query graph for each of the training queries comprises using an input vector anonymizing table by using a feature representation, and providing the selectivity as a desired output.

21. 20. The computer program product of claim 19, wherein the operations further comprise, following training the machine learning model, performing inference using the machine learning model.

22. The step of performing the inference comprises: constructing a query graph for each combination of joins in the query; identifying a best match among the set of query graphs to generate an associated cardinality estimate; 22. The computer program product of claim 21, comprising:

23. 23. The computer program product of claim 22, wherein computing join cardinalities comprises enumerating different permutations for the same set of N tables, and computing the join cardinalities comprises computing intermediate cardinalities on a subset of tables for each enumerated join order, wherein the machine learning model is invoked with the subset of tables and a local predicate.

24. generating a set of training queries using at least one of a query workload and relationships between tables in the database; constructing a query graph for each of the set of training queries; computing, for each training query in the set of training queries, a selectivity based at least in part on the query graph; and constructing an initial joint result distribution as a collection of query graphs based at least in part on the set of training queries. training a machine learning model by Following the step of training the machine learning model, calculating the join cardinality for each join statement of the query; and identifying a best match among the set of query graphs to generate an associated cardinality estimate; performing inference using the machine learning model by compiling new queries in the new query workload using inputs including at least one change selected from the group consisting of the workload, the new table, and a new query based on the change to the data; and updating the set of query graphs performing continuous training on the machine learning model by A computer-implemented method comprising:

25. A computer program product comprising a computer-readable storage medium having program instructions embodied thereon, the program instructions comprising: generating a set of training queries using at least one of a query workload and relationships between tables in the database; constructing a query graph for each of the set of training queries; computing, for each training query in the set of training queries, a selectivity based at least in part on the query graph; and constructing an initial joint result distribution as a collection of query graphs based at least in part on the set of training queries. training a machine learning model by Following the step of training the machine learning model, calculating the join cardinality for each join statement of the query; and identifying a best match among the set of query graphs to generate an associated cardinality estimate; performing inference using the machine learning model by compiling new queries in the new query workload using inputs including at least one change selected from the group consisting of the workload, the new table, and a new query based on the change to the data; and updating the set of query graphs performing continuous training on the machine learning model by 1. A computer program product executable by a processor to cause the processor to perform operations comprising:

Images