# Dynamic partition pruning

DevFeed: [Dynamic partition pruning](<https://devfeed.tech/articles/dynamic-partition-pruning-8639.md>)

Original publisher: [Read original article](<https://trino.io/blog/2020/06/14/dynamic-partition-pruning.html>)

Author: Raunaq Morarka, Qubole and Karol Sobczak, Starburst Data

Published: 2020-06-14T00:00:00Z

Content type: article

Language: en

Sources: [Trino Blog](<https://devfeed.tech/sources/trino-blog.md>)

Topics: [data](<https://devfeed.tech/topics/data.md>), [parquet](<https://devfeed.tech/topics/parquet.md>)

Tags: [communication](<https://devfeed.tech/tags/communication.md>), [data](<https://devfeed.tech/tags/data.md>), [design](<https://devfeed.tech/tags/design.md>), [optimization](<https://devfeed.tech/tags/optimization.md>), [parquet](<https://devfeed.tech/tags/parquet.md>), [storage](<https://devfeed.tech/tags/storage.md>)

## AI overview

The article describes dynamic partition pruning for Presto. Workers collect join values from a filtered dimension table, allowing the coordinator to skip fact-table partitions that cannot satisfy the join and reduce scanned data.

## Source excerpt

Star-schema is one of the most widely used data mart patterns. The star schema consists of fact tables (usually partitioned) and dimension tables, which are used to filter rows from fact tables. Consider the following query which captures a common pattern of a fact table store_sales partitioned by the column ss_sold_date_sk joined with a filtered dimension table date_dim: SELECT COUNT(*) FROM store_sales JOIN date_dim ON store_sales.ss_sold_date_sk = date_dim.d_date_sk WHERE d_following_holiday='Y' AND d_year = 2000; Without dynamic filtering, Presto will push predicates for the dimension table to the table scan on date_dim but it will scan all the data in the fact table since there are no filters on store_sales in the query. The join operator will end up throwing away most of the probe-side rows as the join criteria is highly selective. The current implementation of dynamic filtering improves on this, however it is limited only to broadcast joins on tables stored in ORC or Parquet format. Additionally, it does not take advantage of the layout of partitioned Hive tables. With dynamic partition pruning, which extends the current implementation of dynamic filtering, every worker node collects values eligible for the join from date_dim.d_date_sk column and passes it to the coordinator. Coordinator can then skip processing of the partitions of store_sales which don't meet the join criteria. This greatly reduces the amount of data scanned from store_sales table by worker nodes. This optimization is applicable to any storage format and to both broadcast and partitioned join.