为什么计划者没有利用我对子表的检查约束来减少计划的大小？
我有一个按月分区的表，用于保存 apache 日志信息。当我对一个简单查询运行 EXPLAIN 时，该查询在与分区 CHECK 相同的字段上有一个 WHERE 子句，无论constraint_exclusion 是打开还是关闭，我都会得到相同的计划。下面是 2010 年 2 月和 3 月的主表信息和两个子表信息示例。总体而言，从 2010 年 2 月到 2011 年 6 月每个月都有一个子表。平均每个子表大约有 1 亿条记录。下面还显示了一个简单查询的 EXPLAIN 的输出，一次打开了constraint_exclusion，一次关闭了它。不幸的是，CHECK 约束没有用于减小计划大小。这是因为 request_dt 数据类型是带有时区的 TIMESTAMP 但 CHECK 约束是日期吗？还有其他想法吗？我尚未在 request_dt 上创建索引，但根据文档，这是不必要的。我将添加它们，但我认为它们不会影响 CHECK 约束的使用。
我正在使用 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 with 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 with 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 AND 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 with 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 AND request_dt < '2011-04-01'::date)
Inherits: rpt.websvcs_logs
spatial_data=# SET constraint_exclusion = on;
SET
spatial_data=# EXPLAIN SELECT COUNT(*) FROM rpt.websvcs_logs WHERE 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(*) FROM rpt.websvcs_logs WHERE 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)
I have a table that is partitioned by month and is used for holding apache log information. When I run EXPLAIN for a simple query which has a WHERE clause on the same field as the partition CHECKs, I get the same plan whether constraint_exclusion is on or off. Below is the master table info and two examples of child tables info for Feb and Mar of 2010. Overall, there is a child table for each month from Feb of 2010 through June of 2011. On average, each child table has around 100M records. Also below is the output of EXPLAIN for a simple query, once with constraint_exclusion on and once with it off. Unfortunately, the CHECK constraints aren't being used to reduce the plan size. Is this due to the fact that request_dt datatype is TIMESTAMP WITH TIME ZONE but the CHECK constraints are DATES? Any other thoughts? I have not yet created indexes on request_dt, but according to the documentation that isn't necessary. I'll be adding them, but I wouldn't think they should impact the use of the 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 with 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 with 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 AND 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 with 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 AND request_dt < '2011-04-01'::date)
Inherits: rpt.websvcs_logs
spatial_data=# SET constraint_exclusion = on;
SET
spatial_data=# EXPLAIN SELECT COUNT(*) FROM rpt.websvcs_logs WHERE 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(*) FROM rpt.websvcs_logs WHERE 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)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您必须将 request_dt 列的类型更改为 DATE，或更改检查约束以使用时间戳。在文档中，您可以看到日期类型的示例柱子。
You must change the type of request_dt column to DATE, or change the check constraints to work with timestamp. In the documentation, you can see an example with a date type column.