{"Entry":{"collection":"guc","key":"constraint_exclusion","name":"constraint_exclusion","aliases":[],"metadata":{"baseline":false,"boot_human":"Not specified","boot_val":null,"category":"Query Tuning / Other Planner Options","category_zh":"","changed_in":[],"changes":[{"documentation_changed":false,"fields":{},"from":"8.0","status":"added","to":"8.1"},{"documentation_changed":true,"fields":{},"from":"8.1","status":"changed","to":"8.2"},{"documentation_changed":true,"fields":{},"from":"8.2","status":"changed","to":"8.3"},{"documentation_changed":true,"fields":{},"from":"8.3","status":"changed","to":"8.4"},{"documentation_changed":true,"fields":{},"from":"8.4","status":"changed","to":"9.0"},{"documentation_changed":true,"fields":{},"from":"9.4","status":"changed","to":"9.5"},{"documentation_changed":true,"fields":{},"from":"10","status":"changed","to":"11"},{"documentation_changed":true,"fields":{},"from":"11","status":"changed","to":"12"},{"documentation_changed":true,"fields":{},"from":"16","status":"changed","to":"17"},{"documentation_changed":true,"fields":{},"from":"18","status":"changed","to":"19"}],"content_hash":"3ae5a65e2e865492203a784004b34b0b9079b14406511873010ded3a4bb604c1","context":"","default_changed_in":[],"default_history":[{"from":"9.0","to":"19","value":"partition"}],"editorial":{"advice":{"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."},"mechanism":["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."],"pitfalls":["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."],"references":[{"title":"PostgreSQL 19 Beta 4: constraint_exclusion","url":"https://www.postgresql.org/docs/19/runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION"},{"title":"PostgreSQL 19 release notes","url":"https://www.postgresql.org/docs/19/release-19.html"}],"related":["enable_partition_pruning","from_collapse_limit","join_collapse_limit","default_statistics_target"],"summary":"constraint_exclusion — Enables the planner to use constraints to optimize queries. Observed in PG9.0–19 Beta 4; its last measured boot default is partition in PG19 Beta 4, with user context. This is a beta-snapshot fact and can change before PostgreSQL 19 GA."},"enumvals":[],"first_version":"8.1","group":"Query Tuning","group_slug":"query","imported_at":"2026-09-27T17:57:30.982619+08:00","intro_commit":{},"key":"constraint_exclusion","last_version":"20","max_val":"","min_val":"","name":"constraint_exclusion","position":69,"present_in":["8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"short_desc":"Controls the query planner's use of table constraints to optimize queries.","short_desc_zh":"","source_rev":"english-manuals:2bf8a74bf8b745bad29c5639a2cbe312a76e4c0d9c5164d6288877673ea61bd9","unit":"","vartype":"enum"}},"Definition":{"Collection":"guc","Key":"constraint_exclusion","SourceDatabase":"center","Version":"18","SourceTable":"guc","SourceKey":"constraint_exclusion","SourceRevision":"english-manuals:2bf8a74bf8b745bad29c5639a2cbe312a76e4c0d9c5164d6288877673ea61bd9","Facts":{"boot_val":"partition","category":"Query Tuning / Other Planner Options","context":"user","description":"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.","doc":{"anchor":"GUC-CONSTRAINT-EXCLUSION","file":"runtime-config-query.html","lang":"en","sha256":"33456ca7ef52bf28e93e34b932a411db7a1b95800179add2cf867dc6ec9144bc","slug":"18"},"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":null,"metadata_version":"18","min_val":null,"name":"constraint_exclusion","short_desc":"Enables the planner to use constraints to optimize queries.","source":"pg-settings-source-snapshot","unit":null,"vartype":"enum"},"ManualEvidence":{"doc":{"anchor":"GUC-CONSTRAINT-EXCLUSION","file":"runtime-config-query.html","lang":"en","sha256":"33456ca7ef52bf28e93e34b932a411db7a1b95800179add2cf867dc6ec9144bc","slug":"18"}},"MeasuredEvidence":{"metadata_version":"18"}},"Text":{"Collection":"guc","Key":"constraint_exclusion","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"constraint_exclusion","Summary":"","BodyHTML":"\u003cp\u003e控制查询规划器对表约束的使用，以优化查询。\u003ccode\u003econstraint_exclusion\u003c/code\u003e的允许值是\u003ccode\u003eon\u003c/code\u003e（对所有表检查约束）、\u003ccode\u003eoff\u003c/code\u003e（从不检查约束）和\u003ccode\u003epartition\u003c/code\u003e（只对继承的子表和\u003ccode\u003eUNION ALL\u003c/code\u003e子查询检查约束）。\u003ccode\u003epartition\u003c/code\u003e是默认设置。它通常与传统的继承树一起使用来提高性能。\u003c/p\u003e\u003cp\u003e当此参数允许对某个表使用约束排除时，规划器会比较查询条件与该表的\u003ccode\u003eCHECK\u003c/code\u003e约束，并且忽略那些条件违反约束的表扫描。例如：\u003c/p\u003e\u003cpre\u003eCREATE TABLE parent(key integer, ...);\nCREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);\nCREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);\n...\nSELECT * FROM parent WHERE key = 2400;\n\u003c/pre\u003e\u003cp\u003e在启用约束排除时，这个\u003ccode\u003eSELECT\u003c/code\u003e将完全不会扫描\u003ccode\u003echild1000\u003c/code\u003e，从而提高性能。\u003c/p\u003e\u003cp\u003e目前，约束排除仅在通常用于通过继承树实现表分区的情况下默认启用。为所有表启用它会增加额外的规划开销，这在简单查询上相当明显，而且通常不会为简单查询带来好处。如果没有通过传统继承方式进行分区的表，你可能希望完全关闭它。（注意，分区表的等效功能由另一个参数\u003ca href=\"/docs/18/runtime-config-query.html#GUC-ENABLE-PARTITION-PRUNING\" rel=\"nofollow\"\u003eenable_partition_pruning\u003c/a\u003e控制。）\u003c/p\u003e\u003cp\u003e更多关于使用约束排除实现分区的信息请参阅\u003ca href=\"/docs/18/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION\" rel=\"nofollow\"\u003e第 5.12.5 节\u003c/a\u003e。\u003c/p\u003e","SourceRevision":"2026-09-11@29c86d9","ContentHash":"3b87c5e7bb6d8b2fa083ea6bb7a1195312728f218e56ca1034ed9ff7be6e35d0","Payload":{"carried_from":"","carry_reason":"","doc_html":"\u003cp\u003e控制查询规划器对表约束的使用，以优化查询。\u003ccode class=\"varname\"\u003econstraint_exclusion\u003c/code\u003e的允许值是\u003ccode class=\"literal\"\u003eon\u003c/code\u003e（对所有表检查约束）、\u003ccode class=\"literal\"\u003eoff\u003c/code\u003e（从不检查约束）和\u003ccode class=\"literal\"\u003epartition\u003c/code\u003e（只对继承的子表和\u003ccode class=\"literal\"\u003eUNION ALL\u003c/code\u003e子查询检查约束）。\u003ccode class=\"literal\"\u003epartition\u003c/code\u003e是默认设置。它通常与传统的继承树一起使用来提高性能。\u003c/p\u003e\u003cp\u003e当此参数允许对某个表使用约束排除时，规划器会比较查询条件与该表的\u003ccode class=\"literal\"\u003eCHECK\u003c/code\u003e约束，并且忽略那些条件违反约束的表扫描。例如：\u003c/p\u003e\u003cpre\u003eCREATE TABLE parent(key integer, ...);\nCREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);\nCREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);\n...\nSELECT * FROM parent WHERE key = 2400;\n\u003c/pre\u003e\u003cp\u003e在启用约束排除时，这个\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e将完全不会扫描\u003ccode class=\"structname\"\u003echild1000\u003c/code\u003e，从而提高性能。\u003c/p\u003e\u003cp\u003e目前，约束排除仅在通常用于通过继承树实现表分区的情况下默认启用。为所有表启用它会增加额外的规划开销，这在简单查询上相当明显，而且通常不会为简单查询带来好处。如果没有通过传统继承方式进行分区的表，你可能希望完全关闭它。（注意，分区表的等效功能由另一个参数\u003ca href=\"/docs/18/runtime-config-query.html#GUC-ENABLE-PARTITION-PRUNING\"\u003eenable_partition_pruning\u003c/a\u003e控制。）\u003c/p\u003e\u003cp\u003e更多关于使用约束排除实现分区的信息请参阅\u003ca href=\"/docs/18/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION\" title=\"5.12.5. 分区和约束排除\"\u003e第 5.12.5 节\u003c/a\u003e。\u003c/p\u003e","doc_same_as":""}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}