Thread: Constraint exclusion and overlapping range checks
If I have a partitioned table that has some range constraints that look kinda like they're intended for constraint exclusion,but aren't quite non-overlapping, will that break anything? e.g. create table jan ( …, check(created >= '2013-01-01' and created < '2013-02-01'), check(id >=0 and id < 10000100) ) inherits(foo); create table feb ( …, check(created >= '2013-02-01' and created < '2013-03-01'), check(id >=1000000 and id < 20000100)) inherits(foo); create table mar ( …, check(created >= '2013-03-01' and created < '2013-04-01'), check(id >=2000000 and id < 30000100)) inherits(foo); Querying by created should be fine, and take advantage of constraint exclusion, but will querying by id work? And if it doeswork, will it take any advantage of those constraints at all, or just search all the child partitions? Cheers, Steve
Le 2013-09-07 à 00:29, Steve Atkins a écrit : > If I have a partitioned table that has some range constraints that look kinda like they're intended for constraint exclusion,but aren't quite non-overlapping, will that break anything? > > e.g. > > create table jan ( …, check(created >= '2013-01-01' and created < '2013-02-01'), check(id >=0 and id < 10000100) ) inherits(foo); > create table feb ( …, check(created >= '2013-02-01' and created < '2013-03-01'), check(id >=1000000 and id < 20000100)) inherits(foo); > create table mar ( …, check(created >= '2013-03-01' and created < '2013-04-01'), check(id >=2000000 and id < 30000100)) inherits(foo); > > Querying by created should be fine, and take advantage of constraint exclusion, but will querying by id work? And if itdoes work, will it take any advantage of those constraints at all, or just search all the child partitions? I don't know, but I suspect a quick EXPLAIN ANALYZE will tell you, even with empty tables. Bye, François
Attachment
On Sep 6, 2013, at 9:37 PM, François Beausoleil <francois@teksol.info> wrote: > > Le 2013-09-07 à 00:29, Steve Atkins a écrit : > >> If I have a partitioned table that has some range constraints that look kinda like they're intended for constraint exclusion,but aren't quite non-overlapping, will that break anything? >> >> e.g. >> >> create table jan ( …, check(created >= '2013-01-01' and created < '2013-02-01'), check(id >=0 and id < 10000100) ) inherits(foo); >> create table feb ( …, check(created >= '2013-02-01' and created < '2013-03-01'), check(id >=1000000 and id < 20000100)) inherits(foo); >> create table mar ( …, check(created >= '2013-03-01' and created < '2013-04-01'), check(id >=2000000 and id < 30000100)) inherits(foo); >> >> Querying by created should be fine, and take advantage of constraint exclusion, but will querying by id work? And if itdoes work, will it take any advantage of those constraints at all, or just search all the child partitions? > > I don't know, but I suspect a quick EXPLAIN ANALYZE will tell you, even with empty tables. Explain suggests it'll work fine, and make good use of the constraints to prune partitions from the plan. But the docs arepretty specific about overlapping range constraints being a bad thing so I'm wondering if there's potential for problems. Cheers, Steve
On Sep 7, 2013, at 6:54, Steve Atkins <steve@blighty.com> wrote: > On Sep 6, 2013, at 9:37 PM, François Beausoleil <francois@teksol.info> wrote: > >> Le 2013-09-07 à 00:29, Steve Atkins a écrit : >> >>> If I have a partitioned table that has some range constraints that look kinda like they're intended for constraint exclusion,but aren't quite non-overlapping, will that break anything? >>> >>> e.g. >>> >>> create table jan ( …, check(created >= '2013-01-01' and created < '2013-02-01'), check(id >=0 and id < 10000100) ) inherits(foo); >>> create table feb ( …, check(created >= '2013-02-01' and created < '2013-03-01'), check(id >=1000000 and id < 20000100)) inherits(foo); >>> create table mar ( …, check(created >= '2013-03-01' and created < '2013-04-01'), check(id >=2000000 and id < 30000100)) inherits(foo); >>> >>> Querying by created should be fine, and take advantage of constraint exclusion, but will querying by id work? And ifit does work, will it take any advantage of those constraints at all, or just search all the child partitions? >> >> I don't know, but I suspect a quick EXPLAIN ANALYZE will tell you, even with empty tables. > > Explain suggests it'll work fine, and make good use of the constraints to prune partitions from the plan. But the docsare pretty specific about overlapping range constraints being a bad thing so I'm wondering if there's potential for problems. For values that are in the overlapping parts of the partition, the database will have to look in both table partitions tofind a record that you're searching for instead of a single table partition. That partially defeats the purpose of usingexclusion constraints. Next to that, putting data in the tables becomes ambiguous for records that match both constraints - in which table shouldthe records go? That is something that you need to do programatically anyway, so with the knowledge of how to decidewhich records go where, you could also define your exclusion constraints to not be ambigous. I don't see any benefit of having ambiguous exclusion constraints - IMHO you're better off fixing them. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
Alban Hertroys <haramrae@gmail.com> writes:
> On Sep 7, 2013, at 6:54, Steve Atkins <steve@blighty.com> wrote:
>>> If I have a partitioned table that has some range constraints that look kinda like they're intended for constraint
exclusion,but aren't quite non-overlapping, will that break anything?
> Next to that, putting data in the tables becomes ambiguous for records that match both constraints - in which table
shouldthe records go? That is something that you need to do programatically anyway, so with the knowledge of how to
decidewhich records go where, you could also define your exclusion constraints to not be ambigous.
> I don't see any benefit of having ambiguous exclusion constraints - IMHO you're better off fixing them.
I agree with that advice in principle; but if the true partitioning
constraint is too complicated, you might defeat the planner's ability to
prove that particular tables don't need to be scanned as a consequence of
a particular WHERE clause. The simple range constraints Steve showed
should work fine with constraint exclusion. The proofs are done
separately for each sub-table, so the fact that the ranges overlap doesn't
bother the planner. We might in future have a more efficient partitioning
method that does assume non-overlapping ranges ... but it's not there
today.
regards, tom lane
On Sep 7, 2013, at 7:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Alban Hertroys <haramrae@gmail.com> writes: >> On Sep 7, 2013, at 6:54, Steve Atkins <steve@blighty.com> wrote: >>>> If I have a partitioned table that has some range constraints that look kinda like they're intended for constraint exclusion,but aren't quite non-overlapping, will that break anything? > >> Next to that, putting data in the tables becomes ambiguous for records that match both constraints - in which table shouldthe records go? That is something that you need to do programatically anyway, so with the knowledge of how to decidewhich records go where, you could also define your exclusion constraints to not be ambigous. > >> I don't see any benefit of having ambiguous exclusion constraints - IMHO you're better off fixing them. If the check constraints are there for reasons other than partitioning and exclusion there isn't anything to fix. But ifthe constraint exclusion code can use those overlapping range constraints to reduce queries down to one or two partitionsthat seems like a fairly useful benefit. > > I agree with that advice in principle; but if the true partitioning > constraint is too complicated, you might defeat the planner's ability to > prove that particular tables don't need to be scanned as a consequence of > a particular WHERE clause. The simple range constraints Steve showed > should work fine with constraint exclusion. The proofs are done > separately for each sub-table, so the fact that the ranges overlap doesn't > bother the planner. We might in future have a more efficient partitioning > method that does assume non-overlapping ranges ... but it's not there > today. Constraint exclusion is a global setting, so I'm mostly concerned about the planner mistaking range constraints that arethere for other reasons breaking queries. That it doesn't - and can even usefully use those range constraints to optimizequeries - isn't really surprising, but is reassuring. Cheers, Steve