Re: What happens if the partitions overlap?
От | Thom Brown |
---|---|
Тема | Re: What happens if the partitions overlap? |
Дата | |
Msg-id | bddc86151003300316qd1b4892qf754f41745c0b074@mail.gmail.com обсуждение исходный текст |
Ответ на | What happens if the partitions overlap? (Jasen Betts <jasen@xnet.co.nz>) |
Список | pgsql-novice |
On 30 March 2010 10:35, Jasen Betts <jasen@xnet.co.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
В списке pgsql-novice по дате отправления:
Предыдущее
От: dipti shahДата:
Сообщение: Re: [GENERAL] Get the list of permissions/privileges on schema