Re: Problem with default partition pruning

Horiguchi-san,

On 2019/04/09 18:49, Kyotaro HORIGUCHI wrote:
> Hi, Amit. Thank you for the explanation.
> 
> At Tue, 9 Apr 2019 18:09:20 +0900, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote in
<4c1074cc-bf60-1610-c728-9a5b12f5b234@lab.ntt.co.jp>
>>> The partition constraint is equivalent to "(a IS NOT NULL) AND (a
>>>> = 2) AND (a < 4)". Is it intentional (for, for example,
>>> performance reasons)? Or is it reasonable to deduplicate the
>>> quals?
>>
>> Yeah, we don't try to simplify that due to lack of infrastructure, maybe.
>> If said infrastructure was present, maybe CHECK constraints would already
>> be using that, which doesn't seem to be the case.
> 
> Doesn't predicate_implied_by do that?
>
> With the attached small patch, the partqual in my example becomes.

Ah, I was wrong in saying we lack the infrastructure.

> Partition constraint: ((a IS NOT NULL) AND (a >= 2) AND (a < 4))
> 
> And for in a more complex case:
> 
> create table p2 (a int, b int) partition by range (a, b);
> create table c21 partition of p2 for values from (0, 0) to (1, 50) partition by range (a, b);
> create table c22 partition of p2 for values from (1, 50) to (2, 100) partition by range (a, b);
> create table c211 partition of c21 for values from (0, 0) to (0, 1000);
> create table c212 partition of c21 for values from (0, 1000) to (0, 2000);
> 
> \d+ c212
> ..
> Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND ((a > 0) OR ((a =
>  0) AND (b >= 0))) AND ((a < 1) OR ((a = 1) AND (b < 50))) AND (a IS NOT NULL) A
> ND (b IS NOT NULL) AND (a = 0) AND (b >= 1000) AND (b < 2000))
> 
> is reduced to:
> 
> Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND (a = 0) AND (b >=
>  1000) AND (b < 2000))
> 
> Of course this cannot be reducible:
> 
> create table p3 (a int, b int) partition by range (a);
> create table c31 partition of p3 for values from (0) to (1) partition by range(b);
> create table c311 partition of c31 for values from (0) to (1);
> \d+ c311
> 
> Partition constraint: ((a IS NOT NULL) AND (a >= 0) AND (a < 1) AND (b IS NOT NU
> LL) AND (b >= 0) AND (b < 1))
> 
> I think this is useful even counting possible degradation, and I
> believe generate_partition_qual is not called so often.

I think more commonly used forms of sub-partitioning will use different
columns at different levels as in the 2nd example.  So, although we don't
call generate_partition_qual() as much as we used to before, even at the
times we do, we'd encounter this type of sub-partitioning more often and
the proposed optimization step will end up being futile in more cases than
the cases in which it would help.  Maybe, that was the reason not to try
too hard in the first place, not the lack of infrastructure as I was saying.

Thanks,
Amit