Re: Constraint exclusion and overlapping range checks

Alban Hertroys <haramrae(at)gmail(dot)com> writes:
> On Sep 7, 2013, at 6:54, Steve Atkins <steve(at)blighty(dot)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 should the records go? That is something that you need to do programatically anyway, so with the knowledge of how to decide which 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