constraint_exclusion

Definition PG 18 manual

Controls the query planner's use of table constraints to optimize queries. The allowed values of constraint_exclusion are on (examine constraints for all tables), off (never examine constraints), and partition (examine constraints only for inheritance child tables and UNION ALL subqueries). partition is the default setting. It is often used with traditional inheritance trees to improve performance.

When this parameter allows it for a particular table, the planner compares query conditions with the table's CHECK constraints, and omits scanning tables for which the conditions contradict the constraints. For example:

CREATE TABLE parent(key integer, ...);
CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
...
SELECT * FROM parent WHERE key = 2400;

With constraint exclusion enabled, this SELECT will not scan child1000 at all, improving performance.

Currently, constraint exclusion is enabled by default only for cases that are often used to implement table partitioning via inheritance trees. Turning it on for all tables imposes extra planning overhead that is quite noticeable on simple queries, and most often will yield no benefit for simple queries. If you have no tables that are partitioned using traditional inheritance, you might prefer to turn it off entirely. (Note that the equivalent feature for partitioned tables is controlled by a separate parameter, enable_partition_pruning.)

Refer to Section 5.12.5 for more information on using constraint exclusion to implement partitioning.

How it works

constraint_exclusion lets the planner compare query predicates with CHECK constraints and omit relations whose constraints prove that they cannot match. partition limits that work to traditional inheritance children and UNION ALL arms; it is separate from declarative partition pruning.

The proof is attempted at planning time, so enabling it more broadly increases planning work even when no relation can be excluded. It relies on constraints that are visible and logically contradictory to the query conditions.

For declaratively partitioned tables, enable_partition_pruning is the primary control. constraint_exclusion remains useful for inheritance-based partitioning and carefully constructed constraint-backed UNION ALL views. Its user context permits session- or transaction-local changes; newly performed or newly planned work sees the value.

Operational considerations

Treating constraint_exclusion as an executor resource limit rather than a planning assumption or policy.

Testing only one parameter set or one data distribution.

Expecting an already cached plan to be rewritten automatically.

Using a global override to hide stale statistics or fragile SQL structure.

Workload guidance

OLAP: Analytical SQL can make constraint_exclusion more visible because joins, cursors, or recursion are larger. Benchmark the full statement family and inspect estimates rather than copying a single successful value.

OLTP: Keep constraint_exclusion at its upstream default unless representative plans show a repeatable, workload-wide problem. Test a local override first and include planning latency as well as execution latency.

SMALL: On a small host, avoid increasing planning search or memory pressure through constraint_exclusion without a measured benefit. Prefer query-local structure or a scoped role setting over a cluster-wide override.