On Sep 7, 2013, at 7:20 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Alban Hertroys <haram...@gmail.com> writes:
>> On Sep 7, 2013, at 6:54, Steve Atkins <st...@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 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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to