The basic idea of predicate pushdown is that certain parts of SQL queries (the predicates) can be “pushed” to where the data lives. This optimization can drastically reduce query/processing time by filtering out data earlier rather than later. Depending on the processing framework, predicate pushdown can optimize your query by doing things like filtering data before it is transferred over the network, filtering data before loading into memory, or skipping reading entire files or chunks of files.
A “predicate” (in mathematics and functional programming) is a function that returns a boolean (true or false). In SQL queries predicates are usually encountered in the
WHERE clause and are used to filter data.
Predicate Pushdown in Hive
Generally when executing SQL queries, a
JOIN will be performed before the filtering used in the
WHERE clause. In Hive (MapReduce), predicate pushdown is used to filter data in the map phase before sending over the network to the reduce phase.
For example in this query the
WHERE a.country = 'Argentina' will be evaluated in the map phase, reducing the amount data sent over the network:
SELECT a.* FROM table1 a JOIN table2 b ON a.id = b.id WHERE a.country = 'Argentina';
Predicate Pushdown in Parquet/ORC files
Parquet and ORC files maintain various stats about each column in different chunks of data (such as min and max values). Programs reading these files can use these indexes to determine if certain chunks, and even entire files, need to be read at all. This allows programs to potentially skip over huge portions of the data during processing.
Predicate Pushdown in Spark
Spark will attempt to move filtering of data as close to the source as possible to avoid loading unnecessary data into memory.
Predicate Pushdown in Amazon Redshift Spectrum
Amazon split testing Redshift Spectrum resides on dedicated servers separate from actual Redshift clusters. Redshift Spectrum will use predicate pushdown to filter data at the Redshift Spectrum layer to reduce data transfer, storage, and compute resources on the Redshift cluster itself.
This Post Has One Comment
## FYI for those who are curious whether this kind optimizations can be used in Oracle databases too: ##
The equivalent of “predicate pushdown” in Oracle Exadata (a machine engineered from nose to toes to run Oracle Database) would be “smart scan”. And the Exadata equivalent for “the min and max values maintained by Parquet and ORC” would be “storage indexes”.