What is partition pruning in a partitioned table, what does a query have to contain for the database to do it, and how is it different from using an index?

## The idea A partitioned table is one logical table made of several physical tables, each declared to hold a specific slice of the key space: a range of dates, a list of region codes, a hash bucket. Those declarations are not just routing rules for inserts, they are guarantees the optimizer can reason with. If a partition is declared to hold only January and the query asks for rows in March, that partition provably contains nothing of interest and can be dropped from the plan without being opened. That elimination is partition pruning. The payoff is proportional: a table split into 36 monthly partitions where a query touches one month reads roughly 1/36th of the data, and equally importantly touches only that partition's indexes, which are individually far smaller and better cached than one global index would be. ## What the query must contain Pruning is driven by predicates on the **partition key**, that is, the expression the table was partitioned by. Some shapes work naturally: - range partitioning by a timestamp prunes for equality, comparisons and BETWEEN on that timestamp; - list partitioning by a region code prunes for equality and IN lists on that code; - hash partitioning prunes only for equality, because a hash destroys ordering, so a range comparison on a hash-partitioned key cannot exclude anything. If the query filters only on other columns, no pruning happens and every partition is scanned. This is the single most common disappointment with partitioning: teams partition a table, then run queries that never mention the partition key, and performance gets worse rather than better because the planner now handles many relations and takes a lock on each one. ## How it differs from an index They operate at different levels and are complementary. - An **index** is a data structure inside one table that lets a scan jump to matching rows instead of reading everything. It reduces work within a relation. - **Pruning** is an optimizer and executor decision that removes whole relations from consideration. It happens before any data is read from those partitions. A good plan on a partitioned table usually does both: prune to the handful of partitions that could match, then use each surviving partition's index for the rest of the predicate. Saying "partitioning replaces indexing" is wrong; partitioning changes which index is consulted and how big it is, but selective lookups still need indexes. ## How you see it In PostgreSQL a query over a partitioned table normally plans as an **Append** (or Merge Append) node with one child per partition. Pruning shows up as children that simply are not there: only the surviving partitions appear. If exactly one partition survives, the Append disappears entirely and the plan shows that partition's scan node directly, which is a strong visual signal that pruning worked. Other engines print an equivalent, such as a partitions-accessed list in the plan output. ## Related mechanisms and terminology Older PostgreSQL versions eliminated child tables using CHECK constraints on inheritance hierarchies, a planner-only mechanism called **constraint exclusion**, controlled by the `constraint_exclusion` setting. Modern declarative partitioning uses partition bounds directly, is faster, handles more predicate shapes, and is controlled by `enable_partition_pruning` (on by default). Turning that setting off is a debugging tool, and seeing it off in production is a bug. Distinguishing the two mechanisms is a nice signal of depth, but the concept is the same: prove a relation cannot match, then skip it. ## A caution about granularity Pruning improves with more partitions only up to a point. Each partition adds planning work, catalog entries and a lock to acquire per query, so thousands of tiny partitions can cost more in planning than they save in I/O, especially for short OLTP queries. The useful mental model is: pruning turns a table scan into a scan of the relevant slice, and the slice should be big enough to be worth the bookkeeping.