Global Histogram Merging for Distributed Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In parallel database systems, especially in shared-nothing architectures, the lack of a global view of data and statistics leads to inaccurate global distinct value estimates, resulting in suboptimal query plans due to overestimation or underestimation of distinct values, as compute nodes may not be aware of values present across other nodes.
Innovation Solution
Formulating global statistics by merging histograms from multiple compute nodes, aligning step boundaries, and calculating global average rows per distinct value using knowledge of the partitioning algorithm, ensures accurate global distinct value estimation and improved query optimization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If histograms from multiple compute nodes are merged to form global statistics, then measurement precision of global distinct values is improved, but device complexity increases due to the need to align step boundaries and calculate global averages across distributed nodes
Solution Approach 1:
The patent applies preliminary action by pre-aligning histogram step boundaries across compute nodes before merging. The system establishes a global step boundary structure in advance, allowing each node to contribute pre-aligned histogram data. This preliminary alignment eliminates the need for complex post-merging boundary reconciliation, resolving the contradiction by preparing the data structure beforehand to ensure measurement precision while controlling complexity.
Solution Approach 2:
The patent introduces an intermediary mechanism in the form of a coordinator node or centralized statistics management component that receives histograms from distributed compute nodes, performs the alignment and merging operations, and produces global statistics. This intermediary handles the complexity of boundary alignment and global average calculation centrally, allowing individual nodes to maintain simplicity while achieving accurate global measurements through the mediating structure.
2Device complexity
If compute nodes operate independently in shared-nothing architecture, then device complexity is reduced at individual nodes, but loss of information occurs as nodes are unaware of values present across other nodes
Solution Approach 1:
The patent applies merging by combining histograms from multiple independent compute nodes into a unified global histogram. Each node maintains its simple independent operation with local histograms, then the system merges these local histograms by aligning step boundaries and combining frequency data. This merging process recovers the global information that would otherwise be lost, allowing the system to maintain both node independence and global visibility.
Solution Approach 2:
The patent implements feedback mechanisms where compute nodes submit their local histogram statistics to a centralized coordinator or peer nodes. This feedback loop allows the system to aggregate information from all nodes, ensuring that global distinct value estimates account for data across the entire distributed system. The feedback mechanism enables independent nodes to contribute to collective knowledge without increasing individual complexity.
Data Source
AI summary
The present invention extends to methods, systems, and computer program products for formulating global statistics for parallel databases. In general, embodiments of the invention merge (combine) information in multiple compute node level histograms to create a global histogram for a table that is distributed across a number of compute nodes. Merging can include aligning histogram step boundaries across the compute node histograms. Merging can include aggregating histogram step-level information, such as, for example, equality rows and average range rows (or alternately equality rows, range rows, and distinct range rows), across the compute node histograms into a single global step. Merging can account for distinct values that do not appear at one or more compute nodes as well as distinct values that are counted at multiple compute nodes. A resulting global histogram can be coalesced to reduce the step count.