Postgres Pro
Downloads
Home   >   mailing lists

Thread: constraint checking on partitions

constraint checking on partitions

From
"Chris Spotts"
Date:

I have several partitions on a history table that are partitioned by a date range (monthly).  However, it’s possible for an unexpected but valid date (either very far in the future or very far in the past) to come in the data set and so there is an “overflow” table.

Say table A is parent, B  is April data, C is June data, D is July data, and O is overflow data.

I set several stored procedures to facilitate the adding of triggers, constraints, etc for partitions.  These procs, in addition to adding the constraint the normal partitions, also add a “NOT” constraint to the overflow table.  i.e., when the July partition is created with

 

alter table D add constraint onlyjuly check (date1 >= ‘2009-07-01’ and date1 < ‘2009-07-01’)

Then this is also run

alter table O add constraint notjuly check (NOT(date1 >= ‘2009-07-01’ and date1 < ‘2009-07-01’))

 

The planner excludes correctly except that it always checks O.

It doesn’t seem to be able to use the multiple constraints on O.

Are multiple “NOT” constraints too much for the planner for excluding partitions?

 

 

 

 

 

postgres=# select version();

                                                       version

----------------------------------------------------------------------------------------------------------------------

 PostgreSQL 8.4.0 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-8.0.1), 64-bit

 

 

 

Chris Spotts

 

Re: constraint checking on partitions

From
Tom Lane
Date:
"Chris Spotts" <rfusca@gmail.com> writes:
> Then this is also run

> alter table O add constraint notjuly check (NOT(date1 >= '2009-07-01' and
> date1 < '2009-07-01'))

> The planner excludes correctly except that it always checks O.

What are the actual queries you're hoping it will exclude for?

            regards, tom lane

Re: constraint checking on partitions

From
"Chris Spotts"
Date:
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, July 09, 2009 1:52 PM
> To: Chris Spotts
> Cc: 'postgres list'
> Subject: Re: [GENERAL] constraint checking on partitions
>
> "Chris Spotts" <rfusca@gmail.com> writes:
> > Then this is also run
>
> > alter table O add constraint notjuly check (NOT(date1 >= '2009-07-01'
> and
> > date1 < '2009-07-01'))
>
> > The planner excludes correctly except that it always checks O.
>
> What are the actual queries you're hoping it will exclude for?
>
>             regards, tom lane
[Spotts, Christopher]

I mistyped, that should be

alter table D add constraint onlyjuly check (date1 >= '2009-07-01' and date1
< '2009-08-01')
Then this is also run
alter table O add constraint notjuly check (NOT(date1 >= '2009-07-01' and
date1 < '2009-08-01'))

If I ran a select * from A where date1 >= '2009-07-02' and date1 <
'2009-07-15' then I would think it wouldn't check O.

Re: constraint checking on partitions

From
Tom Lane
Date:
"Chris Spotts" <rfusca@gmail.com> writes:
> I mistyped, that should be

> alter table D add constraint onlyjuly check (date1 >= '2009-07-01' and date1
> < '2009-08-01')
> Then this is also run
> alter table O add constraint notjuly check (NOT(date1 >= '2009-07-01' and
> date1 < '2009-08-01'))

> If I ran a select * from A where date1 >= '2009-07-02' and date1 <
> '2009-07-15' then I would think it wouldn't check O.

Works for me ...

regression=# create table a (date1 date);
CREATE TABLE
regression=# create table july() inherits(a);
CREATE TABLE
regression=# create table other() inherits(a);
CREATE TABLE
regression=# alter table other add constraint notjuly check (NOT(date1 >= '2009-07-01' and date1 < '2009-08-01'));
ALTER TABLE
regression=# explain select * from a where date1 >= '2009-07-02' and date1 < '2009-07-15';
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Result  (cost=0.00..92.00 rows=24 width=4)
   ->  Append  (cost=0.00..92.00 rows=24 width=4)
         ->  Seq Scan on a  (cost=0.00..46.00 rows=12 width=4)
               Filter: ((date1 >= '2009-07-02'::date) AND (date1 < '2009-07-15'::date))
         ->  Seq Scan on july a  (cost=0.00..46.00 rows=12 width=4)
               Filter: ((date1 >= '2009-07-02'::date) AND (date1 < '2009-07-15'::date))
(6 rows)

regression=#

            regards, tom lane

Re: constraint checking on partitions

From
"Chris Spotts"
Date:
>
> > If I ran a select * from A where date1 >= '2009-07-02' and date1 <
> > '2009-07-15' then I would think it wouldn't check O.
>
[Spotts, Christopher]
I oversimplified this too much - but I figured out what was happening.
If you added the June table as well and added a separate NOT constraint for
June, and then wrote the query
"SELECT * from A where date1 >= '2009-06-01' and date1 < '2009-07-05'" the
planner can't match them to individual constraints, so it doesn't exclude.
Theoretically the planner could logically "AND" them together to get better
exclusion, but it must not be.