Filter Pushdown in Query Optimizers for Semantic Integrity
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query optimization techniques can alter the semantics of a query by moving filters to inappropriate positions in the query model, leading to performance issues and incorrect results, especially when filter attributes are not required at certain operator nodes.
Innovation Solution
A system and method that includes a query optimizer with a filter mover and semantics checker to decouple and move filters to downstream positions within the query model, ensuring semantic correctness and performance by marking filter attributes as transparent and removing them from unnecessary operator nodes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the filter is placed at the top operator node of the instantiated query model, then the filter can be applied to all data, but filter attributes must be populated throughout the query model which adversely affects semantics and performance
Solution Approach 1:
Instead of placing the filter at the top operator node and propagating filter attributes downstream, the patent inverts the approach by pushing the filter down to the bottom operator node and having filter attributes propagated upstream. This inversion resolves the contradiction by eliminating the need to populate filter attributes throughout the entire query model while still ensuring correct semantics through the semantics checker.
Solution Approach 2:
The patent introduces a semantics checker as an intermediary component that verifies whether filter pushdown maintains query semantics correctness. This mediator allows the system to safely move filters to downstream positions without compromising reliability, thus improving performance while maintaining semantic correctness.
2Productivity
If the query optimizer removes attributes not required by the query, then data retrieval efficiency improves, but the filter may require filter attributes that are removed, changing query semantics
Solution Approach 1:
The patent applies preliminary action by performing filter pushdown before the attribute removal phase of query optimization. By moving the filter to the bottom operator node first, the system ensures that only attributes actually required by the filter are retained in the query model, while unnecessary attributes can be safely removed without affecting query semantics.
Solution Approach 2:
The semantics checker provides feedback mechanisms that verify whether filter attributes are properly maintained after optimization operations. This feedback loop ensures that attribute removal does not inadvertently change query semantics, allowing the system to maintain high data retrieval efficiency while preserving reliability.
3Productivity
If the filter is moved downstream in the query model, then performance improves by reducing unnecessary attribute propagation, but the filter may need to be at a specific position to maintain correct semantics
Solution Approach 1:
The patent makes the filter position dynamic rather than static. The filter is automatically pushed down to the most downstream position where it can be applied, and the semantics checker dynamically verifies that this position maintains correct semantics. This dynamic approach allows the system to optimize performance for different query models while ensuring semantic correctness.
Solution Approach 2:
The patent changes the parameter of filter position from a fixed top-level location to a variable downstream location. By adjusting this parameter based on the query model structure and using the semantics checker to validate the change, the system achieves improved performance while maintaining semantic consistency.
Data Source
AI summary
According to an aspect, a system for moving a filter in a query model to ensure query semantics includes a query engine configured to obtain a query model for a query to be executed on a database. The query engine includes a query optimizer configured to instantiate the query model by merging the query with the query model. The query optimizer includes a filter mover configured to move a filter to a point within the instantiated query model such that semantics for the query within the instantiated query model is ensured.