Dynamic Filter Pushdown for Cloud MPP Databases
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database scanning techniques face inefficiencies due to inaccurate selectivity estimations, lack of support for diverse storage types in hash join and semi-join operations, and ineffective filter pushdown schemes, leading to increased computational times and redundancy.
Innovation Solution
A method for dynamic filter pushdown in massive parallel processing databases on the cloud, which involves acquiring filters and statistics, determining selectivity, and pushing down filters based on a threshold condition, while supporting various storage types and partitioning filters to reduce unnecessary computations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If filter pushdown is performed without accurate selectivity estimation, then filter application is simplified, but computational efficiency decreases and unnecessary processing increases
Solution Approach 1:
The system performs preliminary actions by acquiring statistics information about database tables before executing queries. This statistics acquisition enables accurate selectivity estimation in advance, allowing the query optimizer to make informed decisions about filter pushdown without sacrificing computational efficiency.
Solution Approach 2:
The system uses feedback mechanisms where statistics information about table selectivity is continuously gathered and fed back to the query optimizer. This feedback loop enables dynamic adjustment of filter pushdown decisions based on actual table characteristics, resolving the contradiction between operational simplicity and computational efficiency.
2Device complexity
If hash join and semi-join operations are performed without storage type support, then operation complexity is reduced, but inability to process diverse databases increases
Solution Approach 1:
The system implements universality by providing unified support for multiple storage types (relational, NoSQL, in-memory, columnar) within the same hash join and semi-join operations. The query optimizer can handle diverse database storage types without increasing operational complexity, enabling versatile processing across different database systems.
3Ease of operation
If filters are pushed down to all database tables, then query processing is simplified, but redundancy and unnecessary computation increase
Solution Approach 1:
The system applies local quality by customizing filter pushdown decisions for each specific database table based on its individual statistics information. Rather than uniformly pushing down filters to all tables, the system evaluates each table's selectivity characteristics and makes localized decisions about whether filter pushdown is beneficial, avoiding redundancy and unnecessary computation on tables where it would be counterproductive.
Data Source
AI summary
A method for dynamic filter pushdown for massive parallel processing databases on the cloud, including acquiring one or more filters corresponding to a query, acquiring statistics information of one or more database tables, determining a selectivity of the one or more database tables based on the statistics information, determining whether the selectivity satisfies a threshold condition, and pushing down the one or more filters to the one or more database tables based on the determination of whether the selectivity satisfies a threshold condition.