PostgreSQL学习手册(表的继承和分区)(7)

来源：未知责任编辑：责任编辑发表时间:2013-11-15 19:51　点击:次

你可以使用EXPLAIN命令显示一个规划在constraint_exclusion打开和关闭情况下的不同。用上面方法设置的表的典型的缺省规划是：

SET constraint_exclusion = off;

EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';

QUERY PLAN

-----------------------------------------------------------------------------------------------

Aggregate (cost=158.66..158.68 rows=1 width=0)

-> Append (cost=0.00..151.88 rows=2715 width=0)

-> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= '2006-01-01'::date) www.2cto.com

-> Seq Scan on measurement_yy04mm02 measurement (cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= '2006-01-01'::date)

-> Seq Scan on measurement_yy04mm03 measurement (cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= '2006-01-01'::date)

...

-> Seq Scan on measurement_yy05mm12 measurement (cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= '2006-01-01'::date)

-> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= '2006-01-01'::date)

从上面的查询计划中可以看出，PostgreSQL扫描了所有分区。下面我们再看一下打开约束排除之后的查询计划：

SET constraint_exclusion = on;

EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';

QUERY PLAN www.2cto.com

-----------------------------------------------------------------------------------------------

Aggregate (cost=63.47..63.48 rows=1 width=0)

-> Append (cost=0.00..60.75 rows=1086 width=0)