Re: What happens if the partitions overlap?

On 30 March 2010 10:35, Jasen Betts <jasen(at)xnet(dot)co(dot)nz> wrote:
> 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
>
>  etc.
>
> 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
> partition.
>
>
> 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:
>
> http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION
>
>> 3: We must provide non-overlapping table constraints. Rather than just
>> creating the partition tables as above,
>
> and
>
>>  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?
>

I can't actually see anything wrong with what you've done. Your
constraints do not actually overlap as I can't see any set of values
which would allow inclusion in more than 1 of your partitions. While
your b values will overlap, they don't overlap for your combined
constraints as b is acting as a subset of a in this case, and your a
values don't overlap at all.

Regards

Thom