Re: Constraint exclusion and overlapping range checks

On Sep 7, 2013, at 7:20 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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.

If the check constraints are there for reasons other than partitioning and exclusion there isn't anything to fix. But if the constraint exclusion code can use those overlapping range constraints to reduce queries down to one or two partitions that 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 are there for other reasons breaking queries. That it doesn't - and can even usefully use those range constraints to optimize queries - isn't really surprising, but is reassuring.

Cheers,
Steve