我的数据库版本为PostgreSQL 8.4.4，
分区表定义如下：
pg# \d db_stats_keepalive
Table "public.db_stats_keepalive"
Column | Type | Modifiers
--------------+-----------------------+-----------
hostname | character varying(30) |
stat_id | integer |
collect_time | integer |
child_stat | character varying(64) |
perf_value | bigint |
Rules:
db_stats_keepalive_insert_p20100107 AS
ON INSERT TO db_stats_keepalive
WHERE new.collect_time >= 1275840000 AND new.collect_time < 1275926400 DO INSTEAD INSERT INTO db_stats_keepalive_p20100607 (hostname, stat_id, collect_time, child_stat, perf_value)
VALUES (new.hostname, new.stat_id, new.collect_time, new.child_stat, new.perf_value)
db_stats_keepalive_insert_p20100108 AS
ON INSERT TO db_stats_keepalive
WHERE new.collect_time >= 1275926400 AND new.collect_time < 1276012800 DO INSTEAD INSERT INTO db_stats_keepalive_p20100608 (hostname, stat_id, collect_time, child_stat, perf_value)
VALUES (new.hostname, new.stat_id, new.collect_time, new.child_stat, new.perf_value)
db_stats_keepalive_insert_p20100605 AS
ON INSERT TO db_stats_keepalive
WHERE new.collect_time >= 1275667200 AND new.collect_time < 1275753600 DO INSTEAD INSERT INTO db_stats_keepalive_p20100605 (hostname, stat_id, collect_time, child_stat, perf_value)
VALUES (new.hostname, new.stat_id, new.collect_time, new.child_stat, new.perf_value)
db_stats_keepalive_insert_p20100606 AS
ON INSERT TO db_stats_keepalive
WHERE new.collect_time >= 1275753600 AND new.collect_time < 1275840000 DO INSTEAD INSERT INTO db_stats_keepalive_p20100606 (hostname, stat_id, collect_time, child_stat, perf_value)
VALUES (new.hostname, new.stat_id, new.collect_time, new.child_stat, new.perf_value)
db_stats_keepalive_insert_p20100609 AS
ON INSERT TO db_stats_keepalive
WHERE new.collect_time >= 1276012800 AND new.collect_time < 1276099200 DO INSTEAD INSERT INTO db_stats_keepalive_p20100609 (hostname, stat_id, collect_time, child_stat, perf_value)
VALUES (new.hostname, new.stat_id, new.collect_time, new.child_stat, new.perf_value)
db_stats_keepalive_insert_p20100610 AS
ON INSERT TO db_stats_keepalive
WHERE new.collect_time >= 1276099200 AND new.collect_time < 1276185600 DO INSTEAD INSERT INTO db_stats_keepalive_p20100610 (hostname, stat_id, collect_time, child_stat, perf_value)
VALUES (new.hostname, new.stat_id, new.collect_time, new.child_stat, new.perf_value)
db_stats_keepalive_insert_p20100611 AS
ON INSERT TO db_stats_keepalive
WHERE new.collect_time >= 1276185600 AND new.collect_time < 1276272000 DO INSTEAD INSERT INTO db_stats_keepalive_p20100611 (hostname, stat_id, collect_time, child_stat, perf_value)
VALUES (new.hostname, new.stat_id, new.collect_time, new.child_stat, new.perf_value)
dsc=# \d db_stats_keepalive_p20100609
Table "public.db_stats_keepalive_p20100609"
Column | Type | Modifiers
--------------+-----------------------+-----------
hostname | character varying(30) |
stat_id | integer |
collect_time | integer |
child_stat | character varying(64) |
perf_value | bigint |
Indexes:
"idx_db_stats_keepalive_p20100609" btree (collect_time)
Check constraints:
"db_stats_keepalive_p20100609_collect_time_check" CHECK (collect_time >= 1276012800 AND collect_time < 1276099200)
Inherits: db_stats_keepalive
其中表db_stats_keepalive是按unix时间进行分区的，一天一个分区，因此增加了unix时间函数：
CREATE FUNCTION unix_timestamp() RETURNS integer AS $$
SELECT (date_part('epoch',now()))::integer;
$$ LANGUAGE SQL IMMUTABLE;
一个sql的执行计划如下：
explain
select hostname,min(perf_value) min_conns,max(perf_value) max_conns,avg(perf_value) avg_conns
from db_stats_keepalive
where stat_id=1000
and collect_time >= unix_timestamp(date(now() - interval '1 day'))
and collect_time < unix_timestamp(date(now()))
and child_stat='logons_current'
group by hostname;
pg=# explain
pg-# select hostname,min(perf_value) min_conns,max(perf_value) max_conns,avg(perf_value) avg_conns
pg-# from db_stats_keepalive
pg-# where stat_id=1000
pg-# and collect_time >= unix_timestamp(date(now() - interval '1 day'))
pg-# and collect_time < unix_timestamp(date(now()))
pg-# and child_stat='logons_current'
pg-# group by hostname;
Q
UERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------
---------
HashAggregate (cost=362634.66..362638.16 rows=200 width=17)
-> Append (cost=0.00..361314.45 rows=132021 width=17)
-> Seq Scan on db_stats_keepalive (cost=0.00..62.25 rows=23 width=17)
Filter: ((stat_id = 1000) AND ((child_stat)::text = 'logons_current'::text) AND (collect_time < (date_part('epoch'::text, (date(now()))::ti
mestamp without time zone))::integer) AND (collect_time >= (date_part('epoch'::text, (date((now() - '1 day'::interval)))::timestamp without time zone))::i
nteger))
-> Seq Scan on db_stats_keepalive_p20100609 db_stats_keepalive (cost=0.00..361197.76 rows=131992 width=17)
Filter: ((stat_id = 1000) AND ((child_stat)::text = 'logons_current'::text) AND (collect_time < (date_part('epoch'::text, (date(now()))::ti
mestamp without time zone))::integer) AND (collect_time >= (date_part('epoch'::text, (date((now() - '1 day'::interval)))::timestamp without time zone))::i
nteger))
-> Index Scan using idx_db_stats_keepalive_p20100608 on db_stats_keepalive_p20100608 db_stats_keepalive (cost=0.03..9.09 rows=1 width=17)
Index Cond: ((collect_time >= (date_part('epoch'::text, (date((now() - '1 day'::interval)))::timestamp without time zone))::integer) AND (c
ollect_time < (date_part('epoch'::text, (date(now()))::timestamp without time zone))::integer))
Filter: ((stat_id = 1000) AND ((child_stat)::text = 'logons_current'::text))
-> Index Scan using idx_db_stats_keepalive_p20100607 on db_stats_keepalive_p20100607 db_stats_keepalive (cost=0.03..9.01 rows=1 width=17)
Index Cond: ((collect_time >= (date_part('epoch'::text, (date((now() - '1 day'::interval)))::timestamp without time zone))::integer) AND (c
ollect_time < (date_part('epoch'::text, (date(now()))::timestamp without time zone))::integer))
Filter: ((stat_id = 1000) AND ((child_stat)::text = 'logons_current'::text))
-> Index Scan using idx_db_stats_keepalive_p20100610 on db_stats_keepalive_p20100610 db_stats_keepalive (cost=0.03..8.63 rows=1 width=17)
Index Cond: ((collect_time >= (date_part('epoch'::text, (date((now() - '1 day'::interval)))::timestamp without time zone))::integer) AND (c
ollect_time < (date_part('epoch'::text, (date(now()))::timestamp without time zone))::integer))
Filter: ((stat_id = 1000) AND ((child_stat)::text = 'logons_current'::text))
-> Index Scan using idx_db_stats_keepalive_p20100606 on db_stats_keepalive_p20100606 db_stats_keepalive (cost=0.03..9.00 rows=1 width=17)
Index Cond: ((collect_time >= (date_part('epoch'::text, (date((now() - '1 day'::interval)))::timestamp without time zone))::integer) AND (c
ollect_time < (date_part('epoch'::text, (date(now()))::timestamp without time zone))::integer))
Filter: ((stat_id = 1000) AND ((child_stat)::text = 'logons_current'::text))
-> Index Scan using idx_db_stats_keepalive_p20100605 on db_stats_keepalive_p20100605 db_stats_keepalive (cost=0.03..9.00 rows=1 width=17)
Index Cond: ((collect_time >= (date_part('epoch'::text, (date((now() - '1 day'::interval)))::timestamp without time zone))::integer) AND (c
ollect_time < (date_part('epoch'::text, (date(now()))::timestamp without time zone))::integer))
Filter: ((stat_id = 1000) AND ((child_stat)::text = 'logons_current'::text))
-> Bitmap Heap Scan on db_stats_keepalive_p20100611 db_stats_keepalive (cost=4.30..9.71 rows=1 width=86)
Recheck Cond: ((collect_time >= (date_part('epoch'::text, (date((now() - '1 day'::interval)))::timestamp without time zone))::integer) AND
(collect_time < (date_part('epoch'::text, (date(now()))::timestamp without time zone))::integer))
Filter: ((stat_id = 1000) AND ((child_stat)::text = 'logons_current'::text))
-> Bitmap Index Scan on idx_db_stats_keepalive_p20100611 (cost=0.00..4.30 rows=2 width=0)
Index Cond: ((collect_time >= (date_part('epoch'::text, (date((now() - '1 day'::interval)))::timestamp without time zone))::integer)
AND (collect_time < (date_part('epoch'::text, (date(now()))::timestamp without time zone))::integer))
(26 rows)
可以看到执行计划对所有的分区表都进行了扫描，开始以为是constraint_exclusion参数设置不正确，检查：
pg=# show constraint_exclusion;
constraint_exclusion
----------------------
partition
(1 row)
发现参数设置正确，没有问题，这是为什么呢？
原来要想使用约束排除时要避免使用NOW()、CURRENT_DATE() 这样的"不稳定的（unstable）"函数，否则就会出现我遇到的这个问题，解决方法是如now()这样的函数移到自定义函数中，如上例，增加一个函数：
CREATE FUNCTION unix_yestoday() RETURNS integer AS $$
SELECT (date_part('epoch',now()- interval '1 day'))::integer;
$$ LANGUAGE SQL IMMUTABLE;
SQL改成：
select hostname,min(perf_value) min_conns,max(perf_value) max_conns,avg(perf_value) avg_conns
from db_stats_keepalive
where stat_id=1000
and collect_time >= unix_today() - 86400
and collect_time < unix_today()
and child_stat='logons_current'
group by hostname;
再看执行计划：
pg=# explain
pg-# select hostname,min(perf_value) min_conns,max(perf_value) max_conns,avg(perf_value) avg_conns
pg-# from db_stats_keepalive
pg-# where stat_id=1000
pg-# and collect_time >= unix_today() - 86400
pg-# and collect_time < unix_today()
pg-# and child_stat='logons_current'
pg-# group by hostname;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
---
HashAggregate (cost=193951.89..193955.39 rows=200 width=17)
-> Append (cost=0.00..192631.74 rows=132015 width=17)
-> Seq Scan on db_stats_keepalive (cost=0.00..32.00 rows=23 width=17)
Filter: ((collect_time >= 1276012800) AND (collect_time < 1276099200) AND (stat_id = 1000) AND ((child_stat)::text = 'logons_current'::text
))
-> Seq Scan on db_stats_keepalive_p20100609 db_stats_keepalive (cost=0.00..192599.74 rows=131992 width=17)
Filter: ((collect_time >= 1276012800) AND (collect_time < 1276099200) AND (stat_id = 1000) AND ((child_stat)::text = 'logons_current'::text
))
(6 rows)
这时看到执行计划正确的执行了。
