Query Condition Reordering via Histogram Optimization

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Conventional database systems require significant CPU resources and time to perform dataset queries, especially when dealing with large datasets and multiple queries, leading to increased costs and user frustration.

Innovation Solution

The technique involves reordering query conditions based on rejection rates calculated from hash and shift histograms, allowing the CPU to inspect fewer rows by prioritizing columns with higher rejection rates, thereby reducing CPU usage and query time.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Loss of time

If additional CPUs are added to reduce query time, then the amount of time to perform a dataset query decreases, but the costs of maintaining the dataset increase

Engineering Contradiction:

Improvequery timeVSAvoidCPU resources

Core Design Contradiction:

Loss of timeVSQuantity of substance

Solution Approach 1:

The system pre-calculates and stores histogram data and rejection rates for each column before queries are executed. This preliminary action allows the query optimizer to quickly determine the best evaluation order without performing complex calculations during query execution, thereby reducing query time without requiring additional CPU resources during operation

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system dynamically adjusts the query evaluation order based on the specific query conditions and the pre-calculated rejection rates. By adapting the execution plan to the particular query at hand, the system optimizes performance for each query without needing additional CPUs to handle all possible query variations

Inventive Principle:

Principle #15Dynamics

2Productivity

If conventional query processing is used, then all query conditions are evaluated, but the number of rows inspected is large leading to increased CPU usage and time

Engineering Contradiction:

Improvequery processing completenessVSAvoidtime to inspect rows

Core Design Contradiction:

ProductivityVSLoss of time

Solution Approach 1:

The query evaluation process is segmented into different stages based on rejection rates. The system divides the evaluation into an initial filtering stage using high-rejection-rate conditions and a subsequent detailed evaluation stage, allowing efficient processing by handling the most restrictive conditions first

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The system changes the evaluation parameters by reordering query conditions based on pre-calculated rejection rates. This parameter change optimizes the evaluation sequence to minimize the number of rows that need to be fully inspected, thereby reducing both CPU usage and query time while maintaining complete query processing

Inventive Principle:

Principle #35Parameter changes

Data Source

PatentUS11657052B2Dual filter histogram optimization

Publication Date: 2023.05.23 MEDALLIA INC

AI summary

A plurality of histograms are maintained with respect to a dataset. The dataset includes a plurality of data segments. In response to a query that includes a plurality of conditions ordered in an initial sequence, a plurality of corresponding rejection rates is determined for the plurality of conditions based at least in part on the plurality of histograms. The plurality of conditions are re-ordered according to the plurality of corresponding rejection rates associated with the plurality of conditions to form a reordered sequence for the plurality of conditions. The query is executed according to the reordered sequence of the plurality of conditions.