Synthetic Data for Query Plan Evaluation Across Data Distributions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database query optimizers often fail to select the most optimal execution plan due to limited visibility into the underlying data, leading to inefficiencies in query performance, especially in multi-tenant databases where statistics may be approximate, stale, or fixed across tenants.
Innovation Solution
Generate synthetic data based on query plans and database statistics to empirically determine optimal query plans, using a data synthesizer to create datasets that match the expected distribution of real-world data, allowing for the evaluation of query plans across various data distributions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If a query optimizer uses estimated computational and storage costs to evaluate query plans, then it can select plans with better scores, but it fails to account for variations in real-world data characteristics leading to suboptimal execution
Solution Approach 1:
The patent creates synthetic data copies that replicate the statistical characteristics and distribution patterns of real-world data. These synthetic datasets serve as proxies for evaluating query plans, allowing the optimizer to assess performance across multiple data distributions without accessing actual sensitive data. The synthetic data preserves the essential properties needed for accurate query plan evaluation while enabling broader adaptability.
Solution Approach 2:
The system performs preliminary evaluation of query plans using synthetic data before actual query execution. By generating synthetic datasets and evaluating query plans in advance across various data distributions, the optimizer prepares optimized execution strategies that are already adapted to different data characteristics. This preliminary action allows the system to select more accurate execution plans when real queries are executed.
2Productivity
If database statistics are used to evaluate query plans, then evaluation can be performed with available information, but approximate or stale statistics reduce the accuracy of query plan selection
Solution Approach 1:
Instead of relying directly on potentially stale or approximate database statistics, the system creates synthetic data copies that capture and preserve the statistical properties. These synthetic datasets provide a more reliable basis for query plan evaluation, as they can be generated with accurate statistical characteristics even when original data changes. This copying approach maintains evaluation efficiency while improving score accuracy.
Solution Approach 2:
The system generates multiple synthetic datasets with varied statistical parameters and data distribution characteristics. By changing parameters such as data distribution patterns, value ranges, and statistical moments in the synthetic data, the optimizer can evaluate query plans under diverse conditions. This parameter variation allows the system to select plans that perform well across different scenarios, improving both accuracy and robustness.
3Adaptability or versatility
If the query optimizer evaluates multiple query execution plans, then it can select the best plan based on scores, but it lacks visibility into underlying data to determine true optimality
Solution Approach 1:
The patent addresses the information loss by creating synthetic data copies that preserve the essential statistical properties and distribution patterns of the underlying real data. These synthetic copies serve as informative proxies, providing the query optimizer with visible data characteristics without requiring direct access to sensitive or private real-world data. The synthetic data maintains sufficient fidelity to enable accurate evaluation across multiple query plans.
4Device complexity
If fixed statistics are used across multiple tenants in a multi-tenant database, then system complexity is reduced, but query performance optimization suffers due to inability to account for tenant-specific data characteristics
Solution Approach 1:
The system creates tenant-specific synthetic data copies that replicate each tenant's unique data characteristics and distribution patterns. Rather than using a single fixed statistic set for all tenants, the system generates customized synthetic datasets for each tenant based on their specific data properties. This approach maintains manageable complexity while enabling personalized query optimization for each tenant, as the synthetic data captures tenant-specific features without requiring direct access to sensitive tenant data.
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.