Re: Problem with default partition pruning

Hi, Amit. Thank you for the explanation.

At Tue, 9 Apr 2019 18:09:20 +0900, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote in <4c1074cc-bf60-1610-c728-9a5b12f5b234(at)lab(dot)ntt(dot)co(dot)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.

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.

> create table foo (a int check ((a IS NOT NULL) AND (a >= 0) AND (a < 10)
> AND (a IS NOT NULL) AND (a >= 2) AND (a < 4)));
>
> \d foo
> Table "public.foo"
> Column │ Type │ Collation │ Nullable │ Default
> ────────┼─────────┼───────────┼──────────┼─────────
> a │ integer │ │ │
> Check constraints:
> "foo_a_check" CHECK (a IS NOT NULL AND a >= 0 AND a < 10 AND a IS NOT
> NULL AND a >= 2 AND a < 4)
>
> Now it's true that users wouldn't manually write expressions like that,
> but the expressions might be an automatically generated, which is also the
> case with partition constraint of a deeply nested partition.

Differently from manually written constraint, partition
constraint is highly reducible.

Thoughts?

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center