Workload-Based Sampling for Database Selectivity Estimation

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing database systems face inaccuracies in estimating selectivity and cardinality of query predicates, particularly for large tables with low selectivities, leading to suboptimal query execution plan selection due to insufficient quality of random sampling-based estimates.

Innovation Solution

A system generates a sample table by selectively adding rows that minimize estimation error, based on the workload queries, to create a representative subset that accurately reflects the selectivity of the original table, thereby improving the accuracy of selectivity statistics and query execution plan selection.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Measurement precision

If random sampling is used to estimate selectivity, then the estimation process is simple and fast, but the accuracy of selectivity estimates is insufficient for large tables with low selectivities

Engineering Contradiction:

Improveselectivity estimation accuracyVSAvoidsampling process complexity

Core Design Contradiction:

Measurement precisionVSDevice complexity

Solution Approach 1:

The system performs preliminary actions by executing workload queries against the full table to collect actual selectivity data before creating the sample table. This preliminary execution allows the system to understand the true selectivity characteristics of the workload, which are then used to guide the selective addition of rows to the sample table, ensuring accurate selectivity estimates without complex sampling algorithms during query optimization.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system creates a simplified copy (sample table) of the original table that preserves the critical selectivity characteristics. Instead of copying the entire table, it selectively adds rows based on their contribution to minimizing estimation error, creating a smaller representation that accurately reflects the selectivity behavior of the full table for the given workload.

Inventive Principle:

Principle #26Copying

2Measurement precision

If a larger sample size is used to improve estimation accuracy, then selectivity estimates improve, but the time and resources required for sampling increase

Engineering Contradiction:

Improveselectivity estimation accuracyVSAvoidsampling time

Core Design Contradiction:

Measurement precisionVSLoss of time

Solution Approach 1:

The system uses the workload queries themselves to serve dual purposes: they are both the queries that need optimization and the mechanism for building an accurate sample table. By executing these same queries against the full table to collect selectivity data, the system makes the workload queries serve the sampling process, eliminating the need for separate, time-consuming sampling procedures.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system changes the parameter of sample table size dynamically based on the contribution of each row to estimation error. Instead of using a fixed large sample size, it selectively adds rows that provide the most value in minimizing estimation error, achieving high accuracy with a smaller, optimized sample size that reduces sampling time.

Inventive Principle:

Principle #35Parameter changes

3Measurement precision

If selective row addition is performed to minimize estimation error, then selectivity estimates become more accurate, but the complexity of the sampling process increases

Engineering Contradiction:

Improveselectivity estimation accuracyVSAvoidsample table generation complexity

Core Design Contradiction:

Measurement precisionVSDevice complexity

Solution Approach 1:

The system implements feedback by calculating the estimation error contribution of each candidate row and using this information to guide the selective addition process. The feedback loop evaluates how much each row reduces the overall estimation error for the workload queries, allowing the system to make informed decisions about which rows to add to the sample table, achieving high accuracy through a systematic but manageable process.

Inventive Principle:

Principle #23Feedback

4Reliability

If the sample table accurately reflects the original table's selectivity, then query execution plan selection improves, but the process of creating such a representative sample becomes more complex

Engineering Contradiction:

Improvequery execution plan selectionVSAvoidsample table creation process

Core Design Contradiction:

ReliabilityVSDevice complexity

Solution Approach 1:

The system replaces complex mechanical sampling algorithms with a more straightforward approach based on actual query execution results. Instead of using sophisticated sampling theories and algorithms, it simply executes the workload queries against the full table to observe actual selectivity behavior, then uses this empirical data to guide sample table construction, substituting theoretical complexity with practical, observation-based methodology.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

Data Source

PatentUS11455307B2Workload-based sampling

Publication Date: 2022.09.27 SAP SE

AI summary

A system includes determination of a plurality of queries of a workload, determination of a data source comprising a plurality of data rows, and determination of a sample data source based on a cardinality of each of the plurality of queries with respect to the data source and an estimated cardinality of each of the plurality of queries with respect to the data source, wherein the estimated cardinality of a query with respect to the data source is determined based on the sample data source.