Columnar Information Computation in Database Join Enumeration
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face inefficiencies in computing the minimal projection list during query plan optimization, especially in systems that materialize intermediate results, exploit vector or batch processing, or support massive scaling, as they rely on inaccurate assumptions about columnar information, leading to suboptimal query plan selection and increased memory and CPU consumption.
Innovation Solution
A two-phase approach is introduced to efficiently compute the minimal projection list, where Phase One performs pre-computation once per query block and Phase Two applies incremental computations for each sub-plan, using bit vector operations to determine projected attributes and columnar information, thereby reducing execution overhead and improving cost estimation accuracy.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If database systems use inaccurate assumptions about columnar information during query plan optimization, then query plan selection becomes simpler and faster, but cost estimation accuracy deteriorates and memory/CPU consumption increases
Solution Approach 1:
The patent segments the computation of columnar information into two distinct phases: Phase One performs pre-computation once per query block to establish baseline columnar properties, while Phase Two applies incremental computations for each sub-plan. This segmentation allows the system to avoid redundant computations while maintaining accurate cost estimation, thereby resolving the contradiction between measurement precision and productivity.
Solution Approach 2:
The patent implements preliminary action by performing pre-computation of columnar information in Phase One before the actual query plan optimization begins. This pre-computation establishes accurate baseline data about columnar properties that can be reused across multiple sub-plans, eliminating the need to recompute this information repeatedly and thus improving both accuracy and efficiency.
2Measurement precision
If database systems recompute columnar information for each sub-plan during join enumeration, then cost estimation accuracy improves, but computational overhead and execution time increase significantly
Solution Approach 1:
The patent performs preliminary computation of columnar information in Phase One once per query block, storing these results for reuse. During Phase Two, when enumerating different sub-plans, the system applies incremental computations that build upon the pre-computed data rather than recomputing everything from scratch. This dramatically reduces the time loss while maintaining accurate projection list computation.
Solution Approach 2:
The patent effectively recovers and reuses columnar information across different sub-plans. Instead of discarding the pre-computed columnar properties after each sub-plan evaluation, the system recovers and applies them incrementally to subsequent sub-plans, avoiding redundant computations and reducing overall execution time.
3Productivity
If database systems assume maximum record width for cost modeling, then computation becomes simpler and faster, but memory requirements and network transfer predictions become inaccurate
Solution Approach 1:
The patent applies local quality by computing and using specific columnar properties (such as actual column widths and encodings) for each column rather than assuming a uniform maximum record width for all data. This localized, precise measurement approach improves the accuracy of memory requirements and network transfer predictions while maintaining computational efficiency through the two-phase computation structure.
Data Source
AI summary
Techniques are described herein for computing columnar information during join enumeration in a database system. The computation occurs in two phases: the first phase involves a pre-computational phase that is only run once per query block to initialize and prepare a set of data structures. The second phase is an incremental approach that takes place for every query sub-plan. Upon completion of the second phase, the generated projected attributes of a query sub-plan are associated as columnar information associated with the query sub-plan, and used to compute the query execution cost. Subsequently, based on the computed query execution cost, the query sub-plan may be executed as part of the query execution plan.