Controls the query planner’s use of table constraints to optimize queries.
At a glance
| Property | Value |
|---|---|
| Parameter | constraint_exclusion |
| Category | Query Planning |
| Default | partition |
| Value type | enum |
| Change scope | Per-session (SET) |
| Available in | PostgreSQL 12, 13, 14, 15, 16, 17, 18, 19 (added in 12) |
What it does
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.
(Description quoted from the official PostgreSQL documentation.)
How to apply a change
Can be set per session with SET, per role/database with ALTER ROLE/DATABASE ... SET, or globally in postgresql.conf.
Inspect the current value and source with SHOW constraint_exclusion; or SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'constraint_exclusion';.
Tuning guidance
Leave at partition for normal partitioning; set on only when relying on CHECK-constraint exclusion on inheritance trees, since on adds planner work to every query.