Hi, Amit. Thank you for the explanation.
At Tue, 9 Apr 2019 18:09:20 +0900, Amit Langote <[email protected]>
wrote in <[email protected]>
> > 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;
