postgresql - Why aren'y my CHECK CONSTRAINTS on child tables being utilized by the planner to reduce the size of the plan? -
i have table partitioned month , used holding apache log information. when run explain simple query has clause on same field partition checks, same plan whether constraint_exclusion on or off. below master table info , 2 examples of child tables info feb , mar of 2010. overall, there child table each month feb of 2010 through june of 2011. on average, each child table has around 100m records. below output of explain simple query, once constraint_exclusion on , once off. unfortunately, check constraints aren't being used reduce plan size. due fact request_dt datatype timestamp time zone check constraints dates? other thoughts? have not yet created indexes on request_dt, according documentation isn't necessary. i'll adding them, wouldn't think should impact use of check constraints.
i'm using postgres 8.3.6.
spatial_data=# \d rpt.websvcs_logs table "rpt.websvcs_logs" column | type | modifiers --------------------------+-----------------------------+--------------- id | bigint | ins_ts | timestamp without time zone | default now() server | text | host | text | request_dt | timestamp time zone | method | text | url | text | api_method | text | api_key | text | geo_type | text | geo_name | text | radius | text | lat | text | long | text | id_param | text | state | text | max | text | sort_by | text | sort_dir | text | rpp | text | page | text | ver | text | output | text | http_ver | text | status | text | size | text | x_forwarded_for | text | referrer | text | agent | text | accept_encoding | text | processing_time_sec | text | processing_time_microsec | text | spatial_data=# \d rpt.websvcs_logs_201102 table "rpt.websvcs_logs_201102" column | type | modifiers --------------------------+-----------------------------+--------------- id | bigint | not null ins_ts | timestamp without time zone | default now() server | text | host | text | request_dt | timestamp time zone | method | text | url | text | api_method | text | api_key | text | geo_type | text | geo_name | text | radius | text | lat | text | long | text | id_param | text | state | text | max | text | sort_by | text | sort_dir | text | rpp | text | page | text | ver | text | output | text | http_ver | text | status | text | size | text | x_forwarded_for | text | referrer | text | agent | text | accept_encoding | text | processing_time_sec | text | processing_time_microsec | text | indexes: "pk_websvcs_logs_201102_id" primary key, btree (id) check constraints: "request_dt" check (request_dt >= '2011-02-01'::date , request_dt < '2011-03-01'::date) inherits: rpt.websvcs_logs spatial_data=# \d rpt.websvcs_logs_201103 table "rpt.websvcs_logs_201103" column | type | modifiers --------------------------+-----------------------------+--------------- id | bigint | not null ins_ts | timestamp without time zone | default now() server | text | host | text | request_dt | timestamp time zone | method | text | url | text | api_method | text | api_key | text | geo_type | text | geo_name | text | radius | text | lat | text | long | text | id_param | text | state | text | max | text | sort_by | text | sort_dir | text | rpp | text | page | text | ver | text | output | text | http_ver | text | status | text | size | text | x_forwarded_for | text | referrer | text | agent | text | accept_encoding | text | processing_time_sec | text | processing_time_microsec | text | indexes: "pk_websvcs_logs_201103_id" primary key, btree (id) check constraints: "request_dt" check (request_dt >= '2011-03-01'::date , request_dt < '2011-04-01'::date) inherits: rpt.websvcs_logs spatial_data=# set constraint_exclusion = on; set spatial_data=# explain select count(*) rpt.websvcs_logs request_dt = date '2011-03-05'; query plan ------------------------------------------------------------------------------------------------------ aggregate (cost=85738875.50..85738875.52 rows=1 width=0) -> append (cost=0.00..85738236.41 rows=255636 width=0) -> seq scan on websvcs_logs (cost=0.00..11.00 rows=1 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201002 websvcs_logs (cost=0.00..564425.36 rows=1387 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201003 websvcs_logs (cost=0.00..1546537.50 rows=4287 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201004 websvcs_logs (cost=0.00..2528697.60 rows=9248 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201005 websvcs_logs (cost=0.00..3164403.20 rows=12885 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201006 websvcs_logs (cost=0.00..4476196.10 rows=12035 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201007 websvcs_logs (cost=0.00..4470579.60 rows=9543 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201008 websvcs_logs (cost=0.00..4881312.70 rows=11071 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201009 websvcs_logs (cost=0.00..4433474.70 rows=11005 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201010 websvcs_logs (cost=0.00..5419184.20 rows=13605 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201011 websvcs_logs (cost=0.00..5562311.50 rows=15424 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201012 websvcs_logs (cost=0.00..5543114.80 rows=14961 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201101 websvcs_logs (cost=0.00..7320972.20 rows=23008 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201102 websvcs_logs (cost=0.00..7413710.90 rows=23898 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201103 websvcs_logs (cost=0.00..8754694.20 rows=27241 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201104 websvcs_logs (cost=0.00..9292596.80 rows=30848 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201105 websvcs_logs (cost=0.00..9148734.80 rows=30727 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201106 websvcs_logs (cost=0.00..1217213.25 rows=4456 width=0) filter: (request_dt = '2011-03-05'::date) spatial_data=# set constraint_exclusion = off; set spatial_data=# explain select count(*) rpt.websvcs_logs request_dt = date '2011-03-05'; query plan ------------------------------------------------------------------------------------------------------ aggregate (cost=85738875.50..85738875.52 rows=1 width=0) -> append (cost=0.00..85738236.41 rows=255636 width=0) -> seq scan on websvcs_logs (cost=0.00..11.00 rows=1 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201002 websvcs_logs (cost=0.00..564425.36 rows=1387 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201003 websvcs_logs (cost=0.00..1546537.50 rows=4287 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201004 websvcs_logs (cost=0.00..2528697.60 rows=9248 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201005 websvcs_logs (cost=0.00..3164403.20 rows=12885 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201006 websvcs_logs (cost=0.00..4476196.10 rows=12035 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201007 websvcs_logs (cost=0.00..4470579.60 rows=9543 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201008 websvcs_logs (cost=0.00..4881312.70 rows=11071 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201009 websvcs_logs (cost=0.00..4433474.70 rows=11005 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201010 websvcs_logs (cost=0.00..5419184.20 rows=13605 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201011 websvcs_logs (cost=0.00..5562311.50 rows=15424 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201012 websvcs_logs (cost=0.00..5543114.80 rows=14961 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201101 websvcs_logs (cost=0.00..7320972.20 rows=23008 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201102 websvcs_logs (cost=0.00..7413710.90 rows=23898 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201103 websvcs_logs (cost=0.00..8754694.20 rows=27241 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201104 websvcs_logs (cost=0.00..9292596.80 rows=30848 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201105 websvcs_logs (cost=0.00..9148734.80 rows=30727 width=0) filter: (request_dt = '2011-03-05'::date) -> seq scan on websvcs_logs_201106 websvcs_logs (cost=0.00..1217213.25 rows=4456 width=0) filter: (request_dt = '2011-03-05'::date)
you must change type of request_dt column date, or change check constraints work timestamp. in documentation, can see example date type column.
Comments
Post a Comment