Trained Joint Cardinality Estimation Using Joint Graph Representation

Abstract

Aspects of the present invention include techniques for providing trained coupled cardinality estimation using coupled graph representations. Non-limiting exemplary methods include the step of constructing a coupled cardinality estimation model. The model may be constructed by the steps of generating training queries with known coupled cardinality, generating an adjacency matrix that encodes the coupled graph of the training queries, encoding one side of the diagonal axis of the adjacency matrix, and training the coupled cardinality estimation model using the encoded adjacency matrix and the known coupled cardinality. The method includes the step of performing inference using the coupled cardinality estimation model. The inference includes the predicted coupled cardinality for the query. The method includes the step of executing a query execution plan for the query using the predicted coupled cardinality.

Description

[Technical Field]

[0001] The present invention generally relates to database design, and more specifically to a computer system, a computer implementation method, and a computer program product for providing trained join cardinality estimation using join graph representations.

[0002] A relational database management system (RDBMS) often relies on a structured query language (SQL) to manage data stored in a relational database. An RDBMS typically includes single-column statistics collected on individual columns, usually within a so-called relation. A relation can include tuples and / or attributes that describe relationships and / or distinct characteristics within a table or between tables in a relational database. For example, a relation can contain data values ​​on a table, and a relational database can store data values ​​as relations or tables. A collection of relations or tables can be stored in the database as a relational model.

[0003] A query can be executed against a relational database using an RDBMS. A query refers to a specific request or command issued to the RDBMS to retrieve, manipulate, and / or update data stored in a relational database. Queries allow users to interact with the database and perform various actions, such as retrieving specific records, filtering data based on specific criteria, aggregating information, joining tables, and modifying data. Queries are typically written using SQL, which provides a standardized set of syntax and commands for interacting with the database through queries.

[0004] Cardinality estimation refers to the process of estimating the number of rows returned by a query operation, and is an essential aspect of query optimization, index selection, resource allocation, and execution planning in RDBMS. When a query is executed, the database or query optimizer (e.g., a component of the RDBMS) analyzes the query and determines the most efficient execution plan. To do this, the optimizer needs to estimate the cardinality of the intermediate and final results of the query. The optimizer makes these estimations using statistical information such as column histograms, single-column statistics, and multi-column correlations.

[0005] The cardinality of a particular column or set of columns represents the number of unique values ​​in that column or set of columns. For example, in a customer table, the cardinality of the "customer_id" column represents the number of unique customers in the table. In contrast, the cardinality of a query result set indicates the number of distinct rows or tuples returned.

[0006] In the context of query optimization, the join cardinality estimation problem refers to the challenge of accurately estimating the number of rows that will result from joining two or more tables in a database query. Join cardinality estimation is crucial for optimizers to generate efficient query execution plans. Optimizers use join cardinality estimation to evaluate different access paths, combine algorithms, and plan choices for selecting the most efficient execution strategy. Accurate join cardinality estimation helps optimizers more accurately estimate the costs of different planning options, including the number of disk I / O operations, CPU utilization, and memory requirements.

[0007] An ideal join cardinality estimation method satisfies several characteristics: it is effective in generating high-quality query plans, it is efficient in minimizing estimation latency, it is generalizable to new queries, and it is easy to deploy in terms of relatively small model size, fast training time, and scaling ability with the number of tables. Unfortunately, accurately estimating join cardinality is difficult due to several factors, including unknown or changing data distributions, unknown correlations between join columns, and filters / predicates in queries.

[0008] Existing combined cardinality estimation techniques address the combined cardinality estimation problem using one or both of two common categories: classical and learning-based. Neither classical nor previous learning-based methods yield good accuracy when estimating the cardinality of combined queries. These techniques often rely on simplified assumptions, which leads to ineffective cardinality estimations, or they require the construction and training of large models to understand data distributions, data correlations, etc., resulting in long planning times and a lack of generalizability between queries. [Overview of the project]

[0009] Embodiments of the present invention relate to techniques for providing trained joined cardinality estimates using joined graph representations. Non-limiting exemplary methods include the step of constructing a joined cardinality estimation model. The model may be constructed by the steps of generating a training query with known joined cardinality, generating an adjacency matrix encoding the joined graph of the training query, encoding one side of the diagonal axis of the adjacency matrix, and training the joined cardinality estimation model using the encoded adjacency matrix and the known joined cardinality. The method includes the step of performing inference using the joined cardinality estimation model. The inference includes a predicted joined cardinality for the query. The method includes the step of executing a query execution plan for the query using the predicted joined cardinality. Advantageously, leveraging the joined cardinality estimation model in this manner enables an encoding mechanism that demonstrates strong generalizability. By using abstract statistical information about the joins of tables, local predicates, and queries, the structure of the joined graph is used rather than its specific elements. Therefore, for a new query, the join cardinality can be inferred based on the similarity of the join graph and abstract information about the tables and the predicates involved.

[0010] In some embodiments, the steps of performing the inference include generating an adjacency matrix that encodes the join graph of the query, encoding one side of the diagonal axis of the adjacency matrix, inputting the encoded adjacency matrix into the join cardinality estimation model, and receiving the predicted join cardinality as output. Advantageously, this configuration provides an efficient join cardinality estimation for each query, as the model can be pre-trained offline.

[0011] In some embodiments, training a coupled cardinality estimation model involves adjusting one or more weights of the coupled cardinality estimation model until the predicted coupled cardinality output for a training query matches a known coupled cardinality within a given threshold. Advantageously, training a coupled cardinality estimation model in this manner provides relatively fast convergence (match with known test inputs).

[0012] In some embodiments, for a given query that joins a set of n tables, each adjacency matrix is ​​defined as an n × n matrix, where the value at position (i,j) in the matrix is ​​1 if table i is joined with table j, and 0 otherwise. Advantageously, this configuration allows the structure of a given query to be encoded in a way that is readily available to a model (e.g., a joined cardinality estimation model).

[0013] In some embodiments, the step of constructing the joined cardinality estimation model further includes generating an additional adjacency matrix that encodes the join types of the training query, encoding one side of the diagonal axis of the additional adjacency matrix into the join type adjacency matrix, and concatenating the encoded join type adjacency matrix with the encoded join graph before training the joined cardinality estimation model. Advantageously, this technique enables matrix encoding to account for join type data in the query.

[0014] In some embodiments, the step of constructing a joined cardinality estimation model further includes generating an additional adjacency matrix that encodes the join operators of the training query, encoding one side of the diagonal axis of the additional adjacency matrix, and concatenating the encoded adjacency matrix of the join operators with an encoded join graph before training the joined cardinality estimation model. Advantageously, this technique allows for matrix encoding to account for the join operator data in the query.

[0015] In some embodiments, the step of constructing a join cardinality estimation model further includes generating an additional adjacency matrix that encodes the ratio of the cardinality of the join column to the table cardinality of each side of the join in the training query; encoding one side of the diagonal axis of the additional adjacency matrix into the encoded adjacency matrix of the column pair table; and concatenating the encoded adjacency matrix of the column pair table with the encoded join graph before training the join cardinality estimation model. Advantageously, this technique enables matrix encoding to account for the ratio of column cardinality to table cardinality in the query.

[0016] In some embodiments, the step of constructing a joined cardinality estimation model further includes generating an additional adjacency matrix that encodes the ratio of the cardinalities of the left and right tables in the join of the training query, encoding one side of the diagonal axis of the additional adjacency matrix into an encoded adjacency matrix of table cardinality, and concatenating the encoded adjacency matrix of table cardinality with an encoded join graph before training the joined cardinality estimation model. Advantageously, this technique allows for matrix encoding to account for the ratio of table cardinality in the query.

[0017] In some embodiments, the step of constructing a joined cardinality estimation model further includes generating an additional adjacency matrix that encodes a measure of skewness for each join column of the join in the training query, encoding one side of the diagonal axis of the additional adjacency matrix into the encoded skewness adjacency matrix, and concatenating the encoded skewness adjacency matrix with the encoded join graph before training the joined cardinality estimation model. Advantageously, this technique allows for matrix encoding to account for the skewness of the join in the query.

[0018] In some embodiments, the step of constructing a join cardinality estimation model further includes generating an additional adjacency matrix that encodes a measure of the selectivity of the join predicates in the join of the training query, encoding one side of the diagonal axis of the additional adjacency matrix into the adjacency matrix of the encoded join factors, and concatenating the adjacency matrix of the encoded join factors with the encoded join graph before training the join cardinality estimation model. Advantageously, this technique enables matrix encoding to account for join selectivity without local predicates and provides indication of whether the number of rows expands, reduces, or remains the same as the Cartesian product of rows in the two tables.

[0019] In some embodiments, the step of constructing a joined cardinality estimation model further includes generating an additional adjacency matrix that encodes whether one side of the values ​​in the joined column is a superset of the other side of the values ​​in the joined column of the training query; encoding one side of the diagonal axis of the additional adjacency matrix into an encoded inclusion factor adjacency matrix; and concatenating the encoded inclusion factor adjacency matrix with an encoded joined graph before training the joined cardinality estimation model. Advantageously, this technique enables matrix encoding for considering the inclusion factors of the join in the query.

[0020] In some embodiments, the step of constructing a join cardinality estimation model further includes generating an additional adjacency matrix that encodes the cardinality of each base table and the selectivity of local predicates on the base tables of the join of the training query; encoding one side of the diagonal axis of the additional adjacency matrix into an encoded node configuration adjacency matrix; and concatenating the encoded node coordinate adjacency matrix with an encoded join graph before training the join cardinality estimation model. Advantageously, this technique allows for matrix encoding to account for other properties of the query that are table properties rather than join properties.

[0021] Embodiments of the present invention are directed to techniques for providing learned join cardinality estimation using a join graph representation. Non-limiting, exemplary methods include generating training queries having known join cardinalities, generating an adjacency matrix that encodes a join graph of the training queries, encoding one side of the diagonal axis of the adjacency matrix, and training the join cardinality estimation model using the encoded join graph and the known join cardinalities. Advantageously, training a join cardinality estimation model in this manner enables an efficient join cardinality estimation mechanism that demonstrates strong generalization capabilities.

[0022] Embodiments of the present invention are directed to techniques for providing learned join cardinality estimation using a join graph representation. Non-limiting, exemplary methods include performing inference using a trained join cardinality estimation model. The inference includes the join cardinality predicted for a query. The performing the inference includes generating an adjacency matrix that encodes a join graph of the query, encoding one side of the diagonal axis into the encoded adjacency matrix, inputting the encoded adjacency matrix into the join cardinality estimation model, and receiving the predicted join cardinality as an output. The method includes executing a query execution plan for the query using the predicted join cardinality. Advantageously, leveraging a trained model for join cardinality estimation in this manner enables an efficient join cardinality estimation mechanism that demonstrates strong generalization capabilities.

[0023] Other embodiments of the present invention implement the features of the above-described methods in a computer system and a computer program product.

[0024] Further technical features and benefits are realized by the techniques of the present invention. Embodiments and aspects of the present invention are described in detail herein and are considered part of the claimed subject matter. For a better understanding, please refer to the detailed description and the drawings.

Brief Description of the Drawings

[0025] The details of the exclusive rights described herein are particularly pointed out and are clearly claimed in the claims at the conclusion of the specification. The foregoing and other features and advantages of embodiments of the present invention will be apparent from the following detailed description when read in conjunction with the accompanying drawings.

[0026] [Figure 1] A block diagram of an exemplary computing environment for use in conjunction with one or more embodiments is shown.

[0027] [Figure 2] A block diagram of a system for training and using a machine learning model for joint cardinality estimation based on a joint graph representation according to one or more embodiments is shown.

[0028] [Figure 3] FIG. 3A shows an exemplary joint graph according to one or more embodiments.

[0029] FIG. 3B shows an exemplary adjacency matrix for the joint graph of FIG. 3A according to one or more embodiments.

[0030] [Figure 4] An exemplary encoding of an adjacency matrix into a one-dimensional vector according to one or more embodiments is shown.

[0031] [Figure 5] An exemplary three-dimensional matrix and its resulting one-dimensional vector according to one or more embodiments are shown.

[0032] [Figure 6]An illustrative table of the ratio of column cardinality to table cardinality for joined columns encoded according to one or more embodiments is shown.

[0033] [Figure 7] An illustrative table of cardinality ratios for the left and right tables of a join encoded according to one or more embodiments is shown.

[0034] [Figure 8] An exemplary table of skewness factors in a joined column encoded according to one or more embodiments is shown.

[0035] [Figure 9] Exemplary node coordinate encodings of join table properties and resulting one-dimensional vectors are shown according to one or more embodiments.

[0036] [Figure 10] This is a flowchart according to one or more embodiments of the present invention.

[0037] [Figure 11] This is a flowchart according to one or more embodiments of the present invention.

[0038] [Figure 12] This is a flowchart according to one or more embodiments of the present invention.

[0039] The figures shown herein are illustrative. Many variations are possible to the figures or the actions described therein without departing from the scope of the invention. For example, the actions may be performed in a different order, or the actions may be added, deleted, or modified.

[0040] In the accompanying figures and the following detailed description of the embodiments described in the present invention, various elements shown in the figures are provided with two or three-digit reference numerals. With few exceptions, the leftmost digit of each reference numeral corresponds to the figure in which the element is first shown. [Modes for carrying out the invention]

[0041] Accurately estimating join cardinality is difficult due to several factors, including unknown or changing data distributions, unknown correlations between join columns, and filters / predicates in queries. For example, the database or query optimizer must make assumptions about the distribution of data in the tables involved in the join. However, the actual data distribution may not be known beforehand or may change over time. A biased data distribution in which some values ​​are far more common than others can significantly affect cardinality estimation. The optimizer must also, in some cases, consider the degree of correlation between multiple join columns. If the values ​​in the join columns are highly correlated, the cardinality of the join result will differ from what would be estimated from a simple multiplication of the selectivity of the individual tables for each join predicate. Accurately determining this correlation is particularly difficult when there are no defined explicit foreign key relationships.

[0042] Other challenges include the presence of filters (predicates) in queries, which adds another layer to the complexity of cardinality estimation. The optimizer must accurately estimate the selectivity of each predicate to predict the number of rows that fit the join. If selectivity is underestimated, the optimizer may choose an inefficient plan that results in excessive intermediate results. In addition, different join algorithms such as nested loop joins, hash joins, and merge joins behave differently depending on the cardinality of the input tables and the resulting cardinality after applying the join predicates. The optimizer must accurately estimate the join cardinality to decide which join algorithm to choose.

[0043] To address the problem of join cardinality estimation, database systems employ various techniques. For example, histograms provide a statistical summary of the data distribution within a column. By analyzing histograms, optimizers can better estimate the number of distinct values ​​and the frequency of each value, which aids in cardinality estimation. Other techniques include sampling, which involves examining a subset of the data to estimate the characteristics of the entire dataset. By analyzing representative samples, optimizers can infer the distribution and correlation of the data, resulting in a more accurate cardinality estimation. Optimizers can also use cost models to assign costs to different query plans based on the estimated cardinality. In these configurations, the optimizer explores different join orders and algorithms, assigns a cost to each plan, and selects the one with the lowest cost. Dynamic programming and iterative algorithms such as genetic algorithms are commonly used to efficiently search the plan space. Database systems can maintain statistics about tables, including histograms of columns, which aids in cardinality estimation. These statistics are collected periodically or on demand based on observed data patterns and correlations, thereby providing valuable information about data distribution and correlations.

[0044] As previously explained, existing combined cardinality estimation techniques use one or both of two common categories: classical and learning-based, to solve combined cardinality estimation problems. Neither classical nor previous learning-based methods yield good accuracy when estimating the cardinality of combined queries. These techniques often rely on simplified assumptions, which results in ineffective cardinality estimations, or they require the construction and training of large models to understand data distributions, data correlations, etc., resulting in long planning times and a lack of generalizability between queries.

[0045] This disclosure introduces a novel method, computing system, and computer program product for providing trained join cardinality estimation using join graph representations. The join graph representation is constructed from two constructs: an adjacency matrix and node coordinates. The adjacency matrix of the join graph is used as a fundamental element for encoding information about the join. Other properties of the query that are properties of the table rather than the join, such as the cardinality of the base table and the selectivity of local predicates to the base table, are encoded as node coordinates. In some embodiments, the training query is encoded into a vector that can be consumed by a machine learning model.

[0046] The combined cardinality estimation architectures that leverage combined graph representations, according to one or more embodiments described herein, offer various technical advantages over previous methods for combined cardinality estimation. In particular, each of the combined graph features incorporated according to one or more embodiments plays a crucial role in enabling the underlying model to estimate the combined cardinality. The combined cardinality estimation architectures described herein are not limited to queries of a specific type of operator or combined type, but can be generalized to any combined graph. Furthermore, the skewness of the predicate sequence is incorporated using skewness coefficients.

[0047] Other advantages are possible. Unlike previous methods, the joined cardinality estimation architecture described herein considers scenarios where the set of column values ​​on one side of the join is not a subset of the column values ​​on the other side. By explicitly incorporating pairwise correlations, the model can account for such correlations while computing the decoding selectivity of local predicates.

[0048] Advantageously, the presented join cardinality estimation architecture leverages an encoding mechanism that demonstrates strong generalization ability. The structure of the join graph, rather than its specific elements, is used by employing abstract statistical information about tables, local predicates, and joins. Therefore, for a new query, join cardinality can be inferred based on the similarity of the join graph and abstract information about the tables and predicates involved.

[0049] Other methods often require collecting combined samples as input to each of those models. Collecting these samples and computing the combined samples is very expensive and unsuitable for production environments. This solution does not require any samples as input to the models for computing the combined cardinality estimate, and is therefore relatively more efficient, resulting in only a small overhead in query optimization time when aggregating the use of models in the query optimizer.

[0050] In some embodiments, the joined cardinality estimation architectures described herein include continuous learning through runtime feedback. Continuous learning through runtime feedback results in the automatic improvement (fitting) of the model over time in terms of runtime changes in schema, data, and workload. Previous methods require ingesting all tables and columns in the database and rebuilding and retraining each model from scratch to account for any changes in the schema. This schema change-induced retraining is very expensive, and the models are rejected for use with statistical databases. Some methods utilize joined samples to help account for potential changes in the data over time, but considering these every time a query is compiled is very expensive compared to the runtime feedback methods described herein, which perform continuous training over time in the background with minimal impact.

[0051] Various aspects of this disclosure are described by explanatory text, flowcharts, block diagrams of computer systems, and / or block diagrams of mechanical logic included in embodiments of computer program products (CPPs). With respect to any flowchart, depending on the technology involved, operations may be performed in a different order than those shown in a given flowchart. For example, again, depending on the technology involved, two operations shown in consecutive blocks of a flowchart may be performed in reverse order, as a single integrated stage, simultaneously, or with at least partial time overlap.

[0052] Embodiments of a computer program product ("CPP Embodiment" or "CPP") are terms used in this disclosure to describe any set of one or more storage media ("mediums") that are collectively included in a set of one or more storage devices that collectively contain machine-readable code corresponding to instructions and / or data for performing computer operations specified in a given CPP claim. A "storage device" is any tangible device capable of holding and storing instructions for use by a computer processor. Computer-readable storage media may be, but are not limited to, electronic storage media, magnetic storage media, optical storage media, electromagnetic storage media, semiconductor storage media, mechanical storage media, or any preferred combination thereof. Some known types of storage devices, including 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 disks (DVDs), memory sticks, floppy disks, mechanically encoded devices (such as pits / lands formed on the main surface of punch cards or disks), or any preferred combination of those described above. Computer-readable storage media, as used in this disclosure, shall not be construed as storage in the form of transient signals themselves, such as radio waves or other freely propagating electromagnetic waves, electromagnetic waves propagating through waveguides, optical pulses passing through optical fiber cables, electrical signals communicated through wires, and / or other transmission media. As those skilled in the art will understand, data is moved at several intermittent points in the normal operation of a storage device, typically during access, defragmentation, or garbage collection; however, data is not transient while it is stored, and therefore a storage device is not transient.

[0053] Referring here to Figure 1, the computing environment 100 includes an example of an environment for executing at least a portion of the computer code involved in carrying out the methods of the present invention, such as a coupled cardinality estimation module 150 (also referred to herein as block 150). In addition to block 150, the computing environment 100 includes, for example, a computer 101, a wide area network (WAN) 102, an end user device (EUD) 103, a remote server 104, a public cloud 105, and a private cloud 106. In this embodiment, the computer 101 has a processor set 110 (including processing circuits 120 and a cache 121), a communication fabric 111, volatile memory 112, persistent storage 113 (including an 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. The remote server 104 includes a remote database 130. The public cloud 105 includes a gateway 140, a cloud orchestration module 141, a host physical machine set 142, a virtual machine set 143, and a container set 144.

[0054] Computer 101 may take the form of a desktop computer, laptop computer, tablet computer, smartphone, smartwatch or other wearable computer, mainframe computer, quantum computer, or any other form of computer or mobile device that is currently known or may be developed in the future, capable of running programs, accessing networks, or querying databases such as remote database 130. As is well understood in the field of computer technology, and depending on the technology, the execution of a computer implementation may be distributed among multiple computers and / or multiple locations. On the other hand, in this description of the computing environment 100, in order to make the explanation as concise as possible, the detailed discussion will focus on a single computer, specifically computer 101. Although computer 101 is not shown in the cloud in Figure 1, it may be located in the cloud. On the other hand, computer 101 does not need to be located in the cloud, except to any extent that may be definitively shown.

[0055] The processor set 110 includes one or more computer processors of any type currently known or to be developed in the future. The processing circuitry 120 may be distributed across multiple packages, for example, multiple interconnected integrated circuit chips. The processing circuitry 120 may implement multiple processor threads and / or multiple processor cores. The cache 121 is memory located within the processor chip package and is typically used for data or code that should be available for high-speed access by threads or cores running on the processor set 110. The cache memory is typically organized into multiple levels depending on its relative proximity to the processing circuitry. Alternatively, some or all of the cache for the processor set may be located "off-chip". In some computing environments, the processor set 110 may operate using qubits and be designed to perform quantum computing.

[0056] Computer-readable program instructions are typically loaded onto computer 101 and cause the processor set 110 of computer 101 to execute a series of operational steps, thereby realizing the computer implementation method. As a result, the instructions thus executed instantiate the methods specified in the flowcharts and / or descriptions of the computer implementation methods contained herein (collectively referred to as the "Methods of the Invention"). 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 the processor set 110 to control and direct the execution of the Methods of the Invention. In computing environment 100, at least some of the instructions for executing the Methods of the Invention may be stored in block 150 in persistent storage 113.

[0057] The communication fabric 111 is a signal conduction path that enables various components of the computer 101 to communicate with one another. Typically, this fabric is made up of switches and conductive paths, such as buses, bridges, physical input / output ports, and similar components. Other types of signal communication paths, such as optical fiber communication paths and / or wireless communication paths, may be used.

[0058] The volatile memory 112 is any type of volatile memory that is currently known or may be developed in the future. Examples include dynamic random-access memory (RAM) or static RAM. Typically, volatile memory is characterized by random access, but this is not mandatory unless explicitly stated. In computer 101, the volatile memory 112 is located in a single package and resides inside computer 101, but alternatively or additionally, the volatile memory may be distributed across multiple packages and / or located externally to computer 101.

[0059] The persistent storage 113 is any form of non-volatile storage for a computer, currently known or to be developed in the future. The non-volatility of this storage means that the stored data is maintained regardless of whether power is supplied to the computer 101 and / or directly to the persistent storage 113. The persistent storage 113 may be read-only memory (ROM), but typically at least a portion of the persistent storage allows for writing, deleting, and rewriting of data. Some well-known forms of persistent storage include magnetic disks and solid-state storage devices. The 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 a portion of computer code involved in performing the method of the present invention.

[0060] The peripheral device set 114 includes a set of peripheral devices for the computer 101. Data communication connections between the computer 101's peripheral devices and other components 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), insert-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, the UI device set 123 may include components such as a display screen, speakers, microphones, wearable devices (such as goggles and smartwatches), keyboards, mice, printers, touchpads, game controllers, and haptic devices. Storage 124 is 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 requires a large amount of storage (for example, when computer 101 locally stores and manages a large database), 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. The IoT sensor set 125 consists of sensors that can be used in Internet of Things applications. For example, one sensor may be a thermometer and another may be a motion detector.

[0061] The network module 115 is a collection of computer software, hardware, and firmware that enables computer 101 to communicate with other computers via the WAN 102. The 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 transmitting data over the internet. In some embodiments, the network control and network forwarding functions of the 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 the 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 carrying out the method of the present invention can typically be downloaded from an external computer or external storage device to computer 101 via a network adapter card or network interface included in the network module 115.

[0062] WAN102 is any wide area network (e.g., the Internet) that can transmit computer data over non-local distances using any technology currently known or to be developed for transmitting computer data. In some embodiments, the WAN may be replaced and / or supplemented by a local area network (LAN), such as a Wi-Fi network, designed to transmit data between devices located in a local area. The WAN and / or LAN typically include computer hardware such as copper transmission cables, optical transmission fibers, wireless transmissions, routers, firewalls, switches, gateway computers, and edge servers.

[0063] The end-user device (EUD) 103 is any computer system used and controlled by an end-user (e.g., a customer of the company operating computer 101), and may take any of the forms described above in relation to computer 101. The EUD 103 typically receives useful and valuable data from the operation of computer 101. For example, in a hypothetical case where computer 101 is designed to provide recommendations to the end-user, these recommendations would typically be transmitted from computer 101's network module 115 to the EUD 103 via the WAN 102. Thus, the EUD 103 can display or otherwise present recommendations to the end-user. In some embodiments, the EUD 103 may be a client device such as a thin client, heavy client, mainframe computer, or desktop computer.

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

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

[0066] Here, some further explanation of virtualized computing environments (VCEs) is provided. A VCE can be stored as an "image." A new active instance of a VCE can be instantiated from an 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 an operating system feature in which the kernel allows for the existence of multiple isolated user-space instances called containers. These isolated user-space instances typically behave as actual computers in terms of the programs running within them. Computer programs running on a normal operating system can utilize all of that computer's resources, including connected devices, files and folders, network shares, CPU power, and quantifiable hardware capabilities. However, programs running inside a container can only use the contents of the container and the devices allocated to the container; this feature is known as containerization.

[0067] The private cloud 106 is similar to the public cloud 105, except that its computing resources are available only for use by a single enterprise. While the private cloud 106 is shown as being in communication with the WAN 102, in other embodiments, the private cloud may be completely isolated from the internet and accessible only via a local / private network. A hybrid cloud is a combination of multiple clouds of different types (e.g., private, community, or public cloud types), often implemented by different vendors. Each of the multiple clouds remains a separate discrete entity, but the larger hybrid cloud architecture is coupled by standardized or proprietary technologies that enable orchestration, management, and / or data / application portability between the multiple configuration clouds. In this embodiment, both the public cloud 105 and the private cloud 106 are part of a larger hybrid cloud.

[0068] It should be understood that the block diagram in Figure 1 is not intended to show that the computing environment 100 includes all the components shown in Figure 1. Rather, the computing environment 100 may include fewer or additional arbitrary components not shown in Figure 1 (e.g., additional memory components, embedded controllers, modules, additional network interfaces, etc.). Furthermore, embodiments of the computing environment 100 described herein may be implemented with any arbitrary logic, and in various embodiments, the logic may include any suitable hardware (e.g., processors, embedded controllers, or application-specific integrated circuits), software (e.g., applications), firmware, or any suitable combination of hardware, software, and firmware, as referred to herein.

[0069] In some embodiments, the coupled cardinality estimation module 150 includes a model trained for coupled cardinality estimation based on a coupled graph representation. A system for training and using a machine learning model for coupled cardinality estimation based on a coupled graph representation is described here with reference to Figure 2. In particular, Figure 2 shows a block diagram of the components of a machine learning training and inference system (e.g., the coupled cardinality estimation module 150) according to one or more embodiments described herein.

[0070] In some embodiments, the combined cardinality estimation module 150 performs training 202 and inference 204. During training 202, the training engine 216 trains a model (e.g., a pre-trained model 218) to perform tasks such as estimating the combined cardinality for a query using a combined graph representation. Inference 204 is the process of implementing the pre-trained model 218 to perform tasks such as estimating the combined cardinality for a query made in the context of a larger system 226 (e.g., the computing environment 100 in Figure 1).

[0071] Training 202 is initiated with training data 212, which may be structured or unstructured data. According to one or more embodiments described herein, the training data 212 includes queries from a query workload. The training engine 216 receives the training data 212 and an untrained model 214. The untrained model 214 represents an untrained (i.e., pre-training) base model. The untrained model 214 has pre-set weights and biases, which may be adjusted during training. It should be understood that the untrained model 214 can be selected from many different model forms, such as a fully connected neural network, a graph neural network, or a gradient boosting tree.

[0072] Training 202 may include supervised learning, semi-supervised learning, unsupervised learning, reinforcement learning, and / or similar methods, including combinations and / or a combination thereof. For example, supervised learning may be used to train a machine learning model from training data with associated labels (i.e., known ground truths) and other data without associated labels. In this example, the training engine 216 takes training queries from the training data 212 as input, makes coupled cardinality predictions, and compares the predictions to known labels (i.e., known coupled cardinality). An algorithm associated with the model being trained then adjusts the model's weights and / or biases based on the results of the comparison, for example, by using backpropagation. Training 202 may be performed multiple times (referred to as “epochs”) until a suitable model (e.g., trained model 218) is trained.

[0073] Once trained, the trained model 218 can be used to perform inference 204 to perform tasks such as estimating combined cardinality, as described earlier. The inference engine 220 applies the trained model 218 to new data 222 (e.g., real-world untrained data). For example, the new data 222 could be queries made during runtime. Briefly, the new data 222 represents data that the trained model 218 has not been exposed to.

[0074] The inference engine 220 makes a prediction 224 (e.g., a join cardinality estimate) and passes the prediction 224 to the system 226 (e.g., the computing environment 100 in Figure 1). Based on the prediction 224, the system 226 can take actions that leverage the prediction. These actions may include, for example, generating and / or executing an efficient query execution plan using the predicted join cardinality (e.g., for a database or query optimizer). In some embodiments, the system 226 may add to and / or modify new data 222 based on the prediction 224.

[0075] 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 operating as expected. Based on the validation, additional training 202 may occur using the trained model 218 as a starting point. 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 expected changes in the input data.

[0076] Generation and labeling of training queries

[0077] In some embodiments, training data 212 may be generated progressively from a database workload. In some embodiments, before any workload trace is collected, the join cardinality estimation module 150 can generate one or more synthetic queries (i.e., training data 212) against the database. These queries include a mix of patterns typically used in production environments. These patterns may be characterized by the number of joins, the number of joins and local predicates, the join type (e.g., inner join, outer join, anti-join), the operators of the joins and local predicates (e.g., <, >, ≥, ≤, =), and the method used to discover the joins. In some embodiments, joins may be discovered primarily based on referential integrity constraints that determine the relationships between the respective fact tables and dimension tables. If the joins discovered in this manner are insufficient, the join cardinality estimation module 150 may discover additional joins and patterns for training using other techniques, including but not limited to data mining, schema relationships (e.g., ontology), column name similarity, value similarity, and actual query workload mining. In some embodiments, the patterns used for query generation can be controlled and refined based on empirical data (e.g., past experience regarding the characteristics of historical database workloads).

[0078] Encoding queries using join graph representations

[0079] Once a training query is generated, the query can be encoded into a vector that can be consumed by a machine learning model (e.g., a trained model 218 via a training engine 216). In some embodiments, these vectors are constructed using two components related to the query join graph: namely, an adjacency matrix and node coordinates. A query may have multiple query blocks separated by non-joining operators, such as UNION or aggregating (GROUP BY) operators. In some embodiments, joins within each query block are encoded separately if they cannot be merged into a single joined query block.

[0080] Joint features encoded as edges of the adjacency matrix

[0081] In some embodiments, the adjacency matrix of a join graph is used as a fundamental element for encoding information about the join. For a given query that joins a set of n tables, the adjacency matrix is ​​an n × n matrix AM n×n It can be defined as:

number

[0082] As an example, we consider a query that includes the exemplary join graph 300 shown in Figure 3A, where T N These represent the different tables involved in the join. An exemplary adjacency matrix 350 of the join graph 300 may be defined as shown in Figure 3B.

[0083] In some embodiments, the adjacency matrix (see Figure 3B) is symmetric, assuming the connected graph is undirected. It is observed that one side of the diagonal axis holds as much information as the entire matrix. Some models, such as fully connected neural network models, receive input in the form of one-dimensional vectors. In this case, the adjacency matrix can be condensed into one-dimensional vectors representing one side of the diagonal axis, as shown in Figure 4. In this manner, it is observed that an n×n matrix is ​​condensed into a vector of size [n(n-1)] / 2. Conversely, in the case of graph neural network models, the input does not need to be represented as one-dimensional vectors, and the matrix structure can be preserved. All such configurations are assumed to be within the scope intended of this disclosure.

[0084] In some embodiments, for self-joins, a separate placeholder may be used for the second instance of the table. For example, if the query is a single self-join on a table, the query is encoded using a 2x2 adjacency matrix, where each instance of the table occupies one of two positions. In some embodiments, this general schema is then used to encode a different number of pieces of information about each join, which are essential for the model to learn the cardinality of the results, as will be discussed in more detail herein.

[0085] Joint type (JT)

[0086] In some embodiments, the join type (e.g., internal join, external join, and anti-joint) is represented using an array at the location of each element in the adjacency matrix, and the three-dimensional matrix JT n×n×mThis is derived, where n is the number of tables to be included and m is the number of join types supported by the Model Plus One. In other words, at each position of the n x n matrix (constructed as discussed earlier), there is an array of size m that defines the one-hot encoding of the join type, which is extended with an additional element ("Plus One") that represents the order of the joins. Note that this last element is necessary because the relative order of inner joins with respect to outer joins or anti-joins can affect the cardinality of the outer joins or anti-joins. Therefore, this can be indicated by an order encoding at the last position of the array corresponding to that join. For example, consider a scenario with the following join features: (T1 inner join T2 ON T1.C1=T2.C1) left outer join (T3 anti-join T4 ON T3.C2=T4.C2) ON T1.C3=T3.C3. A T1-joint T2 can be encoded as (1,0,0,1), a T3-antijoint T4 as (0,0,1,2), and a left outer join as (0,1,0,3). In some embodiments, as discussed earlier, if a model is used that requires a one-dimensional vector representing one side of the diagonal axis, this three-dimensional matrix is ​​then condensed into a one-dimensional vector of size [mn(n-1)] / 2. An exemplary three-dimensional matrix 500 and the resulting one-dimensional vector 550 are shown in Figure 5.

[0087] Join operator (JO)

[0088] In some embodiments, each join in a query may be accompanied by one or more join predicates that relate different columns on the two sides of the join. Each join predicate relates a pair of columns using operators (e.g., <, >, ≤, ≥, =, LIKE, IN). In some embodiments, operators are represented by bit vectors. Assuming that a model (e.g., trained model 218) supports seven operators, the corresponding encoding would be a bit vector of size 7, where the value 1 at each position corresponds to the presence of a particular operator in the join. For example, a join with the "<" operator may be represented by [1 0 0 0 0 0 0]. Joins with one or more join operators can also be encoded in the same way. For example, a join with the "<" and "=" operators may be represented by [1 0 0 0 1 0 0]. Similar to the join types, these bit vectors are represented by the three-dimensional adjacency matrix JO n×n×k The matrix is ​​stacked in such a way that k is the number of supported operators. As discussed earlier, if we use a model that requires a one-dimensional vector, this matrix can be condensed into a one-dimensional vector of size [kn(n-1)] / 2.

[0089] Ratio of join column cardinality to table cardinality (CC)

[0090] The ratio of the cardinality of the join column to the table cardinality on each side of the join provides a signal about the uniqueness of the join column. This value ranges between zero and one, with values ​​closer to zero indicating fewer unique values ​​in the join column, while values ​​closer to one indicate a unique or nearly unique join column. This is a contributing factor to the resulting cardinality. Therefore, in some embodiments, this information is calculated and collected at compile time and included in the encoding.

[0091] In one embodiment, the calculation is performed based on statistics available for different tables and columns: table cardinality, column cardinality (COLCARD), and column group cardinality (COMBINED_COLCARD). If a particular join contains one or more join predicates, where at least one side of the join contains one or more columns, then the COMBINED_COLCARD of those columns is used in the numerator. Otherwise, the column cardinality of the single column contained is used. The cardinality ratio may also be calculated using other methods, including but not limited to on-the-fly sampling, trained models (e.g., trained model 218 and / or another model), or other statistics such as multi-column histograms. In some embodiments, once calculated, the three-dimensional adjacency matrix CC n×n×2 Using this, the ratios on both sides of the join are encoded, which is then condensed into a one-dimensional vector of size n(n-1), as explained in the eye. Figure 6 shows the adjacency matrix and the joined column (T) encoded using the resulting one-dimensional vector 650. i .A,T j Table 600 is shown as an example of the ratio of column cardinality to table cardinality for .A).

[0092] Table cardinality (RC) ratio

[0093] The ratio of the table sizes on the two sides of a join is another piece of information that can be an important contributing factor to the resulting cardinality. In some embodiments, this information is calculated and collected at compile time based on statistics available in the optimizer. The calculated ratio is the adjacency matrix CR n×n Encoded using this, which then, for example, when using a model that requires a one-dimensional vector, as explained earlier, size

number

[0094] Skewness of the join column (SK)

[0095] The values of the join column after applying the local predicate may exhibit different levels of skewness. Joins on a uniformly distributed join column are expected to have a different impact on cardinality compared to joins on a skewed column. In some embodiments, a measure of skewness may be calculated for each join column after applying a local predicate to a sample of values from the original table. Alternatively, a skewness factor may be learned by a supervised learning model (e.g., the trained model 218 and / or another model). The "Gini coefficient" is such a measure that may represent a skewness factor compressed into a single number between zero and one. A value closer to zero indicates perfect equality (uniformity), while a value closer to one indicates perfect inequality or skewness.

[0096] In some embodiments, when at least one side of the join includes more than one column, the skewness factor may be calculated for the combined values in the included columns. This measure has been shown to be effective in capturing the skewness factor, but other measures of skewness may be selected to encode the pattern. Two values corresponding to the skewness of the join columns on each side of the join are encoded into the adjacency matrix SK n×n×2 which is then condensed into a one-dimensional vector of size n(n - 1) as discussed previously. FIG. 8 shows an exemplary table 800 of the skewness factor (i.e., selectivity of the most frequent value) on the join columns (T i .A, T j .A) encoded using the adjacency matrix and the resulting one-dimensional vector 850 if required for input to the model.

[0097] Join factor (JF)

[0098] Some behavioral profiles of joins (e.g., having a very large number of dimension column values ​​versus a very small number of fact column values, the presence of overloaded dimensions, expansion in many-to-many joins, etc.) can be captured by computing the selectivity of the join predicate from a sample of the tables involved. This may be called the join factor. In some embodiments, the join factor may be computed in three steps. First, compute the result of the join from the sample (this is "C1"). Next, compute the Cartesian product based on the cardinality of the samples on both sides of the join (this is "C2"). Finally, compute the decoded join factor as C1 / C2.

[0099] In some embodiments, the join factor information is captured offline and stored as part of the database statistics for the join predicates. When a given join has multiple join predicates, the join factors may be captured separately for each join predicate. In some embodiments, the minimum, mean, and maximum join factors per join are calculated. Thus, in some embodiments, each join has a three-dimensional adjacency matrix JF n×n×3 There will be three values ​​for the binding factors that can be incorporated, and these will then be, as discussed earlier, size if necessary.

number

[0100] Inclusion factor (IF)

[0101] Whether one side of a combination is a superset of the other is a contributing factor in the resulting combination cardinality estimate. In some embodiments, the combination cardinality estimation module 150 incorporates this information, which is referred to as the inclusion factor. In some embodiments, the inclusion factor can be determined in four steps. First, the number of unique values ​​in the combination column is taken from either side of the combination from the sample. Next, the number of matching values ​​between the two sets is calculated. Then, the size of the Cartesian product of the two sets is calculated. Finally, the ratio of the number of matching values ​​by the size of the Cartesian product is calculated to obtain the inclusion factor.

[0102] In some embodiments, inclusion factor information is captured offline and stored as part of the database statistics for the join predicates. When a given join has multiple join predicates, the inclusion factors may be captured separately for each join predicate. In some embodiments, the minimum, mean, and maximum inclusion factors per join can be calculated. Thus, each join is a three-dimensional adjacency matrix IF. n×n×3 The inclusion factors that can be incorporated in this will have three values, which, as discussed earlier, will then be the size if necessary.

number

[0103] Node coordinates (NC)

[0104] Base table cardinality and local predicate selectivity

[0105] In some embodiments, other properties of the query, which are table properties rather than join properties, may be encoded as node coordinates. These properties include the cardinality of the base table, as well as the selectivity of local predicates to the base table. In some embodiments, this information is used in predicting the cardinality obtained from the result of the join query.

[0106] Table cardinality can typically be obtained from statistics available in relational database management systems (RDBMS). Local predicate selectivity can be estimated either by applying the local predicate to a sample of the base table, or by any other means used in the optimizer to estimate the local predicate selectivity, such as a histogram or selectivity model. Figure 9 shows an exemplary node coordinate encoding 900 for the table properties and the resulting one-dimensional vector 950. As shown in Figure 9, the node coordinates 910 are represented as a tuple (a, b), where a represents the base table cardinality before applying the local predicate and b represents the base table cardinality after applying the local predicate. The node coordinates 910 can be condensed into a one-dimensional vector of size 2n, if necessary, as discussed previously.

[0107] Correlation of local predicates

[0108] When multiple local predicates are applied to a given table, if the model estimates composite selectivity, it is observed that correlations between predicate columns are implicitly incorporated by the model. If an accurate estimate of composite selectivity is not available, pair correlations may be explicitly incorporated as part of the encoding in some embodiments. These correlates may be incorporated offline and stored as part of the database statistics. If multiple predicates exist, the correlations of all pairs are loaded from the statistics and aggregated by taking the minimum, mean, and maximum correlations. In some embodiments, these three features are incorporated as additional node coordinates in the combined graph representation.

[0109] Encoding of join graphs

[0110] In some embodiments, the above pieces (e.g., join type (JT), join operator (JO), ratio of join column cardinality to table cardinality (CC), ratio of table cardinality (RC), skewness of join column (SK), join factor (JF), inclusion factor (IF), and node coordinates (NC)) may be computed and collected in parallel to reduce compilation overhead. In some embodiments, the calculation is performed for the entire join graph with each query compilation, because it is independent of the selection and transformation of plans evaluated by the production query optimizer during the process of finding the optimal query execution plan.

[0111] In some embodiments, once a number of different encodings (e.g., JT, JO, CC, CR, SK, JF, IF, and NC) are prepared as one-dimensional vectors, they can be concatenated together to form the final encoding for the query.

number

[0112] Other applications

[0113] The join graph representation is proposed here for constructing a join cardinality model, but it is not limited to this application. This representation can be used for any model that requires information about a query as input. For example, it can be used for join planning models where the best join order or best execution plan must be predicted for a given query.

[0114] Label preprocessing

[0115] Cardinality labels have a wide range of values ​​and typically exhibit high skewness. Therefore, to reduce skewness and make them a more suitable target for machine learning models to learn, a logarithmic transformation is applied to the labels. This is done using the following formula, where label_log represents the transformed label. label log =log 10 (Cardinity)

[0116] Logarithmic labels have a distribution closer to normal. However, the range of values ​​they take is still unsuitable for deep learning models that best predict values ​​between zero and one. Therefore, logarithmic labels are transformed using minimum-maximum scaling according to the following equation:

number

[0117] Input preprocessing

[0118] The encoded query input values ​​are also transformed using minimum-maximum scaling, so that all input values ​​take a range between zero and one. This is essential to help machine learning models converge more quickly. This is done by the following equation:

number

[0119] Multiple specialized models

[0120] While a single model can efficiently learn to predict the cardinality of different classes of queries, it is also possible to train multiple models, each specialized for a particular class. Query classes are determined by the patterns they exhibit, such as query graph patterns (linear, stellar, periodic, etc.), the presence of multiple predicates per join, or the presence of self-joins. Each class of query exhibits different behavior and can result in different distributions of cardinality. Therefore, each model can be specialized to predict the cardinality of a specific class. Specialized models can make more accurate predictions at the expense of relatively high training overhead. Assuming the model training phase is performed offline and not in the critical path of query execution, the overhead is acceptable in most environments.

[0121] Model architecture, tuning, and activation

[0122] Depending on the complexity of the workload and the size of the training dataset, neural networks with various architectures can provide optimal performance. This includes various combinations of the number of hidden layers (network depth), the number of neurons in each layer (network width), and the activation functions of the hidden and output layers. Various objective functions can be used to train the model. The mean squared error is an effective objective function for this use case.

[0123] Parameters found to be effective during the development phase are supplied as default configurations, while these values ​​can also be automatically adjusted at the customer site to suit their specific workload characteristics. This can be done by performing a grid search on parameter combinations to find the combination that has the minimum value of the objective function for the enable set. In addition, early stopping techniques are used to avoid overfitting.

[0124] Model Evaluation

[0125] The model's accuracy is measured using the q-error, which is the de facto standard for measuring the accuracy of cardinality estimation. The q-error is calculated as follows:

number

[0126] The accuracy of each model is measured and compared on the activation set, and those that exceed a certain threshold are activated for use. If the threshold criteria are not met, the results are analyzed to determine which classes of queries are being negatively impacted by suboptimal performance. This leads to targeted query generation, which increases the training data by using more queries from those classes. In some embodiments, the model training process is repeated until the performance criteria are met.

[0127] Input for inference

[0128] During inference time (e.g., during inference 204), the information necessary for encoding can be obtained from various sources. The sizes of tables, columns, and groups of columns can be collected either through statistics typically available in the RDBMS, or through external and / or internal modules (e.g., the joined cardinality estimation module 150) that periodically monitor and incorporate statistics. The selectivity of local predicates can be obtained either from estimation methods available in the optimizer, such as selectivity models or histograms, or from applying local predicates to table samples, or from a feedback warehouse maintained based on the actual runtime. The same applies to the calculation or estimation of skewness of joined columns as described earlier.

[0129] In some embodiments, input preprocessing is performed based on min(Input) and max(Input) arrays collected and stored during the training phase, as previously described.

[0130] Calling the model

[0131] Depending on which class the query belongs to, the preprocessed input data is fed to the corresponding model and the target value is calculated. The model is invoked during the query optimization phase of the query compilation, during which the optimizer enumerates various permutations of join orders. For each of these join orders, the optimizer invokes the model with the associated encoding for that join order. The optimizer may enumerate different permutations of join orders for the same set (or subset) of joins, but the resulting cardinality for each join order is the same. To minimize any overhead of repeatedly constructing input encodings and / or invoking models, the prediction results may be computed once for each join combination and cached for later reuse.

[0132] Inverse transformation of prediction

[0133] The model predicts a transformed version of the cardinality value, so it must be inversely transformed before it can be used in the optimizer. This inverse transformation involves two stages: inverse minimum-maximum scaling and inverse logarithmic transformation.

number

number

[0134] Continuous learning from runtime feedback

[0135] In some embodiments, the model's performance is continuously measured and monitored for incoming queries. If the prediction accuracy does not meet the performance criteria, the corresponding query is analyzed and the class to which the query belongs is identified. This leads to the generation of queries exhibiting the same pattern. New queries are encoded and added to the training data, which is used to train future models. Runtime feedback can also be used to encode more accurate information to obtain better predictions from existing models.

[0136] Alternative methods for handling various types of coupling

[0137] Alternatively, or in addition, the join cardinality models previously described herein may be trained using queries that contain only internal joins. Predictions from such models may be used to make estimates for other types of joins. This means that, in representation, the join type encodings previously described are excluded (since all joins are internal joins by default).

[0138] In some embodiments, the cardinality calculation for left outer-joins (LOJ), right outer-joins (ROJ), and full outer-joins (FOJ) is performed as follows: Firstly, LOJ = IJ + LAJ; secondly, ROJ = IJ + RAJ; and finally, FOJ = IJ + LAJ + RAJ, where LAJ represents a left anti-join and RAJ represents a left anti-join.

[0139] The calculation of anti-connections can be performed in several ways. The first method is to use the simplification assumptions that some optimizers today make when calculating such connections. The second method requires the model to predict multiple statistics for a given node. Specifically, the model will predict not only the cardinality of the inner connection, but also the filter factor of the inner stream (inner_ff) and the filter factor of the outer stream (outer_ff). These two allow the estimation of LAJ and RAJ as follows: firstly, LAJ = (1 - outer_ff) * cart_card; secondly, RAJ = (1 - inner_ff) * cart_card, where cart_card represents the cardinality of the Cartesian product of the inner and outer streams. To simplify this further, the model may be designed in some embodiments to predict only outer_ff. Then, the cardinality of RAJ is estimated using outer_ff by converting RAJ ​​in the input to LAJ. A third method is to have an anti-join model for at least the base table of pairs having suitable local predicates.

[0140] Limiting the model to internal joins only has two main advantages: reduced encoding size and therefore model complexity, resulting in better accuracy and generalizability; and relatively lower model training costs because fewer data points are required. Such a method is expected to give better estimates for internal joins, while its accuracy for external and anti-joints depends on good estimates for inner_ff and outer_ff. Whether to include all join types or train the model only with internal joins can be decided on a case-by-case basis, depending on how the two methods compare for a given database.

[0141] Performance results and comparison with previous models

[0142] The join cardinality estimation architecture described herein (which leverages join graph representations) builds upon previous methods for join cardinality estimation, such as the so-called query graph representation. Advantageously, this architecture can generalize initial joins and further generalize and extrapolate to more joins (e.g., extrapolating from one to four processed joins to five or more findings).

[0143] The coupled cardinality estimation architecture described herein, when trained on four coupled combinations, has been found to extrapolate to 14 coupleds with minimal error (specifically, a log-predict / actual ratio of less than 7.5), which far surpasses conventional results. This model is often superior to conventional solutions for estimating coupled cardinality because the model described herein is specifically specialized for coupleds, thereby reducing training time, model size, and coupled cardinality estimation error. Furthermore, this solution can work in conjunction with conventional query graph representation-based methods, where the previous architecture is used for the uncoupled portion of the query graph, while models constructed according to one or more embodiments described herein can be used to incorporate coupleds and to provide conventional models with inputs representing coupleds as derived results input to those models.

[0144] In summary, the coupled cardinality estimation architecture described herein provides an efficient model trained using relatively smaller encodings (compared to previous architectures that are not coupled cardinality-specific models), thereby resulting in a compact model with fewer parameters. Consequently, the model size can be much smaller, leading to faster inference (i.e., prediction execution). Furthermore, since training time is proportional to the size of the encodings and the complexity of the model (model parameters), the smaller encodings given to the models constructed as described herein, when combined with the simplified models, translate directly into a training regime that is significantly (orders of magnitude) faster.

[0145] Referring here to Figure 10, a flowchart 1000 for providing a trained coupled cardinality estimation using a coupled graph representation according to one embodiment is generally shown. Flowchart 1000 is described by reference to Figures 1-9 and may include additional blocks not shown in Figure 10. Although shown in a particular order, the blocks shown in Figure 10 may be rearranged, split, and / or combined. In an exemplary embodiment, method 1000 may be implemented by a computing environment (e.g., computing environment 100 shown in Figure 1).

[0146] The method includes a step for constructing a coupled cardinality estimation model. In block 1002, the method includes a step for generating training queries with known coupled cardinality. In block 1004, the method includes a step for generating an adjacency matrix that encodes the coupled graph of the training queries. In block 1006, the method includes a step for condensing the adjacency matrix into a one-dimensional vector that encodes one side of the diagonal axis of the adjacency matrix. In block 1008, the method includes a step for training the coupled cardinality estimation model using the one-dimensional vector and the known coupled cardinality, if appropriate for the model.

[0147] In block 1010, the method includes a step of performing inference using a joined cardinality estimation model. The inference includes the predicted joined cardinality for the query.

[0148] In block 1012, the method includes a step of executing a query execution plan for the query using the predicted join cardinality.

[0149] Referring here to Figure 11, a flowchart 1100 for training a coupled cardinality estimation model according to one embodiment is generally shown. Flowchart 1100 is described with reference to Figures 1-9 and may include additional blocks not shown in Figure 11. Although shown in a particular order, the blocks shown in Figure 11 may be rearranged, split, and / or combined. In an exemplary embodiment, method 1100 may be carried out by a computing environment (e.g., computing environment 100 shown in Figure 1).

[0150] In block 1102, the method includes a step of generating training queries with known coupled cardinality. In block 1104, the method includes a step of generating an adjacency matrix that encodes the coupled graph of the training queries. In block 1106, the method includes a step of condensing the adjacency matrix into a one-dimensional vector that encodes one side of the diagonal axis of the adjacency matrix. In block 1108, the method includes a step of training a coupled cardinality estimation model using the one-dimensional vector and the known coupled cardinality, if appropriate for the model.

[0151] Referring here to Figure 12, a flowchart 1200 for utilizing a coupled cardinality estimation model for inference, according to one embodiment, is generally shown. Flowchart 1200 is described with reference to Figures 1-9 and may include additional blocks not shown in Figure 12. Although shown in a particular order, the blocks shown in Figure 12 may be rearranged, split, and / or combined. In an exemplary embodiment, method 1200 may be implemented by a computing environment (e.g., computing environment 100 shown in Figure 1).

[0152] In block 1202, the method includes a step of performing inference using a trained coupled cardinality estimation model. The inference includes the coupled cardinality predicted for the query.

[0153] In block 1204, the method includes a step of generating an adjacency matrix that encodes the combined graph of the training queries. In block 1206, the method includes a step of condensing the adjacency matrix into a one-dimensional vector where appropriate for the model and encoding one side of the diagonal axis of the adjacency matrix. In block 1208, the method includes a step of inputting the encoded combined graph into a combined cardinality estimation model. In block 1210, the method includes a step of receiving the predicted combined cardinality as output.

[0154] In block 1212, the method includes a step of executing a query execution plan for the query using the predicted join cardinality.

[0155] Various embodiments of the present invention are described herein with reference to the relevant drawings. Alternative embodiments of the present invention can be devised without departing from the scope of the invention. Various connection and positional relationships (e.g., above, below, adjacent, etc.) are described between elements in the following description and drawings. These connections and / or positional relationships are direct or indirect unless otherwise specified, and the present invention is not intended to be limiting in this respect. Thus, the connection between entities can refer to either a direct or indirect connection, and the positional relationship between entities can be a direct or indirect positional relationship. Furthermore, the various tasks and process steps described herein can be incorporated into additional steps or more comprehensive procedures or processes having functions not described in detail herein.

[0156] One or more of the methods described herein can be implemented using any or a combination of the following technologies: discrete logic circuits having logic gates for implementing logic functions for data signals, application-specific integrated circuits (ASICs) having appropriate combinational logic gates, programmable gate arrays (PGAs), field-programmable gate arrays (FPGAs), and each of these technologies is well known in the art.

[0157] For the sake of brevity, prior art relating to the creation and use of embodiments of the present invention may or may not be described in detail herein. In particular, various embodiments of computing systems and specific computer programs for implementing the various technical features described herein are well known. Accordingly, for the sake of brevity, many prior implementation details are mentioned only briefly herein, or are omitted entirely without providing details of well known systems and / or processes.

[0158] In some embodiments, various functions or operations may be performed at a given location and / or in connection with the operation of one or more devices or systems. In some embodiments, a portion of a given function or operation may be performed at a first device or location, and the remainder of the function or operation may be performed at one or more additional devices or locations.

[0159] The technical terms used herein are intended solely to describe specific embodiments and are not intended to limit them. Where used herein, the singular forms "a," "an," and "the" are intended to include the plural forms unless otherwise clearly indicated by the context. Where used herein, the terms "comprises" and / or "comprising" specify the presence of the described features, integers, stages, actions, elements, and / or components, but do not exclude the presence or addition of one or more other features, integers, stages, actions, element components, and / or groups thereof.

[0160] The corresponding structures, materials, actions, and equivalents of any means-plus-function element or step-plus-function element in the following claims are intended to include any structures, materials, or actions for performing a function in combination with other specifically claimed elements. This disclosure is presented for illustrative and explanatory purposes, but is not intended to be comprehensive or to limit oneself to the disclosed forms. Many modifications and variations will be apparent to those skilled in the art without departing from the scope of this disclosure. The embodiments have been selected and described to best illustrate the principles and practical applications of this disclosure and to enable those skilled in the art to understand this disclosure in terms of various embodiments with various modifications suited to specific intended uses.

[0161] The diagrams shown herein are illustrative. Many variations are possible in the diagrams or steps (or actions) described herein without departing from the scope of this disclosure. For example, actions may be performed in a different order, or actions may be added, deleted, or modified. Furthermore, the term “coupled” indicates that there is a signaling path between two elements, and does not imply a direct connection between elements that do not have an intervening element / connection between them. All of these variations are considered part of this disclosure.

[0162] The following definitions and abbreviations are used for the purposes of the claims and interpretation of this specification. Where used herein, the terms “comprises,” “comprising,” “includes,” “including,” “has,” “having,” “contains,” or “containing,” or any other variation thereof, are intended to cover non-exclusive inclusion. For example, a composition, mixture, process, method, article, or apparatus containing a list of elements is not necessarily limited to those elements alone, and may include other elements not expressly listed or that are inherent to such composition, mixture, process, method, article, or apparatus.

[0163] Furthermore, the term “exemplary” is used herein to mean “serving as an example, instance, or illustration.” Any embodiment or design described herein as “exemplary” is not necessarily construed to be preferable or advantageous to other embodiments or designs. The terms “at least one” and “one or more” may be understood to include any integer greater than or equal to 1, i.e., 1, 2, 3, 4, etc. The term “multiple” is understood to include any integer greater than or equal to 2, i.e., 2, 3, 4, 5, etc. The term “connection” may include both indirect and direct “connections.”

[0164] The terms “about,” “substantially,” and “approximately,” and their variations, are intended to include the degree of error associated with measurements of a particular quantity based on equipment available at the time of filing this application. For example, “about” may include a range of ±8%, 5%, or 2% of a given value.

[0165] The present invention may be an integrated system, method, and / or computer program product at any possible level of technical detail. The computer program product may include a computer-readable storage medium (or a plurality of computer-readable storage media) having computer-readable program instructions for causing a processor to perform aspects of the present invention.

[0166] The computer-readable program instructions described herein may be downloaded from a computer-readable storage medium to each computing / processing device, or to an external computer or external storage device via a network, such as the Internet, a local area network, a wide area network, and / or a wireless network. The network may include copper transmission cables, optical transmission fibers, wireless transmissions, routers, firewalls, switches, gateway computers, and / or edge servers. A network adapter card or network interface within each computing / processing device receives computer-readable program instructions from the network and transfers them for storage in a computer-readable storage medium within each computing / processing device.

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

[0168] Aspects of the present invention are described herein with reference to flowcharts and / or block diagrams of methods, apparatuses (systems) and computer program products according to embodiments of the present invention. It will be understood that each block in the flowcharts and / or block diagrams, and combinations of blocks in the flowcharts and / or block diagrams, can be implemented by computer-readable program instructions.

[0169] These computer-readable program instructions may be provided to the processor of a general-purpose computer, a dedicated computer, or other programmable data processing device to generate a machine, thereby creating means for implementing functions / operations specified in one or more blocks of a flowchart and / or block diagram, through which instructions executed via the processor of the computer or other programmable data processing device. These computer-readable program instructions may also be stored in a computer-readable storage medium on which the instructions are stored, which can instruct computers, programmable data processing devices, and / or other devices to function in a particular manner, such that the storage medium on which the instructions are stored has a product containing instructions that implements modes of functions / operations specified in one or more blocks of a flowchart and / or block diagram.

[0170] Computer-readable program instructions may also be loaded into a computer, other programmable data processing device, or other device to perform a series of operational steps on the computer, other programmable device, or other device, thereby generating a computer implementation process in which the instructions executed on the computer, other programmable device, or other device implement the functions / operations specified in one or more blocks of a flowchart and / or block diagram.

[0171] 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 the flowchart or block diagram may represent a module, segment, or portion of instructions containing one or more executable instructions for implementing a specified logical function. In some alternative implementations, the functions described in a block may be performed in a different order than shown in the figure. For example, two blocks shown consecutively may actually be executed substantially simultaneously, or blocks may be executed in reverse order depending on the functions involved. It should also be noted that each block in the block diagram and / or flowchart, and combinations of blocks in the block diagram and / or flowchart, may be implemented by a dedicated hardware-based system that performs a specified function or operation, or implements a combination of dedicated hardware and computer instructions.

[0172] The descriptions of various embodiments of the present invention are presented for illustrative purposes only and are not intended to be comprehensive or limitless to the disclosed embodiments. Many modifications and variations will become 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 describe the principles, practical applications, or technical improvements to the technologies available on the market, or to enable other those skilled in the art to understand the embodiments described herein.

Claims

1. The stage of generating training queries with known join cardinality; The step of generating an adjacency matrix that encodes the join graph of the aforementioned training query; The step of encoding one side of the diagonal axis of the adjacency matrix; and Step 1: Train a coupled cardinality estimation model using the encoded adjacency matrix and the known coupled cardinality. The step of constructing the aforementioned combined cardinality estimation model; The step of performing inference using the aforementioned joined cardinality estimation model, wherein the inference includes the predicted joined cardinality for the query; and The step of executing a query execution plan for the query using the predicted join cardinality. A computer implementation method comprising the following features.

2. The step of carrying out the aforementioned reasoning is: The step of generating an adjacency matrix that encodes the join graph of the aforementioned query; The step of encoding one side of the diagonal axis of the adjacency matrix; The step of inputting the encoded adjacency matrix into the combined cardinality estimation model; and The step of receiving the predicted coupling cardinality as an output. A computer implementation method according to claim 1, comprising:

3. A computer implementation method according to claim 1 or claim 2, wherein, for a given query that joins a set of n tables, each of the adjacency matrices is defined as an n × n matrix, where the value at position (i, j) in the matrix is ​​1 if table i is joined with table j, and 0 otherwise.

4. The computer implementation method according to claim 3, wherein the step of encoding the adjacency matrix includes a step of condensing the adjacency matrix into a one-dimensional vector for a machine learning algorithm that requires a vector input.

5. The step of constructing the aforementioned coupled cardinality estimation model is: A step of generating an additional adjacency matrix that encodes the join type of the training query; The step of encoding one side of the diagonal axis of the additional adjacency matrix into a join-type adjacency matrix; and Before training the aforementioned bond cardinality estimation model, the step of concatenating the adjacency matrix of the encoded bond type with the encoded bond graph. A computer implementation method according to any immediately preceding claim, further comprising:

6. The step of constructing the aforementioned coupled cardinality estimation model is: A step of generating an additional adjacency matrix that encodes the join operator of the training query; The step of encoding one side of the diagonal axis of the additional adjacency matrix into the adjacency matrix of the join operator; and Before training the bond cardinality estimation model, the step of concatenating the adjacency matrix of the encoded bond operator with the encoded bond graph. A computer implementation method according to any immediately preceding claim, further comprising:

7. The step of constructing the aforementioned coupled cardinality estimation model is: A step of generating an additional adjacency matrix that encodes the ratio of the cardinality of the join column to the table cardinality on each side of the join of the training query; The step of encoding one side of the diagonal axis of the additional adjacency matrix into the adjacency matrix of the column-pair table; and Before training the combined cardinality estimation model, the step of concatenating the adjacency matrix of the encoded column-pair table with the encoded combined graph. A computer implementation method according to any immediately preceding claim, further comprising:

8. The step of constructing the aforementioned coupled cardinality estimation model is: A step of generating an additional adjacency matrix that encodes the cardinality ratio of the left and right tables of the join of the training query; The step of encoding one side of the diagonal axis of the additional adjacency matrix into a table cardinality adjacency matrix; and Before training the combined cardinality estimation model, the step involves concatenating the adjacency matrix of the encoded table cardinality with the encoded combined graph. A computer implementation method according to any immediately preceding claim, further comprising the following:

9. The step of constructing the aforementioned coupled cardinality estimation model is: A step of generating an additional adjacency matrix that encodes a measure of skewness for each join column of the join of the training query; The step of encoding one side of the diagonal axis of the additional adjacency matrix into the skewness adjacency matrix; and The step of training the combined cardinality estimation model involves concatenating the encoded skewness adjacency matrix with the encoded combined graph. A computer implementation method according to any immediately preceding claim, further comprising:

10. The step of constructing the aforementioned coupled cardinality estimation model is: A step of generating an additional adjacency matrix that encodes a measure of join selectivity for joins that do not contain local predicates of the aforementioned training query; The step of encoding one side of the diagonal axis of the additional adjacency matrix into the adjacency matrix of the binding factor; and Before training the coupling cardinality estimation model, the step of concatenating the adjacency matrix of the encoded coupling factors with the encoded coupling graph. A computer implementation method according to any immediately preceding claim, further comprising:

11. The step of constructing the aforementioned coupled cardinality estimation model is: A step of generating an additional adjacency matrix that encodes the inclusion metric on one side of the values ​​in the joined column of the training query, on the other side of the values ​​in the joined column; The step of encoding one side of the diagonal axis of the additional adjacency matrix into the adjacency matrix of the inclusion factor; and Before training the combined cardinality estimation model, the step of concatenating the encoded adjacency matrix of inclusion factors with the encoded combined graph. A computer implementation method according to any immediately preceding claim, further comprising:

12. The step of constructing the aforementioned coupled cardinality estimation model is: A step of generating an additional adjacency matrix that encodes the cardinality of each base table in the join of the training query, and the selectivity of the local predicates to the base tables; The step of encoding one side of the diagonal axis of the additional adjacency matrix into the adjacency matrix of node coordinates; and Before training the combined cardinality estimation model, the step of concatenating the adjacency matrix of the encoded node coordinates with the encoded combined graph. A computer implementation method according to any immediately preceding claim, further comprising:

13. The step of carrying out the aforementioned reasoning is: The step of inputting the encoded adjacency matrix into the combined cardinality estimation model; and The step of receiving the predicted coupling cardinality as an output. A computer implementation method according to any immediately preceding claim, having the following:

14. The step of executing a query execution plan for the query using the predicted join cardinality. The computer implementation method according to claim 13, comprising:

15. A system having memory, computer-readable instructions, and one or more processors for executing the computer-readable instructions, wherein the computer-readable instructions are: Procedure for generating training queries with known join cardinality; A procedure for generating an adjacency matrix that encodes the join graph of the aforementioned training query; A procedure for encoding one side of the diagonal axis of the adjacency matrix; and A procedure for training a coupled cardinality estimation model using the encoded adjacency matrix and the known coupled cardinality. The procedure for constructing the aforementioned combined cardinality estimation model; A procedure for performing inference using the aforementioned joined cardinality estimation model, wherein the inference includes the predicted joined cardinality for the query; and A procedure to execute a query execution plan for the query using the predicted join cardinality. A system that controls one or more processors to perform operations including those mentioned above.

16. The procedure for carrying out the above reasoning is: A procedure for generating an adjacency matrix that encodes the join graph of the aforementioned query; A procedure for encoding one side of the diagonal axis of the adjacency matrix; A procedure for inputting the encoded adjacency matrix into the combined cardinality estimation model; and Procedure for receiving the predicted coupling cardinality as output. The system according to claim 15, having the following features.

17. The system according to claim 15 or 16, wherein for a given query that joins a set of n tables, each of the adjacency matrices is defined as an n × n matrix, where the value at position (i, j) in the matrix is ​​1 if table i is joined with table j, and 0 otherwise.

18. The system according to claim 17, wherein the step of encoding the adjacency matrix includes a step of condensing the adjacency matrix into a one-dimensional vector for a machine learning algorithm that requires a vector input.

19. The procedure for constructing the aforementioned coupled cardinality estimation model is as follows: A procedure for generating an additional adjacency matrix that encodes the join type of the training query; A procedure for encoding one side of the diagonal axis of the additional adjacency matrix into a join-type adjacency matrix; and A procedure to concatenate the adjacency matrix of the encoded join type with the encoded join graph before training the join cardinality estimation model. The system according to any one of claims 15 to 18, further comprising the above.

20. A computer program product comprising a computer-readable storage medium having program instructions embodied thereby, wherein the program instructions are transmitted to one or more processors: Procedure for generating training queries with known join cardinality; A procedure for generating an adjacency matrix that encodes the join graph of the aforementioned training query; A procedure for encoding one side of the diagonal axis of the adjacency matrix; and A procedure for training a coupled cardinality estimation model using the encoded adjacency matrix and the known coupled cardinality. The procedure for constructing the aforementioned combined cardinality estimation model; A procedure for performing inference using the aforementioned joined cardinality estimation model, wherein the inference includes the predicted joined cardinality for the query; and A procedure to execute a query execution plan for the query using the predicted join cardinality. A computer program product that is executable by one or more processors to perform operations including the above.

21. The procedure for carrying out the above reasoning is: A procedure for generating an adjacency matrix that encodes the join graph of the aforementioned query; A procedure for encoding one side of the diagonal axis of the adjacency matrix; A procedure for inputting the encoded adjacency matrix into the combined cardinality estimation model; and Procedure for receiving the predicted coupling cardinality as output. A computer program product according to claim 20, having the following characteristics.

22. A computer program product according to claim 20 or 21, wherein, for a given query that joins a set of n tables, each of the adjacency matrices is defined as an n × n matrix, where the value at position (i, j) in the matrix is ​​1 if table i is joined with table j, and 0 otherwise.

23. The computer program product according to claim 22, wherein the procedure for encoding the adjacency matrix includes a procedure for condensing the adjacency matrix into a one-dimensional vector for a machine learning algorithm that requires vector input.

24. The procedure for constructing the aforementioned coupled cardinality estimation model is as follows: A procedure for generating an additional adjacency matrix that encodes the join type of the training query; A procedure for encoding one side of the diagonal axis of the additional adjacency matrix into a join-type adjacency matrix; and A procedure to concatenate the adjacency matrix of the encoded join type with the encoded join graph before training the join cardinality estimation model. A computer program product according to any one of claims 20 to 23, further comprising the above.

25. The stage of generating training queries with known join cardinality; The step of generating an adjacency matrix that encodes the join graph of the aforementioned training query; The step of encoding one side of the diagonal axis of the adjacency matrix; and The next step involves training a coupled cardinality estimation model using the encoded adjacency matrix and the known coupled cardinality. A computer implementation method comprising the following features.

26. The computer implementation method according to claim 25, wherein the step of encoding the adjacency matrix includes a step of condensing the adjacency matrix into a one-dimensional vector for a machine learning algorithm that requires a vector input.

27. The step of performing inference using a trained combined cardinality estimation model, wherein the inference includes the predicted combined cardinality for the query, is: The step of generating an adjacency matrix that encodes the join graph of the aforementioned query; The step of encoding one side of the diagonal axis of the adjacency matrix; The step of inputting the encoded adjacency matrix into the combined cardinality estimation model; and The step includes receiving the predicted coupled cardinality as an output; and The step of executing a query execution plan for the query using the predicted join cardinality. A computer implementation method comprising the following features.

Images