PostgreSQL分区表的执行计划问题
时间：2010-06-10 来源：osdba
调试一个分区表时，发现约束排除不生效，总是把所有分区表都扫描一遍：
我的数据库版本为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)
这时看到执行计划正确的执行了。
分区表定义如下： 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)
这时看到执行计划正确的执行了。
相关阅读 更多 +
