Partitioning on ip4 datatype using <<=

From: "Michael Artz" <mlartz(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Partitioning on ip4 datatype using <<=
Date: 2006-05-16 18:45:41
Message-ID: 1147805141.293497.120450@i40g2000cwc.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jim C. Nasby 2006-05-16 18:52:52 Re: Friendly catalog views
Previous Message Rafael Martinez 2006-05-16 18:05:48 Re: Weird ..... (a=1 or a=2) <> (a=2 or a=1)