Skip site navigation (1) Skip section navigation (2)

What happens if the partitions overlap?

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-novice(at)postgresql(dot)org
Subject: What happens if the partitions overlap?
Date: 2010-03-30 09:35:13
Message-ID: hosgkh$ufd$ (view raw or flat)
Lists: pgsql-novice
I have a table that is indexed on two columns one is eximm message-id
(eseentially a base-62 timestamp that sorts usefully in locale
C but not in the locale the database uses so I have coerced it
into timestamptz) and the other an ordinary serial 

As both the timestamp and the serial are generated at around the same
time the both follow the same approximate order.

I have chosen to partition on the timestamp as I feel it's easier to do
that in a cron job than it is to partition on the serial column.

many of the queries I want to run are selecting single records using the 
the serial column.

can constraint exclusion still be used if the range ovelaps a little?

eg (using a int instad of a timestamp)

 create table foo (a int, b serial);
 create table foo_1(
   check( a => 10000  and a < 20000 and b>9811 and b < 20121)
   ) inherits (foo);
 create table foo_2( 
   check( a => 20000  and a < 30000 and b>18702 and b < 30171)
   ) inherits (foo);
 create table foo_3( 
   check( a => 30000  and a < 40000 and b>29212 )
   ) inherits (foo);

I am immagining that I could create the 'b' parts of the constraint an
hour or so after the cross over of a into the the new partition, when 
I can be fairly sure that there are no low values left to be inserted
into the high-a partition, or high values of b left to go into the the
low a partition. I'd do this by inspection: 

 select max(b) from foo_1 
before then I'd leave the upper end on the low partition unbounded and
use a value picked from an hour previous for the low bound on the high

Anyway. it seems to work with these overlaps:

 SET constraint_exclusion = on;

 explain select * from foo where b=20000;
                              QUERY PLAN                               

 Result  (cost=0.00..110.25 rows=33 width=8)
   ->  Append  (cost=0.00..110.25 rows=33 width=8)
        ->  Seq Scan on foo  (cost=0.00..36.75 rows=11 width=8)
               Filter: (b = 20000)
        ->  Seq Scan on foo_1 foo  (cost=0.00..36.75 rows=11 width=8)
               Filter: (b = 20000)
        ->  Seq Scan on foo_2 foo  (cost=0.00..36.75 rows=11 width=8)
               Filter: (b = 20000)

It seems to have worked here, but there is a warning against it on this page:

> 3: We must provide non-overlapping table constraints. Rather than just
> creating the partition tables as above, 


>  Ensure that the constraints guarantee that there is no overlap
> between the key values permitted in different partitions. A common
> mistake is to set up range constraints like this:
> CHECK ( outletID BETWEEN 100 AND 200 )
> CHECK ( outletID BETWEEN 200 AND 300 )
> This is wrong since it is not clear which partition the key value 200
> belongs in. 

Is overlap actually bad (leading to corruption or other serious
failure), or just a little inefficient, meaning that in rare cases two tables 
need to be checked instead of just one?


pgsql-novice by date

Next:From: dipti shahDate: 2010-03-30 09:40:08
Subject: Re: Get the list of permissions/privileges on schema
Previous:From: Tom LaneDate: 2010-03-29 23:30:56
Subject: Re: plpgsql function help

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group