skytf=> create table test (id integer, name varchar(32)); CREATE TABLE skytf=> createindex idx_test_id on test using btree(id); CREATEINDEX
创建三个子表
1 2 3 4 5 6 7 8 9 10 11 12
skytf=> create table test_a (like test including constraints including defaults including indexes ) inherits (test ); NOTICE: merging column "id" with inherited definition NOTICE: merging column "name" with inherited definition CREATE TABLE skytf=> create table test_b (like test including constraints including defaults including indexes ) inherits (test ); NOTICE: merging column "id" with inherited definition NOTICE: merging column "name" with inherited definition CREATE TABLE skytf=> create table test_c (like test including constraints including defaults including indexes ) inherits (test ); NOTICE: merging column "id" with inherited definition NOTICE: merging column "name" with inherited definition CREATE TABLE
设置约束
1 2 3 4 5 6
skytf=> alter table test_a add constraint con_test_a check (id >=1 and id <=1000); ALTER TABLE skytf=> alter table test_b add constraint con_test_b check (id >=1001 and id <=2000); ALTER TABLE skytf=> alter table test_c add constraint con_test_c check (id >=2001 and id <=3000); ALTER TABLE
skytf=> /d test Table "skytf.test" Column | Type | Modifiers --------+-----------------------+----------- id | integer | name | character varying(32) | Indexes: "idx_test_id" btree (id) Number of child tables: 3 (Use d+ to list them.)
skytf=> /d test_a Table "skytf.test_a" Column | Type | Modifiers --------+-----------------------+----------- id | integer | name | character varying(32) | Indexes: "test_a_id_idx" btree (id) Check constraints: "con_test_a" CHECK (id >= 1 AND id <= 1000) Inherits: test
skytf=> /d test_b; Table "skytf.test_b" Column | Type | Modifiers --------+-----------------------+----------- id | integer | name | character varying(32) | Indexes: "test_b_id_idx" btree (id) Check constraints: "con_test_b" CHECK (id >= 1001 AND id <= 2000) Inherits: test
skytf=> /d test_c; Table "skytf.test_c" Column | Type | Modifiers --------+-----------------------+----------- id | integer | name | character varying(32) | Indexes: "test_c_id_idx" btree (id) Check constraints: "con_test_c" CHECK (id >= 2001 AND id <= 3000) Inherits: test
skytf=> show constraint_exclusion; constraint_exclusion ---------------------- off (1 row)
skytf=> explain select * From test where id=1; QUERY PLAN --------------------------------------------------------------------------------------------- Result (cost=2.28..20.60 rows=7 width=53) -> Append (cost=2.28..20.60 rows=7 width=53) -> Bitmap Heap Scan on test (cost=2.28..7.80 rows=4 width=86) Recheck Cond: (id = 1) -> Bitmap Index Scan on idx_test_id (cost=0.00..2.28 rows=4 width=0) Index Cond: (id = 1) -> Index Scan using test_a_id_idx on test_a test (cost=0.00..4.27 rows=1 width=8) Index Cond: (id = 1) -> Index Scan using test_b_id_idx on test_b test (cost=0.00..4.27 rows=1 width=8) Index Cond: (id = 1) -> Index Scan using test_c_id_idx on test_c test (cost=0.00..4.27 rows=1 width=8) Index Cond: (id = 1) (12 rows)
skytf=> show constraint_exclusion; constraint_exclusion ---------------------- partition (1 row)
skytf=> explain select * From test where id=1; QUERY PLAN --------------------------------------------------------------------------------------------- Result (cost=2.28..12.07 rows=5 width=70) -> Append (cost=2.28..12.07 rows=5 width=70) -> Bitmap Heap Scan on test (cost=2.28..7.80 rows=4 width=86) Recheck Cond: (id = 1) -> Bitmap Index Scan on idx_test_id (cost=0.00..2.28 rows=4 width=0) Index Cond: (id = 1) -> Index Scan using test_a_id_idx on test_a test (cost=0.00..4.27 rows=1 width=8) Index Cond: (id = 1) (8 rows)
constraint_exclusion (enum) Controls the query planner iss 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. When this parameter allows it for a particular table, the planner compares query conditions with the table is CHECK constraints, and omits scanning tables for which the conditions contradict the constraints.
总结
constraint_exclusion 的含义是：当PG生产执行计划时是否考虑表上的约束，这个参数有三个选项 “off,on ,partition” ，默认参数为 off, 意思不使用表上的 constraint 来生成计划，如果设置成 on ,则对所有表生效，生成 PLAN 时会考虑表上的 constraint, 建议设置成 partition,只对分区表生效，从而避免扫描分区表所有分区。
