constraint_exclusion controls a planner trick: when a table carries a CHECK constraint, the planner can compare that constraint against your query’s WHERE clause and, if the two contradict, skip scanning the table altogether. A CHECK (key BETWEEN 1000 AND 1999) on a child table means a query for key = 2400 provably has nothing to find there, so the planner doesn’t look. The values are on (examine constraints on every table), off (examine none), and partition (examine them only for inheritance children and UNION ALL arms). The default is partition and the context is user.
If that sounds like partitioning, it’s because for about a decade it was partitioning.
Before PostgreSQL 10, the database had no native concept of a partitioned table. What it had was table inheritance, and “partitioning” was a recipe you assembled by hand: create an empty parent table; create child tables that INHERITS from it, each carrying a CHECK constraint describing its slice of the key space; write a trigger or rule on the parent to route every INSERT to the correct child; and then lean on constraint_exclusion so that SELECTs with a predicate on the partition key would skip the children that couldn’t match. It worked, and an enormous number of production systems ran on exactly this. It was also entirely manual, and it didn’t scale. Because the planner examines the constraints of every child on every query, planning time grew with the number of children, and the practical ceiling sat somewhere around a hundred partitions before planning overhead became the problem you’d set out to avoid.
What it actually controls now, which is less than you’d think
PostgreSQL 10 introduced declarative partitioning: CREATE TABLE ... PARTITION BY RANGE (and LIST; HASH arrived in 11), with tuple routing built into the server so the trigger disappeared. But version 10 still pruned partitions the old way, through constraint exclusion under the covers. The decisive change came in PostgreSQL 11, which replaced that machinery with a purpose-built partition pruning engine. Rather than examine a CHECK-shaped constraint on each partition in turn, it compares the query’s values directly against the sorted partition bounds with a binary search, turning an O(n) planning cost into O(log n) and making thousands of partitions practical. It also prunes at execution time, for values that aren’t known when the query is planned: parameters in a prepared statement, or a value handed across a join. Constraint exclusion only ever ran at plan time. Runtime pruning is the capability it never had.
The consequence is the part people miss. On a modern PostgreSQL using declarative partitioning, constraint_exclusion has nothing to do with your partitions. Elimination for a PARTITION BY table is governed by a different parameter, enable_partition_pruning, which is on by default. If your partitions aren’t being pruned and you reach for constraint_exclusion, you are turning the wrong knob; the answer lives in enable_partition_pruning, in your EXPLAIN output, and in whether your WHERE clause actually constrains the partition key.
What constraint_exclusion still governs is the old stuff: genuine inheritance trees with hand-written CHECK constraints, and UNION ALL subqueries. The default partition confines the work to exactly those cases, which is why it has been the correct setting for years. Setting it to on taxes the planning of every ordinary query to check constraints that will almost never help. Setting it to off is defensible only if you have no inheritance-based partitioning anywhere, and even then it buys back a sliver of planning time you’ll struggle to measure. Leave it at partition. The interesting decision it once represented now belongs to enable_partition_pruning, and this parameter has quietly become a well-preserved fossil of how we used to do things.