Re: Problem with default partition pruning

Horiguchi-san,

On 2019/04/09 17:51, Kyotaro HORIGUCHI wrote:
> At Tue, 09 Apr 2019 17:37:25 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote in <20190409(dot)173725(dot)31175835(dot)horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
>>> I'm attaching the latest version. Could you please check it again?
>
> By the way, I noticed that partition constraint in a multi-level
> partition contains duplicate clauses.
>
> create table p (a int) partition by range (a);
> create table c1 partition of p for values from (0) to (10) partition by range (a);
> create table c11 partition of c1 for values from (0) to (2) partition by range (a);
> create table c12 partition of c1 for values from (2) to (4) partition by range (a);
>
> =# \d+ c12
> | Partitioned table "public.c12"
> | Column | Type | Collation | Nullable | Default | Storage | Stats target | De
> | scription
> | --------+---------+-----------+----------+---------+---------+--------------+---
> | ----------
> | a | integer | | | | plain | |
> | Partition of: c1 FOR VALUES FROM (2) TO (4)
> | Partition constraint: ((a IS NOT NULL) AND (a >= 0) AND (a < 10) AND (a IS NOT N
> | ULL) AND (a >= 2) AND (a < 4))
> | Partition key: RANGE (a)
> | Number of partitions: 0
>
>
> 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.

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.

Thanks,
Amit