我有一个由两列expired和type_id分隔的表。expired是一个布尔值，按范围划分。
表格示例：
mos_active_1
...
mos_active_15
mos_expired_1
...
mos_expired_15
我的约束设置如下：
ADD CONSTRAINT mos_active_1_check CHECK (expired = false AND type_id < 100 )
...
ADD CONSTRAINT mos_expired_1_check CHECK (expired = true AND type_id < 100 )
如果我现在运行一个
type_id所有的工作都像预期的那样，只需点击SELECT * from mos WHERE expired = true AND type_id = 34。Result (cost=0.00..19.77 rows=2 width=627)
-> Append (cost=0.00..19.77 rows=2 width=627)
-> Seq Scan on mos (cost=0.00..11.50 rows=1 width=627)
Filter: (expired AND (type_id = 34))
-> Index Scan using index_mos_expired_1_on_type_id_and_region_id on mos_expired_1 mos (cost=0.00..8.27 rows=1 width=627)
Index Cond: (type_id = 34)
Filter: expired
完全不工作。
mos_expired_1表示同时查询SELECT * from mos WHERE expired = false AND type_id = 34和EXPLAIN。Result (cost=0.00..2464.71 rows=5863 width=150)
-> Append (cost=0.00..2464.71 rows=5863 width=150)
-> Seq Scan on mos (cost=0.00..11.50 rows=1 width=627)
Filter: ((NOT expired) AND (type_id = 34))
-> Index Scan using index_mos_expired_1_on_type_id_and_region_id on mos_expired_1 mos (cost=0.00..8.27 rows=1 width=627)
Index Cond: (type_id = 34)
Filter: (NOT expired)
-> Bitmap Heap Scan on mos_active_1 mos (cost=113.68..2444.95 rows=5861 width=150)
Recheck Cond: (type_id = 34)
Filter: (NOT expired)
-> Bitmap Index Scan on index_mos_active_1_on_type_id (cost=0.00..112.22 rows=5861 width=0)
Index Cond: (type_id = 34)
complete SQL（除了实际的mos表创建之外）
我真的很想知道我是否遗漏了什么，或者这是一个查询计划问题。
更新：我用一个简单得多的例子重现了同样的问题，只有两个表有一个基于expired的约束：
CREATE TABLE mos (type_id INTEGER UNIQUE, expired boolean);
CREATE TABLE mos_expired_1 ( CHECK ( expired = true ) ) INHERITS (mos);
CREATE TABLE mos_active_1 ( CHECK ( expired = false ) ) INHERITS (mos);
INSERT INTO mos_expired_1 (type_id,expired) VALUES(1, true);
INSERT INTO mos_active_1 (type_id,expired) VALUES(2, false);
EXPLAIN SELECT * from mos where expired = false;
EXPLAIN SELECT * from mos where expired = true;
最佳答案
看起来在PostgreSQL中，布尔值的约束排除不起作用。当您使用integer或enum时，它工作良好。
整数：
CREATE TABLE mos (type_id INTEGER UNIQUE, expired int);
CREATE TABLE mos_expired_1 ( CHECK ( expired = 0 ) ) INHERITS (mos);
CREATE TABLE mos_active_1 ( CHECK ( expired = 1 ) ) INHERITS (mos);
INSERT INTO mos_expired_1 (type_id,expired) VALUES(1, 0);
INSERT INTO mos_active_1 (type_id,expired) VALUES(2, 1);
analyze;
EXPLAIN SELECT * from mos where expired = 0;
QUERY PLAN
-----------------------------------------------------------------------------
Result (cost=0.00..37.76 rows=12 width=8)
-> Append (cost=0.00..37.76 rows=12 width=8)
-> Seq Scan on mos (cost=0.00..36.75 rows=11 width=8)
Filter: (expired = 0)
-> Seq Scan on mos_expired_1 mos (cost=0.00..1.01 rows=1 width=8)
Filter: (expired = 0)
(6 rows)
EXPLAIN SELECT * from mos where expired = 1;
QUERY PLAN
----------------------------------------------------------------------------
Result (cost=0.00..37.76 rows=12 width=8)
-> Append (cost=0.00..37.76 rows=12 width=8)
-> Seq Scan on mos (cost=0.00..36.75 rows=11 width=8)
Filter: (expired = 1)
-> Seq Scan on mos_active_1 mos (cost=0.00..1.01 rows=1 width=8)
Filter: (expired = 1)
(6 rows)
枚举：
CREATE TYPE mybool AS ENUM ('true', 'false');
CREATE TABLE mos (type_id INTEGER UNIQUE, expired mybool);
CREATE TABLE mos_expired_1 ( CHECK ( expired = 'true' ) ) INHERITS (mos);
CREATE TABLE mos_active_1 ( CHECK ( expired = 'false' ) ) INHERITS (mos);
INSERT INTO mos_expired_1 (type_id,expired) VALUES(1, 'true');
INSERT INTO mos_active_1 (type_id,expired) VALUES(2, 'false');
analyze;
EXPLAIN SELECT * from mos where expired = 'true';
QUERY PLAN
-----------------------------------------------------------------------------
Result (cost=0.00..37.76 rows=12 width=8)
-> Append (cost=0.00..37.76 rows=12 width=8)
-> Seq Scan on mos (cost=0.00..36.75 rows=11 width=8)
Filter: (expired = 'true'::mybool)
-> Seq Scan on mos_expired_1 mos (cost=0.00..1.01 rows=1 width=8)
Filter: (expired = 'true'::mybool)
(6 rows)
EXPLAIN SELECT * from mos where expired = 'false';
QUERY PLAN
----------------------------------------------------------------------------
Result (cost=0.00..37.76 rows=12 width=8)
-> Append (cost=0.00..37.76 rows=12 width=8)
-> Seq Scan on mos (cost=0.00..36.75 rows=11 width=8)
Filter: (expired = 'false'::mybool)
-> Seq Scan on mos_active_1 mos (cost=0.00..1.01 rows=1 width=8)
Filter: (expired = 'false'::mybool)
(6 rows)
我不知道是不是虫子。我想在任何地方都没有记录。
关于postgresql - PostgreSQL分区约束行为异常，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2331446/