Synthetic Query Plan Evaluation Using Statistical Test Data
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database query optimizers often fail to select the most optimal execution plan due to difficulties with statistics and data skew, making it challenging for developers to ascertain whether the generated plan is efficient without direct access to the underlying data.
Innovation Solution
A synthetic data generation technique is employed to create datasets that mimic real-world data distributions, allowing query optimizers to evaluate and select optimal plans based on database statistics, using probability models to generate synthetic data tuples that reflect expected query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Extent of automation
If query optimizers rely on database statistics to evaluate execution plans, then query plan selection can be automated, but the accuracy of plan evaluation deteriorates due to difficulties with statistics and data skew
Solution Approach 1:
The patent creates synthetic copies of real database tables with preserved statistical properties and data distributions. These synthetic tables serve as test beds for evaluating query execution plans without accessing actual production data. The copying process maintains key characteristics like data types, constraints, indexes, and statistical moments while generating realistic test scenarios that reveal optimizer weaknesses.
Solution Approach 2:
The system performs preliminary evaluation of query execution plans on synthetic data before deploying them to production. By pre-generating synthetic tables and testing plans beforehand, the system identifies potentially suboptimal plans that rely on inaccurate statistics or exhibit poor behavior under specific data distributions, allowing developers to refine the optimizer before real-world deployment.
2Measurement precision
If developers want to verify query plan efficiency, then direct access to underlying data is needed, but data privacy and security constraints prevent such access
Solution Approach 1:
Instead of granting developers access to sensitive production data, the system creates synthetic copies that preserve the statistical and structural properties needed for plan evaluation. These synthetic tables allow thorough testing and verification of query plans while maintaining complete data isolation and privacy protection.
Solution Approach 2:
The synthetic data generation system acts as an intermediary between the need for plan verification and data privacy constraints. It translates production data characteristics into synthetic representations that enable evaluation without exposing actual sensitive information, serving as a safe middle ground for developer testing.
3Speed
If query optimizers use estimated computational costs to select execution plans, then plan selection speed is improved, but the reliability of plan selection deteriorates due to inaccurate cost estimates
Solution Approach 1:
The system performs preliminary testing of execution plans on synthetic data to validate cost estimates before production deployment. By pre-evaluating plans on realistic synthetic scenarios, the system identifies cases where estimated costs diverge from actual performance, allowing for optimizer adjustments that improve both speed and reliability.
Solution Approach 2:
The synthetic data testing framework provides feedback loops for validating and refining cost estimation algorithms. By comparing estimated costs against actual plan performance on synthetic data, the system iteratively improves the accuracy of cost models while maintaining efficient plan selection capabilities.
Data Source
AI summary
Techniques are disclosed relating to database query optimizers. In some embodiments, a system receives, from a query optimizer, a plurality of query plans for a database maintained by the database system. The system retrieves a set of database statistics for the database and generates, via a data synthesizer, a plurality of synthetic datasets, where generating a given synthetic dataset is performed based on a given query plan of the plurality of query plans and the set of database statistics, and includes generating a plurality of synthetic data tuples. The system executes the plurality of query plans on the plurality of synthetic datasets and updates the query optimizer based on results of executing the plurality of query plans on the plurality of synthetic datasets. The disclosed data synthesis may advantageously improve query performance due to more efficient query plans being selected for execution of requested queries.