On 2017/11/18 8:28, Robert Haas wrote:
> On Fri, Nov 17, 2017 at 12:57 AM, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>> While working on the patch for partition pruning for declarative
>> partitioned tables, I noticed that default range partition will fail to be
>> included in a plan in certain cases due to pruning by constraint exclusion.
>> you'll notice that it doesn't explicitly say that the default partition
>> allows rows where a is null or b is null or both are null. Given that,
>> constraint exclusion will end up concluding that the default partition's
>> constraint is refuted by a = 2.
>>
>> The attached will make the constraint to look like:
>
> Uh, if the constraint exclusion logic we're using is drawing false
> conclusions, we need to fix it so it doesn't, not change the
> constraint so that the wrong logic gives the right answer.
I did actually consider it, but ended up concluding that constraint
exclusion is doing all it can using the provided list partition constraint
clauses.
If all predicate_refuted_by() receives is the expression tree (AND/OR)
with individual nodes being strict clauses involving partition keys (and
nothing about the nullness of the keys), the downstream code is just
playing by the rules as explained in the header comment of
predicate_refuted_by_recurse() in concluding that query's restriction
clause a = 2 refutes it.
You may notice in the example I gave that all rows with non-null partition
keys have a non-default range partitions to choose from. So, the only
rows that exist in the default partition are those that contain null in
either or both partition keys.
Constraint that's currently generated for the default partition only
describes rows that contain non-null partition keys. The default
partition in the example contains no such rows, so constraint exclusion is
right in excluding that it is excluded by a clause like a = 2.
By the way, I made a mistake when listing the constraint that the proposed
patch will produce; it's actually:
NOT ( a IS NOT NULL AND b IS NOT NULL AND ( ((a < 1) OR ((a = 1) AND (b < 1))) OR
((a > 1) OR ((a = 1) AND (b >= 1))) ) )
Am I missing something?
Thanks,
Amit
