Wiki / Configuration Parameters

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.

English manual coverage begins with PostgreSQL 7.4. Source history and runtime observations have their own recorded version boundaries.

boot val

partition

category

Query Tuning / Other Planner Options

context

user

documented

true

enumvals

partition, on, off

extra desc

Table scans will be skipped if their constraints guarantee that no rows match the query.

lang

en

max val

Unknown

metadata version

18

min val

Unknown

name

constraint_exclusion

short desc

Enables the planner to use constraints to optimize queries.

source

pg-settings-source-snapshot

unit

Unknown

vartype

enum