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@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.

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
diff --git a/src/backend/utils/cache/partcache.c b/src/backend/utils/cache/partcache.c
index 8f43d682cf..c2f6d472c2 100644
--- a/src/backend/utils/cache/partcache.c
+++ b/src/backend/utils/cache/partcache.c
@@ -357,7 +357,14 @@ generate_partition_qual(Relation rel)
 
     /* Add the parent's quals to the list (if any) */
     if (parent->rd_rel->relispartition)
-        result = list_concat(generate_partition_qual(parent), my_qual);
+    {
+        List *pqual = generate_partition_qual(parent);
+
+        if (predicate_implied_by(pqual, my_qual, false))
+            result = my_qual;
+        else
+            result = list_concat(pqual, my_qual);
+    }
     else
         result = my_qual;