Re: Partitioning on ip4 datatype using <<=

"Michael Artz" <mlartz(at)gmail(dot)com> 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