Scheduling of query pipeline execution

Abstract

A system includes receiving a query execution plan associated with a plurality of query execution pipelines, estimated execution costs, and estimated intermediate result cardinalities, determining one or more precedence relationships for the plurality of query execution pipelines, determining an execution order for the plurality of query execution pipelines based on the estimated execution costs, the estimated intermediate result cardinalities, and the one or more precedence relationships, and providing the execution order and the query execution plan for the plurality of query execution pipelines to a query execution engine.

Description

[0001] Cross-references to related applications

[0002] This application claims priority to U.S. Provisional Application No. 63 / 263,703, filed November 8, 2021, the contents of which are incorporated herein by reference. Technical Field

[0003] This disclosure relates to database management systems, and more specifically, to database management systems for scheduling the execution of query pipelines. Background Technology

[0004] In traditional database systems, the query optimizer receives a database query and generates a query execution plan based on that query. More specifically, the query optimizer determines multiple candidate execution plans based on the received query, estimates the cost of each execution plan, and selects the plan with the lowest execution cost. The execution engine then executes the selected query execution plan on the database and returns the corresponding result set.

[0005] Query execution plans can conform to a pipeline-based query execution model. According to such a model, a query execution plan comprises multiple execution pipelines. Each execution pipeline takes the base table or result set of a previously executed pipeline as input and performs unary operators or probes (e.g., hash joins) on the base table / result set to construct another result set. Therefore, execution pipelines are interdependent, as the execution of a given pipeline may require the prior execution of one or more other pipelines.

[0006] With increasing data size, query workload, and query complexity, especially in cloud environments, optimizing memory consumption during query processing is crucial. For example, modern cloud-based systems face a large number of complex online analytical processing (OLAP) queries that reference numerous tables. These queries are typically executed using the execution pipeline described above. Furthermore, optimal use of existing hardware resources in main-memory database systems can lead to lower operating costs and higher throughput.

[0007] Systems that need to reduce memory consumption during query processing, especially in the case of pipelined query execution plans. Summary of the Invention

[0008] This disclosure provides a system comprising: a memory storing processor-executable program code; and a processing unit for executing the processor-executable program code such that the system: receives a query execution plan associated with a plurality of query execution pipelines, an estimated execution cost, and an estimated intermediate result cardinality; determines one or more prioritization relationships among the plurality of query execution pipelines; determines an execution order of the plurality of query execution pipelines based on the estimated execution cost, the estimated intermediate result cardinality, and the one or more prioritization relationships; and provides the execution order of the plurality of query execution pipelines and the query execution plan to a query execution engine.

[0009] This disclosure provides a computer-implemented method comprising: receiving a query execution plan associated with a plurality of query execution pipelines, an estimated execution cost, and an estimated intermediate result cardinality; determining one or more prioritization relationships among the plurality of query execution pipelines; determining an execution order of the plurality of query execution pipelines based on the estimated execution cost, the estimated intermediate result cardinality, and the one or more prioritization relationships; and providing the execution order of the plurality of query execution pipelines and the query execution plan to a query execution engine.

[0010] This disclosure provides a non-transitory medium for storing processor-executable program code, which can be executed by a computing system to enable the computing system to: receive a query execution plan associated with a plurality of query execution pipelines, estimated execution costs, and estimated intermediate result cardinality; determine one or more prioritization relationships among the plurality of query execution pipelines; determine the execution order of the plurality of query execution pipelines based on the estimated execution costs, the estimated intermediate result cardinality, and one or more prioritization relationships; and provide the execution order of the plurality of query execution pipelines and the query execution plan to a query execution engine. Attached Figure Description

[0011] Figure 1 This is a block diagram of a system for determining the pipeline execution order for querying an execution plan, according to some embodiments.

[0012] Figure 2 This is a diagram illustrating the query execution plan according to some embodiments.

[0013] Figure 3 This includes flowcharts for determining the pipeline execution sequence of a query execution plan according to some embodiments.

[0014] Figure 4 It is a pipeline-specific view of the query execution plan according to some embodiments.

[0015] Figure 5 This includes flowcharts that determine the pipeline execution order of a query execution plan based on the respective memory costs of candidate pipeline execution orders, according to some embodiments.

[0016] Figure 6 It is a graph illustrating the priority relationships and metadata associated with the execution pipeline of the query execution plan according to some embodiments.

[0017] Figure 7 This is a general view showing a query execution plan subtree of a logical entity used to determine pipelined memory costs according to some embodiments.

[0018] Figure 8 This includes flowcharts that determine the pipeline execution order of a query execution plan based on the execution pipeline depth, according to some embodiments.

[0019] Figure 9 It is a pipeline-specific view of the query execution plan according to some embodiments.

[0020] Figure 10 It is a graph illustrating the priority relationships and metadata associated with the execution pipeline of the query execution plan according to some embodiments.

[0021] Figure 11 This is a block diagram of a database node according to some embodiments.

[0022] Figure 12 This is a view of a cloud-based architecture based on some embodiments. Detailed Implementation

[0023] The following description is provided to enable anyone in the art to make and use the described embodiments, and illustrates the best mode for implementing some embodiments. However, various modifications will be apparent to those skilled in the art.

[0024] As described above, the query execution plan output by the query optimizer can include several execution pipelines. These pipelines can be executed in various orders while still conforming to the query execution plan, where each execution order produces the same result set and is associated with the same execution cost. The inventors have discovered that different pipeline execution orders may consume memory differently (i.e., exhibit different memory consumption profiles). Therefore, with the goal of reducing memory consumption, it may be desirable to execute the query execution plan's execution pipelines in a specific execution order that conforms to the query execution plan, rather than in another execution order that also conforms to the query execution plan.

[0025] The inventors have also discovered systems and methods for selecting a pipelined execution order from several pipelined execution orders that conform to a given query execution plan based on their relative memory consumption profiles. These systems and methods can be efficiently integrated into database management systems for execution after query optimization and before plan execution. In this regard, some embodiments utilize various data structures output by the query optimizer, including the query execution plan and various estimated costs and cardinality.

[0026] Figure 1 This is a block diagram of system 100 according to some embodiments. Each element of system 100 shown can be implemented using any suitable combination of known or becoming known computing hardware and / or software. System 100 may include components of a standalone or distributed (i.e., multi-node) database system. In some embodiments, two or more elements of system 100 are implemented by a single computing device. One or more elements of system 100 may be implemented as a cloud service (e.g., Software as a Service, Platform as a Service).

[0027] Query optimizer 110 receives a query from a client and determines a query execution plan for executing the query on table 124 of database storage 120. Embodiments are not limited to any particular client or any particular query language. Therefore, query optimizer 110 may include any known or soon-to-be-known suitable query processor. As is known in the art, query optimizer 110 may generate several alternative query execution plans based on metadata 122 of database storage 120 and calculate the corresponding execution costs. Metadata 122 may define the structure and relationships of table 124 (e.g., database schema) and statistics representing the data in table 124. These statistics may be periodically refreshed by a statistics server (not shown) of system 100.

[0028] The query optimizer 110 selects one of the query execution plans and provides the selected query execution plan to the pipeline sorting component 130. The query execution plan specifies the execution pipeline, pipeline breaker, input tables, cardinality estimates of intermediate results, and the execution cost of subtrees of the query execution plan. In some embodiments, the query execution plan may also include other data.

[0029] Figure 2 A representation 200 of the elements of a query execution plan received from a query optimizer, according to some embodiments, is shown. The query execution plan includes input tables R, S, T, U, and V, and physical operators 202-208, represented as a hash join. Embodiments are not limited to hash joins.

[0030] Each hash join includes a build end and a probe end, as defined in the query execution plan. According to representation 200, the build end is the right end of the hash join, while the probe end is depicted at the left end. The hash join is defined here as a pipeline interruptor for the build end, thus terminating the pipeline leading to the right end of the hash join. For example, hash join 202 is a pipeline interruptor for a pipeline starting from table T, hash join 204 is a pipeline interruptor for a pipeline starting from table V, hash join 206 is a pipeline interruptor for a pipeline starting from table S, and hash join 208 is a pipeline interruptor for a pipeline starting from table U.

[0031] Therefore, the execution pipeline may depend on intermediate results generated by pipeline interruptors of other execution pipelines. For example, intermediate results generated by hash links 202 and 204 must be retained in memory until they are consumed by hash link 208. Once the pipeline produces a new intermediate result (or final result), all intermediate results referenced therefrom, such as hash tables already probed by the current pipeline, can be released from memory.

[0032] Because intermediate results have different sizes (e.g., number of records * record width), and the number of intermediate results maintained in memory changes during execution, the overall memory utilization varies over time during the execution of the query execution plan. The inventors have noted that the execution order of a single pipeline can significantly affect memory utilization, as this order dictates the sequence and timing in which subsequent pipelines generate, store, and consume intermediate results.

[0033] Therefore, the pipeline sequencing component 130 determines the execution order of each pipeline that leads to optimal memory utilization (i.e., the pipeline execution order). According to some embodiments, the pipeline sequencing component 130 determines all possible pipeline execution orders that conform to the query execution plan provided by the query optimizer 110. Using data provided with the query execution plan, and for each pipeline execution order, the sequencing component 130 estimates memory usage or memory profile over time based on memory size and the lifetime of intermediate results generated by pipeline interruptors during execution according to the execution order. The pipeline execution order associated with the least memory-consuming profile can then be selected as the pipeline execution order for executing the query execution plan.

[0034] The pipeline sorting component 130 then provides the execution engine 140 with the query execution plan and the selected pipeline execution order. The execution engine 140 executes the query execution plan, as is known in the art, and generates a result set 150 based on the selected pipeline execution order, according to the data in Table 124.

[0035] Figure 3This includes a flowchart of process 300 according to some embodiments. In some embodiments, various hardware elements of system 100 execute program code to perform process 300. Process 300 and all other processes mentioned herein may be embodied in processor-executable program code read from one or more non-transitory computer-readable media (e.g., hard disk drives, volatile or non-volatile random access memory, DVD-ROMs, flash drives, and magnetic tapes) and may be executed by one or more processing units, including but not limited to hardware processors, processor cores, and processor threads. In some embodiments, hardwired circuitry may be used instead of or incorporated into the program code used to implement the processes according to some embodiments. Therefore, the embodiments are not limited to any particular combination of hardware and software.

[0036] Prior to process 300, a query is received and a query execution plan is generated based on that query. Queries can be received from any client application through any type of query processor. According to some embodiments, the query is received by the query optimizer of a Structured Query Language (SQL) server. Therefore, queries can include SQL queries, but embodiments are not limited to this.

[0037] As is known in the art, a query optimizer, such as query optimizer 110, generates a query execution plan based on a received query. The generation of the query execution plan may include an estimate of the execution cost associated with the query execution plan. The cost may be estimated based on metadata about the structure and content of the database tables to be queried and their descriptions.

[0038] In process 300, at S310, a query execution plan is received. The query execution plan includes the tree of the execution pipeline, the execution cost of the tree and its subtrees, and an estimated cardinality of intermediate results generated by the pipeline interruptor. The query execution plan may be received by the pipeline sorting component 130 of system 100, but embodiments are not limited thereto. For example, the functionality attributed herein to the pipeline sorting component 130 may be performed entirely or partially by the query optimizer 110.

[0039] The term "tree" is used here to indicate that the execution pipeline can be viewed as branching outwards and downwards from the pipeline interruptor that generates the final query result, as shown in Representation 200. The query execution plan describes the inputs of each pipeline interruptor and determines which inputs include the build end and which include the probe end. The pipeline interruptors, their inputs, and the build / probe end information of the query execution plan are sufficient to generate a representation of the query execution plan, as shown in Representation 200. Figure 2 As shown.

[0040] Figure 4 yes Figure 2 The view shown is a pipeline-specific view of the query execution plan. Each pipeline is labeled with a circled letter. Figure 4 In this case, the name corresponds to the input table of the pipeline. View 400 specifies the estimated cardinality (i.e., the number of records) of the intermediate results generated by each pipeline interruptor.

[0041] As defined herein, each pipeline interruptor terminates its build-end pipeline. For example, pipeline T terminates with a hash connection to probe-end pipeline S, producing a hash table including an estimated 20 records. Pipeline S terminates with a hash connection to probe-end pipeline R, producing a hash table including an estimated 50 records.

[0042] Returning to process 300, step S320 determines the priority relationships of the execution pipelines. Based on the query execution plan, the priority relationships define which pipelines must execute before which other pipelines. These priority relationships can be derived from the query execution plan tree. (See reference) Figure 4 Pipeline T must execute before pipeline S, and pipeline S must execute before pipeline R. Similarly, pipeline V must execute before pipeline U, and pipeline U must execute before pipeline R. However, there is no precedence relationship between pipelines U, V and pipelines S, T. According to some embodiments, the precedence relationship can be provided directly by the query optimizer along with the query execution plan.

[0043] Next, in S330, the execution order of the pipeline is determined. The pipeline execution order is determined based on priority relationships, execution costs, and intermediate result cardinality. For example, initially, all possible pipeline execution orders that match the query execution plan are determined. In this example, the possible pipeline execution orders are (V, U, T, S, R), (V, T, S, U, R), (V, T, U, S, R), (T, S, V, U, R), (T, V, S, U, R), and (T, V, U, S, R).

[0044] Based on the execution cost and intermediate result cardinality received in S310, an indicator for memory usage is determined for each pipelined execution sequence. This determination may include determining the memory size and lifetime of intermediate results generated by the pipeline interruptor during execution, according to each execution sequence. The pipelined execution sequence is determined based on the indicator. For example, the determined pipelined execution sequence may be the sequence associated with the most advantageous memory usage. Further details of S330 according to some embodiments will be described below.

[0045] In S340, the query execution plan and the determined pipeline execution order are provided to the execution engine. The execution engine can then execute the query execution plan according to the pipeline execution order to generate a result set known in the art. The memory usage of this execution is considered more desirable than the memory usage resulting from executing the query execution plan according to a different (e.g., randomly selected) pipeline execution order.

[0046] Figure 5 This includes a flowchart of determining the pipeline execution order of a query execution plan based on the respective memory costs of candidate pipeline execution orders according to some embodiments. Therefore, process 500 may include an implementation of S330 of process 300.

[0047] In S510, the execution order of all pipelines that satisfy the priority relationship of the query execution plan is determined. Figure 6 This is a diagram illustrating the priority relationships of the execution pipeline described above. As mentioned above, satisfying... Figure 6 The pipelined execution order of the graph is (V, U, T, S, R), (V, T, S, U, R), (V, T, U, S, R), (T, S, V, U, R), (T, V, S, U, R), and (T, V, U, S, R). Executing a query execution plan according to one of these pipelined execution orders may result in the creation and consumption of intermediate results of the query execution plan at different points in time compared to execution according to a different pipelined execution order, thus leading to different memory consumption profiles.

[0048] Figure 6 It also shows the cardinality f of the associated pipeline interruptor for each pipeline. This value can be received from and / or determined based on the query optimizer. As mentioned above, the query optimizer provides an estimated cardinality for each intermediate result of the query execution plan. Since each intermediate result corresponds to a pipeline interruptor, the cardinality of the pipeline interruptor is determined as the cardinality of its corresponding intermediate result.

[0049] In S520, for each step of the pipeline execution sequence determined in S510, the lifetime of each pipeline interrupt is determined. The lifetime of the pipeline interrupt can be determined based on the execution cost of its corresponding pipeline.

[0050] Figure 7 The logical entity referenced in the following discussion regarding execution cost, interruptor lifetime, and memory size is shown. According to some embodiments, the query execution plan includes subtrees. i Each subtree has an estimated execution cost c. i As is known in the art, the subtrees and estimated execution costs are provided by the query optimizer. Subtrees i The intermediate results show the cardinality f i And row (i.e., record) size w i And it is written to the corresponding pipeline interrupt b. i This marks the p-line of the production line i The end.

[0051] With the entire subtree s i Execution cost c iIn comparison, execution cost e i Simply execute the pipeline p i The cost. Because the subtrees provided by the query optimizer... i The estimated execution cost c i Excluding subtrees s i The result is written to pipeline interrupt b i Therefore, the cost of writing this kind of cost is in Figure 7 The execution cost σ is represented in the figure. i l i Indicates pipeline interrupter b i Lifespan.

[0052] According to some embodiments, in S520, through an approximately corresponding pipeline p i The execution time is approximately equal to that of the pipeline interrupt b. i lifespan i Conversely, the assembly line p i The execution time can be determined by the pipeline's execution cost e i To approximate. Pipeline p i Execution cost e i It can be obtained from the corresponding pipeline p i subtrees s i The estimated execution cost c i Subtract s from the middle i All subtrees s j The estimated execution cost c j To export, where s j Only its pipeline interruptor b is included j p by assembly line i The subtrees consumed. Pipeline execution time σ i Added to the result difference so that subtrees s can be considered. i and assembly line p i The result is written to pipeline interrupt b i The cost. Accordingly, as follows:

[0053]

[0054] Some embodiments utilize C out The cost function is the sum of the output cardinality of all operators, including base table scans. Since C... out The cost function does not distinguish between the construction cost and the probe cost in the cost of column joins, therefore the cost σ of writing to a pipeline interruptor (e.g., a hash table) is also not considered. i It is set to 0 because C out The cardinality of the result has already been included.

[0055] Pipeline interruptor bi The lifecycle includes its corresponding pipeline p i Execution time e i In addition, to consume pipeline interrupter b i The execution time of other pipelines that must be executed. For the example pipeline execution sequence (V, T, S, U, R) discussed above, the lifetime of the pipeline interruptor of pipeline V is the sum of the execution times of pipelines V, T, S, and U. Given a pipeline execution sequence O = (p1, ..., p i ,…,p j ,…,p k ,…,p n ), and the corresponding pipeline execution cost vector And priority relationship p i <p k assembly line p i assembly line b i Interrupt lifetime l i It can be calculated as:

[0056]

[0057] Therefore, the pipeline execution order O = (p1, ..., p i ,…,p n pipeline lifetime vector It can be defined as

[0058] In S530, the memory cost of each pipeline execution sequence is determined. The memory cost of the pipeline execution sequence can be determined based on the lifetime of each execution pipeline and the intermediate result cardinality of the determined pipeline execution sequence. First, the pipeline interrupter b... i memory size m i It is approximated by its corresponding cardinality f i and row size w i The product of and makes m i =w i ·f i Referring to the example of hash joins, this approximation is based on the assumption that, regardless of the hash table implementation, the pipeline interruptor comprises a densely packed hash table. Given a pipeline execution order O = (p0, ..., p... i ,…,p n The vector of pipeline interrupt memory size. Represented as

[0059] According to some embodiments, the memory cost determined in S530 is a memory integral metric. The memory integral metric is calculated by vectorizing the pipeline interruptor memory cost. Transpose of the pipeline interrupt lifetime

[0060]

[0061] Therefore, the memory integral MI is the key to all pipelined interrupts b. i memory size m i Multiply by its lifespan i The sum. The memory consumption of the pipeline interrupt is 0 before creation and m when the corresponding pipeline starts running. i until b i It is destructed and then becomes 0.

[0062] In S540, the pipeline execution order with the lowest memory cost is determined. This determined pipeline execution order, along with the corresponding query execution plan, can then be provided to the execution engine for executing the query execution plan's execution pipeline in the determined pipeline execution order.

[0063] According to some embodiments, in S510, the pipeline execution order that satisfies the priority relationship is ignored for pipelines whose pipeline interruptors are associated with small memory costs. Such pipeline interruptors are associated with short lifespan, small memory size, or both. By ignoring such pipelines, the number of pipeline execution orders determined in S510 can be significantly reduced, resulting in faster overall execution of process 500.

[0064] For example, assuming pipeline V is associated with low memory cost, it is therefore chosen to be ignored in S510. Figure 6 The pipeline execution order of the graph dependencies is therefore determined in S510 as (U, T, S, R), (T, S, U, R), and (T, U, S, R), which is half of the pipeline execution order determined in the example of S510 above. Once the pipeline execution order associated with the lowest memory cost among these three pipeline execution orders is determined in S540, pipeline V is added at the position within the determined pipeline execution order that conforms to the dependency of the query execution plan.

[0065] Figure 8 This is a flowchart of process 800 according to some embodiments. Process 800 can be executed to determine a pipeline execution order from multiple pipeline execution orders that conform to a given query execution plan. Some embodiments of process 800 do not utilize intermediate results provided by the query optimizer or the cardinality of execution costs to determine the pipeline execution order.

[0066] Conversely, Process 800 is a heuristic algorithm designed to determine the pipelined execution order with low memory cost, but not necessarily the lowest memory cost among all pipelined execution orders that match the topic query execution plan. As an exchange for a suboptimal choice of pipelined execution order, Process 800 can be more... Figure 5 Process 500 executes faster. The heuristic for process 800 is based on the observation that the first execution of the deepest part of an interdependent pipeline will produce a near-optimal pipeline execution order. In other words, executing subtrees in order of length from the start node to the result node is the "best guess" pipeline execution order.

[0067] Figure 9 This is a pipeline-specific view of the query execution plan used to describe some embodiments of process 800. Figure 9 The query execution plan consists of nine base tables and eight intermediate hash tables. Figure 10 The pipeline of the query execution plan is represented as nodes in the dependency graph.

[0068] In S810 of process 800, the length of each subtree of the execution pipeline is determined. Regarding Figure 10 For example, the subtrees determined in S810 and their corresponding lengths are as follows: (E, D, B, A, R; 5); (C, B, A, R; 4); (U, T, S, R; 4).

[0069] Next, in S820, an execution priority is determined for each execution pipeline based on the longest subtree to which it belongs. Continuing this example, execution pipelines E, D, B, A, and R are assigned priority 5, execution pipeline C is assigned priority 4, and execution pipelines U, T, and S are assigned priority 4. Because execution pipeline C is assigned priority 4, and execution pipelines U, T, and S are also assigned priority 4, potential ordering conflicts can be resolved by increasing the priority of execution pipeline C or execution pipelines U, T, and S, and similarly increasing the priority of all higher-priority pipelines. In this example, as... Figure 10 As shown in the dashed box, due to the relationship between execution pipeline C and higher-priority execution pipelines B, A, and R, its priority increases from 4 to 5. Therefore, the priorities of higher-priority execution pipelines E, D, B, A, and R also increase from 5 to 6.

[0070] In S830, the pipeline execution order is determined based on the assigned priority. The determination of the pipeline execution order is primarily based on the dependencies of the query execution plan. However, as mentioned above, at certain execution points, two or more pipelines can be selected as the next pipeline to be executed, while still satisfying the dependencies. In some embodiments of S830, the selected next pipeline is the pipeline with the highest assigned priority among the two or more pipelines.

[0071] Turning back to this example, any of the pipelines E, C, and U could be the first pipeline in the pipeline execution order. However, pipeline E is chosen because it is assigned the highest priority among the three pipelines. Based on the dependency graph, the next pipeline to execute could be D, C, or U. Pipeline D is chosen as the next pipeline to execute because it is assigned the highest priority among the three pipelines. At this execution point, only pipeline C or U can be executed (e.g., pipeline B cannot be executed because it requires the intermediate result generated by pipeline C). Since pipeline C is assigned a higher priority, it is chosen for the next execution. For the remaining pipelines, this logic continues as described above, resulting in a pipeline execution order determined by (E, D, C, B, A, U, T, S, R).

[0072] According to some embodiments, execution process 800 determines the pipeline execution order relatively quickly, and determines the memory cost of the determined pipeline execution order as described above. Next, process 500 is executed as described above (with or without a pipeline ignore option). However, if a sub-result determined in S530 during the determination of the memory cost of a particular pipeline execution order exceeds the memory cost of the pipeline execution order determined via process 800, the determination of the memory cost of the pipeline for that particular pipeline execution order is aborted.

[0073] Figure 11 This is a block diagram of a database architecture that, according to some embodiments, can determine the pipelined execution order of a query execution plan. The embodiments are not limited to... Figure 11 The architecture.

[0074] Server node 1110 can receive queries from one of client applications 1130 and 1140 and return results to it based on data stored within server node 1110. Node 1110 executes program code to provide application server 1115 and query processor 1120. Application server 1115 provides services for executing server applications. For example, a web application executing on application server 1115 can receive Hypertext Transfer Protocol (HTTP) requests from client application 1140, such as... Figure 11 As shown.

[0075] The query processor 1120 may include stored data and an engine for processing that data. The query processor 1120 may also be responsible for processing Structured Query Language (SQL) and Multidimensional Expression (MDX) statements, and may receive such statements directly from the client application 1130.

[0076] The query processor 1120 includes a query optimizer 1122 for determining a query execution plan, a pipeline sorting component 1123 for determining a pipelined execution order of the query execution plan as described herein, and an execution engine 1124 for executing the query execution plan on a table 1126 of the storage device 1125 using the determined pipelined execution order. In some embodiments, the query processor 1120 may further include a statistics server (not shown) for determining statistics for estimating the cost of the query execution plan.

[0077] In some embodiments, the data in storage device 1125 may include one or more of regular table data, row-stored data, column-stored data, and object-based data. Furthermore, the data may be indexed and / or selectively replicated to allow for fast searching and retrieval. Server node 1110 may support multi-tenancy to support multiple unrelated clients separately by providing multiple logical database systems that are programmatically isolated from each other.

[0078] Metadata 1128 includes data describing the database schema identified by table 1126. Metadata 1128 can therefore describe the columns and attributes of table 1126, the attributes of each column in each table 1126, the relationships between columns, and any other suitable information. In one example, metadata 1128 may identify one or more columns of table 1126 as dictionary-compressed and include information for locating the column dictionary and dictionary index associated with each dictionary-compressed column.

[0079] Server node 1110 can implement storage device 1125 as an "in-memory" database, where the complete database is stored in volatile (e.g., non-disk-based) memory (e.g., random access memory). The complete database can be saved and / or backed up to a fixed disk (not shown). Embodiments are not limited to in-memory implementations. For example, data can be stored in random access memory (e.g., cache memory for storing recently used data) and one or more fixed disks (e.g., permanent storage for storing their respective portions of the complete database).

[0080] Figure 12 A cloud-based database deployment according to some embodiments is illustrated. The components shown can reside in one or more public clouds, providing self-service and on-demand provisioning, autoscaling, security, compliance, and identity management features.

[0081] User device 1210 can interact with an application running on application server 1220, for example, via a web browser running on user device 1210, to create, read, update, and delete data managed by database system 1230 and stored in distributed file storage device 1235. Database system 1230 can store data and execute processes as described herein to determine the pipelined execution sequence of query execution plans for the data. Application server 1220 and / or database system 1230 may include cloud-based computing resources, such as virtual machines, allocated by a public cloud provider. In this way, application server 1220 and database system 1230 can exhibit demand-based resilience.

[0082] The foregoing diagrams illustrate the logical architecture of processes according to some embodiments, and actual implementations may include more or different components arranged in other ways. Other topologies may be used in conjunction with other embodiments. Furthermore, each component or device described herein may be implemented by any number of devices communicating via any number of other public and / or private networks. Two or more such computing devices may be geographically isolated from each other and may communicate with each other via networks and / or dedicated connections of any known manner. Each component or device may include any number of hardware and / or software elements suitable for providing the functions described herein as well as any other functions. For example, any computing device used in the implementations described herein may include a programmable processor to execute program code that causes the computing device to operate as described herein.

[0083] All systems and processes discussed herein can be implemented using program code stored on one or more non-transitory computer-readable media. Such media may include, for example, DVD-ROMs, flash drives, magnetic tapes, and solid-state random access memory (RAM) or read-only memory (ROM) storage units. Therefore, embodiments are not limited to any particular combination of hardware and software.

[0084] The elements described herein that communicate with each other are capable of communicating directly or indirectly through any number of different systems to transmit data, including but not limited to shared memory communication, local area networks, wide area networks, telephone networks, cellular networks, fiber optic networks, satellite networks, infrared networks, radio frequency networks, and any other type of network that can be used to transmit information between devices. Furthermore, communication between systems can be carried out through any one or more known or soon-to-be-known transport protocols, such as Asynchronous Transfer Mode (ATM), Internet Protocol (IP), Hypertext Transfer Protocol (HTTP), and Wireless Application Protocol (WAP).

[0085] The embodiments described herein are for illustrative purposes only. Those skilled in the art will recognize that modifications and variations can be made to the above embodiments to implement other embodiments.

Claims

1. A system comprising: a memory storing processor-executable program code; and a processing unit to execute the processor-executable program code to cause the system to: receive a query execution plan associated with a plurality of query execution pipelines, estimated execution costs, and estimated intermediate result cardinalities; determine one or more precedence relationships of the plurality of query execution pipelines; determine a plurality of execution orders of the plurality of query execution pipelines based on the one or more precedence relationships; determine, for each execution order of the plurality of execution orders, a memory size and a lifetime of intermediate results of each pipeline based on the estimated execution costs and the estimated intermediate result cardinalities; determine an execution order from the plurality of query execution orders based on the memory size and the lifetime of intermediate results of each pipeline determined for each execution order of the plurality of execution orders; and provide the execution order of the plurality of query execution pipelines and the query execution plan to a query execution engine.

2. The system of claim 1, wherein determining the execution order comprises: determining a memory cost of each execution order of the plurality of execution orders based on the memory size and the lifetime of intermediate results of each pipeline determined for each execution order of the plurality of execution orders; and determining the execution order from the plurality of execution orders based on the memory cost determined for each execution order of the plurality of execution orders.

3. The system of claim 2, wherein determining the execution order from the plurality of execution orders comprises determining one of the plurality of execution orders having a lowest memory cost.

4. The system of claim 2, wherein determining the plurality of execution orders of the plurality of query execution pipelines based on the one or more precedence relationships comprises: determining one of the plurality of query execution pipelines having a memory cost below a threshold value, wherein the determined plurality of execution orders does not include the one of the plurality of query execution pipelines having the memory cost below the threshold value.

5. The system of claim 1, wherein determining the execution order comprises: determining a longest pipeline path to which each query execution pipeline of the plurality of query execution pipelines belongs based on the one or more precedence relationships; and determining the execution order based on the longest pipeline path to which each query execution pipeline of the plurality of query execution pipelines belongs.

6. The system of claim 5, wherein determining the execution order comprises: determining a first execution order based on the longest pipeline path to which each query execution pipeline of the plurality of query execution pipelines belongs; determining a plurality of execution orders of the plurality of query execution pipelines based on the one or more precedence relationships; determining a memory cost of the first execution order based on the memory size and the lifetime of intermediate results of each pipeline determined for the first execution order; and determining the execution order from the plurality of execution orders based on the memory cost of the first execution order. ​ ​ ​ determining a memory cost for each of the plurality of execution orders based on the memory size and the lifetime of the intermediate results of each pipeline determined for each of the plurality of execution orders, wherein the determination of the memory cost for one of the plurality of execution orders terminates if the memory cost for the one of the plurality of execution orders would exceed the memory cost of the first execution order; and determining an execution order from the plurality of execution orders based on the memory cost determined for each of the plurality of execution orders.

7. The system of claim 6, wherein determining a plurality of execution orders for the plurality of query execution pipelines based on the one or more precedence relationships comprises: determining one of the plurality of query execution pipelines having a memory cost below a threshold value, wherein the determined plurality of execution orders does not include the one of the plurality of query execution pipelines having a memory cost below the threshold value.

8. A computer-implemented method comprising: receiving a query execution plan associated with a plurality of query execution pipelines, estimated execution costs, and estimated intermediate result cardinalities; determining one or more precedence relationships for the plurality of query execution pipelines; determining a plurality of execution orders for the plurality of query execution pipelines based on the one or more precedence relationships; determining, for each of the plurality of execution orders, a memory size and a lifetime of intermediate results of each pipeline based on the estimated execution costs and the estimated intermediate result cardinalities; determining an execution order from the plurality of query execution orders based on the memory size and the lifetime of the intermediate results of each pipeline determined for each of the plurality of execution orders; and providing the execution order for the plurality of query execution pipelines and the query execution plan to a query execution engine.

9. The method of claim 8, wherein determining an execution order comprises: determining a memory cost for each of the plurality of execution orders based on the memory size and the lifetime of the intermediate results of each pipeline determined for each of the plurality of execution orders; and determining an execution order from the plurality of execution orders based on the memory cost determined for each of the plurality of execution orders.

10. The method of claim 9, wherein determining an execution order from the plurality of execution orders comprises determining one of the plurality of execution orders having a lowest memory cost.

11. The method of claim 9, wherein determining a plurality of execution orders for the plurality of query execution pipelines based on the one or more precedence relationships comprises: determining one of the plurality of query execution pipelines having a memory cost below a threshold value, wherein the determined plurality of execution orders does not include the one of the plurality of query execution pipelines having a memory cost below the threshold value.

12. The method of claim 8, wherein determining an execution order comprises: determining a longest pipeline path to which each of the plurality of query execution pipelines belongs based on the one or more precedence relationships; and determining the execution order based on a longest pipeline path to which each of the plurality of query execution pipelines belongs.

13. The method of claim 12, wherein determining the execution order comprises: determining a first execution order based on a longest pipeline path to which each of the plurality of query execution pipelines belongs; determining a plurality of execution orders for the plurality of query execution pipelines based on one or more precedence relationships; determining a memory cost for the first execution order based on a memory size and a lifetime of an intermediate result for each pipeline determined for the first execution order; determining a memory cost for each of the plurality of execution orders based on a memory size and a lifetime of an intermediate result for each pipeline determined for each of the plurality of execution orders, wherein the determination of the memory cost for one of the plurality of execution orders terminates if the memory cost for the one of the plurality of execution orders would exceed the memory cost for the first execution order; and determining the execution order from the plurality of execution orders based on the memory cost determined for each of the plurality of execution orders.

14. The method of claim 13, wherein determining a plurality of execution orders for the plurality of query execution pipelines based on one or more precedence relationships comprises: determining one of the plurality of query execution pipelines having a memory cost below a threshold value, wherein the determined plurality of execution orders does not include the one of the plurality of query execution pipelines having a memory cost below the threshold value.

15. A non-transitory medium storing processor-executable program code, the program code executable by a computing system to cause the computing system to: receive a query execution plan associated with a plurality of query execution pipelines, an estimated execution cost, and an estimated intermediate result cardinality; determine one or more precedence relationships for the plurality of query execution pipelines; determine a plurality of execution orders for the plurality of query execution pipelines based on the one or more precedence relationships; determine, for each of the plurality of execution orders, a memory size and a lifetime of an intermediate result for each pipeline based on the estimated execution cost and the estimated intermediate result cardinality; determine an execution order from the plurality of query execution orders based on the memory size and the lifetime of the intermediate result for each pipeline determined for each of the plurality of execution orders; and provide the execution order for the plurality of query execution pipelines and the query execution plan to a query execution engine.

16. The medium of claim 15, wherein determining the execution order comprises: determining a memory cost for each of the plurality of execution orders based on the memory size and the lifetime of the intermediate result for each pipeline determined for each of the plurality of execution orders; and determining the execution order from the plurality of execution orders based on the memory cost determined for each of the plurality of execution orders.

17. The medium of claim 15, wherein determining a plurality of execution orders for the plurality of query execution pipelines based on one or more precedence relationships comprises: determining one of the plurality of query execution pipelines having a memory cost below a threshold value, wherein the determined plurality of execution orders does not include the one of the plurality of query execution pipelines having a memory cost below the threshold value. determining one of the plurality of query execution pipelines that has a memory cost below a threshold, wherein the determined plurality of execution orders does not include the one of the plurality of query execution pipelines that has a memory cost below the threshold.

18. The medium of claim 15, wherein determining an execution order comprises: determining a longest pipeline path to which each of the plurality of query execution pipelines belongs based on one or more precedence relationships; and determining an execution order based on the longest pipeline path to which each of the plurality of query execution pipelines belongs.

19. The medium of claim 18, wherein determining an execution order comprises: determining a first execution order based on the longest pipeline path to which each of the plurality of query execution pipelines belongs; determining a plurality of execution orders of the plurality of query execution pipelines based on one or more precedence relationships; determining a memory cost of the first execution order based on a memory size and a lifetime of intermediate results of each pipeline determined for the first execution order; determining a memory cost of each of the plurality of execution orders based on a memory size and a lifetime of intermediate results of each pipeline determined for each of the plurality of execution orders, wherein the determination of the memory cost of one of the plurality of execution orders terminates if the memory cost of the one of the plurality of execution orders would exceed the memory cost of the first execution order; and determining an execution order from the plurality of execution orders based on the memory cost determined for each of the plurality of execution orders.

20. The medium of claim 18, wherein determining a plurality of execution orders of the plurality of query execution pipelines based on one or more precedence relationships comprises: determining one of the plurality of query execution pipelines that has a memory cost below a threshold, wherein the determined plurality of execution orders does not include the one of the plurality of query execution pipelines that has a memory cost below the threshold. ​ ​

Images