US-12619612-B2 - Optimizing an operator flow for performing aggregation via a database system

Abstract

A database system is operable to generate an initial query operator execution that includes an IO operator for execution serially before an aggregation operator indicating performance of an aggregation. The initial query operator execution flow is converted into an updated query operator execution flow semantically equivalent to the initial query operator execution flow based on updating the IO operator to include the performance of the aggregation and adding a re-aggregation operator serially after the IO operator. A corresponding query is executed by applying the updated query operator execution flow based on executing, via each of a plurality of parallelized resources, the IO operator generate corresponding sub-aggregation output via performance of the aggregation, and executing the re-aggregation operator upon data blocks generated based on execution of the IO operator across the plurality of parallelized resources.

Inventors

• Anna Veselova
• Greg R. Dhuse

Assignees

• Ocient Holdings LLC

Dates

Publication Date

20260505

Application Date

20230501

Claims (20)

1. 1 . A method for execution by at least one processor of a database system, comprising: generating an initial query operator execution flow for a corresponding query for execution that includes an IO operator for execution serially before an aggregation operator indicating performance of an aggregation; converting the initial query operator execution flow into an updated query operator execution flow that is semantically equivalent to, and produces the same result as, the initial query operator execution flow by: updating the IO operator to include the performance of the aggregation; and adding a re-aggregation operator serially after the IO operator, wherein the re-aggregation operator is configured to further aggregate a plurality output data blocks generated via a plurality of parallelized executions of the IO operator via a plurality of parallelized resources; and executing the corresponding query against a plurality of rows by applying the updated query operator execution flow based on: executing, via each of the plurality of parallelized resources, the IO operator to produce at least one data block indicating corresponding sub-aggregation output via performance of the aggregation upon a corresponding subset of the plurality of rows; and executing the re-aggregation operator upon a plurality of data blocks generated based on execution of the IO operator across the plurality of parallelized resources to generate aggregation output.
2. 2 . The method of claim 1 , further comprising: determining whether the initial query operator execution flow meets a set of aggregation push-down conditions; wherein the initial query operator execution flow is converted into the updated query operator execution flow in response to determining the initial query operator execution flow meets the set of aggregation push-down conditions.
3. 3 . The method of claim 2 , wherein the set of aggregation push-down conditions includes a condition requiring that the aggregation operator is not paired with an ORDER BY clause.
4. 4 . The method of claim 2 , wherein the set of aggregation push-down conditions includes at least one of: an input type condition requiring input to the IO operator and the aggregation operator be one of a set of data types; and an output type condition requiring output of the IO operator and the aggregation operator be one of the set of data types.
5. 5 . The method of claim 2 , wherein the set of aggregation push-down conditions includes at least one of: an aggregation type condition requiring the aggregation be one of a set of aggregation types.
6. 6 . The method of claim 5 , wherein the set of aggregation types includes at least one of: a count aggregation type, a summation aggregation type, a product aggregation type, a maximum aggregation type, or a minimum aggregation type.
7. 7 . The method of claim 1 , wherein converting the initial query operator execution flow into an updated query operator execution flow includes selecting a position of the re-aggregation operator in the updated query operator execution flow from a plurality of possible positions in the updated query operator execution flow serially after the IO operator, wherein the re-aggregation operator is included in the updated query operator execution flow at the selected position.
8. 8 . The method of claim 7 , wherein the position of the re-aggregation operator in the updated query operator execution flow is selected to be immediately after the IO operator.
9. 9 . The method of claim 7 , wherein the position of the re-aggregation operator in the updated query operator execution flow is selected based on a position of the aggregation operator in the initial query operator execution flow.
10. 10 . The method of claim 7 , wherein the position of the re-aggregation operator in the updated query operator execution flow is selected based on further selecting a position of a shuffle operation in the updated query operator execution flow, wherein the re-aggregation operator is performed serially after the shuffling operator.
11. 11 . The method of claim 1 , wherein converting the initial query operator execution flow into the updated query operator execution flow includes applying a plurality of updates to the initial query operator execution flow one at a time to ultimately produce the updated query operator execution flow, wherein one of the plurality of updates includes updating the IO operator to include the performance of the aggregation and including the re-aggregation operator serially after the IO operator.
12. 12 . The method of claim 11 , wherein the initial query operator execution flow includes the aggregation operator serially after a join operator, wherein a prior one of the plurality of updates includes pushing the aggregation operator to a new position serially before the join operator and serially after the IO operator, and wherein the one of the plurality of updates is performed after the prior one of the plurality of updates and further includes removing the aggregation operator from the new position based on including the performance of the aggregation within the IO operator.
13. 13 . The method of claim 12 , wherein the re-aggregation operator is in the new position after performing the one of the plurality of updates based on replacing the aggregation operator in the new position with the re-aggregation operator in the new position.
14. 14 . The method of claim 11 , wherein the re-aggregation operator is added serially after the IO operator in an initial position based on performing the one of the plurality of updates, and wherein a subsequent one of the plurality of updates is performed after the one of the plurality of updates and includes re-positioning the re-aggregation operator to a new position different from the initial position, wherein the new position is also serially after the IO operator.
15. 15 . The method of claim 1 , wherein updating the IO operator to include the performance of the aggregation is based on updating an IO pipeline implemented via the IO operator to include the performance of the aggregation, wherein executing the IO operator includes executing the IO pipeline.
16. 16 . The method of claim 1 , wherein a database table that includes the plurality of rows includes a plurality of columns, and wherein the aggregation is performed to generate the corresponding sub-aggregation output corresponding to applying the aggregation to at least one column of the plurality of columns.
17. 17 . The method of claim 1 , wherein the initial query operator execution flow further includes a second aggregation operator indicating performance of a second aggregation, and wherein the IO operator is serially before the second aggregation operator in the initial query operator execution flow, wherein converting the initial query operator execution flow into the updated query operator execution flow further includes: updating the IO operator to further include the performance of the second aggregation; and adding a second re-aggregation operator serially after the IO operator; wherein executing the corresponding query against the plurality of rows further includes executing the second re-aggregation operator to further generate second aggregation output.
18. 18 . The method of claim 17 , the aggregation corresponds to a first type of aggregation, and the second aggregation corresponds to a second type of aggregation different from the first type of aggregation.
19. 19 . A database system comprising: at least one processor; and at least one memory storing executable instructions that, when executed by the at least one processor, cause the database system to: generate an initial query operator execution flow for a corresponding query for execution that includes an IO operator for execution serially before an aggregation operator indicating performance of an aggregation; convert the initial query operator execution flow into an updated query operator execution flow that is semantically equivalent to, and produces the same result as, the initial query operator execution flow by: updating the IO operator to include the performance of the aggregation; and adding a re-aggregation operator serially after the IO operator, wherein the re-aggregation operator is configured to further aggregate a plurality of output data blocks generated via a plurality of parallelized executions of the IO operator via a plurality of parallelized resources; and execute the corresponding query against a plurality of rows by applying the updated query operator execution flow based on: executing, via each of the plurality of parallelized resources, the IO operator to produce at least one data block indicating corresponding sub-aggregation output via performance of the aggregation upon a corresponding subset of the plurality of rows; and executing the re-aggregation operator upon a plurality of data blocks generated based on execution of the IO operator across the plurality of parallelized resources to generate aggregation output.
20. 20 . A non-transitory computer readable storage medium comprises: at least one memory section that stores operational instructions that, when executed by at least one processing module that includes a processor and a memory, causes the at least one processing module to: generate an initial query operator execution flow for a corresponding query for execution that includes an IO operator for execution serially before an aggregation operator indicating performance of an aggregation; convert the initial query operator execution flow into an updated query operator execution flow that is semantically equivalent to, and produces the same result as, the initial query operator execution flow by: updating the IO operator to include the performance of the aggregation; and adding a re-aggregation operator serially after the IO operator, wherein the re-aggregation operator is configured to further aggregate a plurality of output data blocks generated via a plurality of parallelized executions of the IO operator via a plurality of parallelized resources; and execute the corresponding query against a plurality of rows by applying the updated query operator execution flow based on: executing, via each of the plurality of parallelized resources, the IO operator to produce at least one data block indicating corresponding sub-aggregation output via performance of the aggregation upon a corresponding subset of the plurality of rows; and executing the re-aggregation operator upon a plurality of data blocks generated based on execution of the IO operator across the plurality of parallelized resources to generate aggregation output.

Description

CROSS-REFERENCE TO RELATED APPLICATIONS Not Applicable. STATEMENT REGARDING FEDERALLY SPONSORED RESEARCH OR DEVELOPMENT Not Applicable. INCORPORATION-BY-REFERENCE OF MATERIAL SUBMITTED ON A COMPACT DISC Not Applicable. BACKGROUND OF THE INVENTION Technical Field of the Invention This invention relates generally to computer networking and more particularly to database system and operation. Description of Related Art Computing devices are known to communicate data, process data, and/or store data. Such computing devices range from wireless smart phones, laptops, tablets, personal computers (PC), work stations, and video game devices, to data centers that support millions of web searches, stock trades, or on-line purchases every day. In general, a computing device includes a central processing unit (CPU), a memory system, user input/output interfaces, peripheral device interfaces, and an interconnecting bus structure. As is further known, a computer may effectively extend its CPU by using “cloud computing” to perform one or more computing functions (e.g., a service, an application, an algorithm, an arithmetic logic function, etc.) on behalf of the computer. Further, for large services, applications, and/or functions, cloud computing may be performed by multiple cloud computing resources in a distributed manner to improve the response time for completion of the service, application, and/or function. Of the many applications a computer can perform, a database system is one of the largest and most complex applications. In general, a database system stores a large amount of data in a particular way for subsequent processing. In some situations, the hardware of the computer is a limiting factor regarding the speed at which a database system can process a particular function. In some other instances, the way in which the data is stored is a limiting factor regarding the speed of execution. In yet some other instances, restricted co-process options are a limiting factor regarding the speed of execution. BRIEF DESCRIPTION OF THE SEVERAL VIEWS OF THE DRAWING(S) FIG. 1 is a schematic block diagram of an embodiment of a large scale data processing network that includes a database system in accordance with various embodiments; FIG. 1A is a schematic block diagram of an embodiment of a database system in accordance with various embodiments; FIG. 2 is a schematic block diagram of an embodiment of an administrative sub-system in accordance with various embodiments; FIG. 3 is a schematic block diagram of an embodiment of a configuration sub-system in accordance with various embodiments; FIG. 4 is a schematic block diagram of an embodiment of a parallelized data input sub-system in accordance with various embodiments; FIG. 5 is a schematic block diagram of an embodiment of a parallelized query and response (Q&R) sub-system in accordance with various embodiments; FIG. 6 is a schematic block diagram of an embodiment of a parallelized data store, retrieve, and/or process (IO& P) sub-system in accordance with various embodiments; FIG. 7 is a schematic block diagram of an embodiment of a computing device in accordance with various embodiments; FIG. 8 is a schematic block diagram of another embodiment of a computing device in accordance with various embodiments; FIG. 9 is a schematic block diagram of another embodiment of a computing device in accordance with various embodiments; FIG. 10 is a schematic block diagram of an embodiment of a node of a computing device in accordance with various embodiments; FIG. 11 is a schematic block diagram of an embodiment of a node of a computing device in accordance with various embodiments; FIG. 12 is a schematic block diagram of an embodiment of a node of a computing device in accordance with various embodiments; FIG. 13 is a schematic block diagram of an embodiment of a node of a computing device in accordance with various embodiments; FIG. 14 is a schematic block diagram of an embodiment of operating systems of a computing device in accordance with various embodiments; FIGS. 15-23 are schematic block diagrams of an example of processing a table or data set for storage in the database system in accordance with various embodiments; FIG. 24A is a schematic block diagram of a query execution plan implemented via a plurality of nodes in accordance with various embodiments; FIGS. 24B-24D are schematic block diagrams of embodiments of a node that implements a query processing module in accordance with various embodiments; FIG. 24E is an embodiment is schematic block diagrams illustrating a plurality of nodes that communicate via shuffle networks in accordance with various embodiments; FIG. 24F is a schematic block diagram of a database system communicating with an external requesting entity in accordance with various embodiments; FIG. 24G is a schematic block diagram of a query processing system in accordance with various embodiments; FIG. 24H is a schematic block diagram of a query operator executio