Parameter · Query Tuning / Other Planner Options
constraint_exclusion
Enables the planner to use constraints to optimize queries. Table scans will be skipped if their constraints guarantee that no rows match the query.
- Default:
partition - Type: enum
- Values: partition · on · off
- Applies: Session (any user)
In plain words
If a table has a CHECK constraint like "year = 2024", a query asking for year 2023 cannot find anything in it. Constraint exclusion lets the planner skip such tables. It mostly matters for old-style inheritance partitioning; modern declarative partitioning uses partition pruning instead.
For DBAs and operators
Values: partition (default) applies the check only to inheritance child tables and UNION ALL subqueries; on applies it to every table in every query, which costs planning time for little benefit; off never checks. The planner tries to prove that the WHERE clause contradicts the table's CHECK constraints, which only works when the WHERE clause contains constants (or externally supplied parameters), not with non-immutable functions such as CURRENT_TIMESTAMP.
Declarative partitions (PARTITION BY) are handled by enable_partition_pruning. Verified on 18.6: with pruning off, constraint_exclusion = partition did not remove any of the four partitions.
With on, a query on a regular table with a CHECK constraint like CHECK (status IN ('a','b')) and WHERE status = 'z' becomes a Result node with One-Time Filter: false. Rarely worth the planning overhead except for specific UNION ALL views over constrained tables.
How to change it
Any user can change it for their session with SET.