Controls the query planner's use of table constraints to optimize queries. The allowed values of
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 ddl-partitioning-constraint-exclusion for more information on using constraint exclusion to implement partitioning.
- How to check whether constraint_exclusion is enabled or not in my database?
- PostgreSQL: when we should set constraint_exclusion off?
- Why aren'y my CHECK CONSTRAINTS on child tables being utilized by the planner to reduce the size of the plan?
- PostgreSQL constraint exclusion not working with subquery SELECT IN
- Terrible performance degradation in postgreSQL when i created too many partitions