Database Query Join Optimization via Predicate Pushdown
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face performance issues when executing JOIN statements across tables in different databases, leading to increased network latency, memory, and CPU usage due to inefficient query processing.
Innovation Solution
A computer-implemented method that involves obtaining a query with a JOIN statement, determining if executing the JOIN statement exceeds pre-defined system resource allotments, generating a filter based on the intersection of predicates in the query, and applying this filter to fetch results while excluding unrelated data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a JOIN statement is executed across tables in different databases, then data from multiple tables can be combined, but network latency increases and system resources (memory and CPU) are excessively consumed
Solution Approach 1:
The system performs preliminary analysis of the JOIN query to identify pushable predicates and generates pushdown filters before executing the JOIN operation. By pre-processing the query to determine which filters can be applied at remote databases, the system avoids transferring unnecessary data over the network, thereby reducing network latency and improving query execution efficiency
Solution Approach 2:
The system introduces an intermediary layer (the query optimization module) that acts between the local database and remote databases. This intermediary analyzes the JOIN statement, generates pushdown filters, and coordinates with remote databases to apply filters before data transfer. This intermediary mechanism enables intelligent data filtering and reduces the volume of data transmitted across the network
2Reliability
If a JOIN statement is executed across tables in different databases, then comprehensive data can be retrieved, but the amount of data fetched from remote sources increases, consuming more network bandwidth and local resources
Solution Approach 1:
The system extracts and identifies pushable predicates from the JOIN statement that can be applied at remote databases. By taking out these filtering conditions and applying them before data transfer, the system removes unnecessary data from the transfer stream while preserving all relevant data needed for the JOIN operation, thus maintaining data completeness while reducing transferred volume
Solution Approach 2:
The system performs preliminary generation of pushdown filters before executing the JOIN query. By pre-analyzing which filters can be pushed to remote databases and applying them in advance, the system ensures that only necessary data is fetched, maintaining data completeness for the JOIN operation while minimizing the quantity of data transferred
3Ease of operation
If traditional JOIN processing is used, then all data from remote tables is fetched, but this causes increased CPU usage and memory consumption at the local database
Solution Approach 1:
The system introduces a query optimization intermediary that analyzes the JOIN statement and generates pushdown filters. This intermediary communicates with remote databases to apply filters before data transfer, reducing the volume of data that needs to be processed locally. This maintains query simplicity from the user perspective while significantly reducing CPU and memory usage at the local database
4Productivity
If no filtering is applied before JOIN, then all data is processed, but this leads to inefficient resource utilization and increased processing time
Solution Approach 1:
The system performs preliminary analysis of the JOIN query to identify and generate pushdown filters before execution. By pre-determining which filters can be applied at remote databases and applying them in advance, the system reduces the volume of data that needs to be transferred and processed locally, thereby improving query processing speed and resource utilization efficiency
Solution Approach 2:
The system changes the parameter of data volume by applying pushdown filters that reduce the amount of data transferred from remote databases. By modifying the data set size through intelligent filtering before transfer, the system improves processing speed and optimizes resource utilization without compromising query results
Data Source
AI summary
Computer implemented methods, systems, and computer program products include program code executing on a processor(s) that obtain a query comprising a JOIN statement that references two or more tables in the one or more remote databases. The program code determines, for each table of the two or more tables, whether executing the JOIN statement utilizes system resources above a pre-defined allotment. Based on determining, for at least one table the program code generates a filter for the at least one table based on an intersection between predicates in the query. The program code applies the filer to the at least one table to fetch results for the query and exclude unrelated data from the fetching.