FAQ
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?

Search Discussions

  • Thom Brown at Mar 30, 2010 at 10:16 am

    On 30 March 2010 10:35, Jasen Betts 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMar 30, '10 at 9:35a
activeMar 30, '10 at 10:16a
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Jasen Betts: 1 post Thom Brown: 1 post

People

Translate

site design / logo © 2022 Grokbase