---
title: "Partition pruning"
description: "Partition pruning is a query optimization that skips reading entire partitions of a dataset when a query's filters make it impossible for those partitions to contain matching rows."
canonical: "https://motherduck.com/glossary/partition-pruning/"
related:
  - title: "DuckLake Lakehouse: Getting Started to Going Fast | MotherDuck"
    url: "https://motherduck.com/videos/ducklake-lakehouse-getting-started/"
  - title: "Optimizing query performance | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/query-performance/"
  - title: "Ingest Partitioned S3 Parquet on a Schedule | MotherDuck Docs"
    url: "https://motherduck.com/docs/cookbook/flight-scheduled-s3-ingest/"
gated_asset:
  title: "DuckLake on MotherDuck"
  url: "https://motherduck.com/product/ducklake/"
---

# Partition pruning

> Partition pruning is a query optimization that skips reading entire partitions of a dataset when a query's filters make it impossible for those partitions to contain matching rows.

## Overview

Large datasets are frequently partitioned — physically split into separate files or directories based on the value of one or more columns, commonly a date. A common convention, popularized by Hive, lays out partitions as directories like `year=2024/month=06/`. Partition pruning uses a query's `WHERE` clause to figure out which partitions could possibly contain matching rows and skips reading every other partition entirely, without even opening those files.

<glossary-callout guide="duckdb-book-brief" />

## How Pruning Works

If a table is partitioned by `year` and a query filters `WHERE year = 2024`, a pruning-aware engine only reads files under the `year=2024/` partition and never touches `year=2023/`, `year=2022/`, and so on. This is far cheaper than filtering after reading, because the pruned partitions' data — and their I/O cost — are avoided completely rather than read and discarded.

## DuckDB and Partition Pruning

DuckDB supports Hive-style partition pruning when reading partitioned Parquet or CSV datasets with `hive_partitioning` enabled (DuckDB also auto-detects Hive partitioning for many layouts):

```sql
SELECT *
FROM read_parquet('s3://bucket/events/*/*.parquet', hive_partitioning = true)
WHERE year = 2024 AND month = 6;
```

DuckDB extracts `year` and `month` as virtual columns from the directory structure and prunes any file paths whose partition values can't satisfy the filter before ever opening them. When writing data out, `COPY ... TO 'path' (FORMAT parquet, PARTITION_BY (year, month))` produces the same Hive-style layout, making downstream partition pruning by DuckDB or other engines possible.