"Michael Artz" <mlartz@xxxxxxxxx> writes: > I'm trying to partition my table on the first octet of an ip4 column > and can't seem to get the planner to do the constraint_exclusion. The > following SQL (copied by hand): > CREATE TABLE a (ip ip4); > CREATE TABLE a_1 ( CHECK (ip <<= '1.0.0.0/8' ) INHERITS(a); > CREATE TABLE a_2 ( CHECK (ip <<= '2.0.0.0/8' ) INHERITS(a); > CREATE TABLE a_3 ( CHECK (ip <<= '3.0.0.0/8' ) INHERITS(a); > CREATE TABLE a_4 ( CHECK (ip <<= '4.0.0.0/8' ) INHERITS(a); > SET constraint_exclusion = on; > EXPLAIN SELECT * FROM a WHERE ip <<= '1.0.0.0/8' This isn't gonna work because the planner is not able to deduce that ip <<= '1.0.0.0/8' implies NOT (ip <<= '2.0.0.0/8'), etc. The cases in which the planner can make nontrivial deductions of that sort are connected to operators that fall into btree operator classes, which <<= doesn't. (Yes, I know there's a kluge that lets a search using <<= use a btree index. There are a number of reasons why it's a kluge, one being that it's disconnected from constraint_exclusion reasoning...) If you can convert your partition constraints and queries into simple "<" and ">" conditions then it'd work. regards, tom lane
