Postgres Pro
Downloads
Home   >   mailing lists

Thread: Partitioning on ip4 datatype using <<=

Partitioning on ip4 datatype using <<=

From
"Michael Artz"
Date:
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'

tells me that its going to do a sequential scan across all of the
tables.

Couple of questions:
-Can you see anything immediate/stupid that I'm doing wrong?  I'm
thinking that maybe it has something to do with the constraint types
...

-Does the partitioning understand the '<<=' check?  I created a set of
trial tables that used '=' instead on the ip4 column and things behaved
as they should.

-If I have overlapping/duplicate constraints, will constraint_exclusion
scan only the tables that satisfy the constraints, or will it get
confused and bail?  I'm thinking of having a 'normal' and an 'old'
partition on the same octet, so the check constraint will be the same
for both of them.

-Will the planner use the constraint exclusion if my queries are of the
form " ip = '1.2.3.4' ", i.e. does the planner understand that '<<='
provides a superset of '='?

Thanks
-Mike

Re: Partitioning on ip4 datatype using <<=

From
"Michael Artz"
Date:
Forgot important part ... running on RHEL 4 Update 3 x86_64 using the
8.1.3 PG distributed in the RH Web Application Beta.

SELECT version()

PostgreSQL 8.1.3 on x86_64-redhat-linux-gnu, compiled by GCC
x86_64-redhat-linux-gcc (GCC) 3.4.5 20051201 (Red Hat 3.4.5-2)

-Mike

Re: Partitioning on ip4 datatype using <<=

From
Tom Lane
Date:
"Michael Artz" <mlartz@gmail.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