Changeset: fb7ffb6bcd79 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=fb7ffb6bcd79
Modified Files:
sql/server/rel_optimizer.c
sql/test/merge-partitions/Tests/mergepart31.sql
sql/test/merge-partitions/Tests/mergepart31.stable.out
Branch: Oct2020
Log Message:
Updated child elimination optimization for range partitions with identical min
and max values
diffs (215 lines):
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -9121,19 +9121,26 @@ rel_merge_table_rewrite(visitor *v, sql_
skip |= nskip;
}
} else { /* limit1 to limit2 (general case),
limit2 is exclusive */
+
bool max_differ_min =
ATOMcmp(col->type.type->localtype, &rmin->data.val, &rmax->data.val) != 0;
+
if (lval) {
if (next->flag == cmp_equal) {
-
skip |= next->anti ?
exp_range_overlap(rmin, rmax, lval, hval, false, true) != 0 :
-
exp_range_overlap(rmin, rmax, lval, hval, false, true) == 0;
+
skip |= next->anti ?
exp_range_overlap(rmin, rmax, lval, hval, false, max_differ_min) != 0 :
+
exp_range_overlap(rmin, rmax, lval, hval, false,
max_differ_min) == 0;
} else if (hval != lval) { /*
For the between case */
comp_type higher =
range2rcompare(next->flag);
-
skip |= next->anti ?
exp_range_overlap(rmin, rmax, lval, hval, higher == cmp_lt, true) != 0 :
-
exp_range_overlap(rmin, rmax, lval, hval, higher == cmp_lt,
true) == 0;
+
skip |= next->anti ?
exp_range_overlap(rmin, rmax, lval, hval, higher == cmp_lt, max_differ_min) !=
0 :
+
exp_range_overlap(rmin, rmax, lval, hval, higher == cmp_lt,
max_differ_min) == 0;
} else {
switch (next->flag) {
case cmp_gt:
+
skip |=
next->anti ? VALcmp(&(lval->data), &(rmax->data)) < 0 : VALcmp(&(lval->data),
&(rmax->data)) >= 0;
+
break;
case cmp_gte:
-
skip |=
next->anti ? VALcmp(&(lval->data), &(rmax->data)) < 0 : VALcmp(&(lval->data),
&(rmax->data)) >= 0;
+
if
(max_differ_min)
+
skip |= next->anti ? VALcmp(&(lval->data), &(rmax->data)) < 0 :
VALcmp(&(lval->data), &(rmax->data)) >= 0;
+
else
+
skip |= next->anti ? VALcmp(&(lval->data), &(rmax->data)) <= 0 :
VALcmp(&(lval->data), &(rmax->data)) > 0;
break;
case cmp_lt:
skip |=
next->anti ? VALcmp(&(rmin->data), &(lval->data)) < 0 : VALcmp(&(rmin->data),
&(lval->data)) >= 0;
@@ -9152,7 +9159,7 @@ rel_merge_table_rewrite(visitor *v, sql_
if (a->isnull)
continue;
-
nskip &=
exp_range_overlap(rmin, rmax, a, a, false, true) == 0;
+
nskip &=
exp_range_overlap(rmin, rmax, a, a, false, max_differ_min) == 0;
}
skip |= nskip;
}
diff --git a/sql/test/merge-partitions/Tests/mergepart31.sql
b/sql/test/merge-partitions/Tests/mergepart31.sql
--- a/sql/test/merge-partitions/Tests/mergepart31.sql
+++ b/sql/test/merge-partitions/Tests/mergepart31.sql
@@ -123,4 +123,31 @@ plan select 1 from splitted2 where stamp
select 1 from splitted2 where stamp in (8,9); --all children pruned
-- empty
+CREATE MERGE TABLE table1 (a int, b int) PARTITION BY RANGE ON (a);
+CREATE TABLE another1 (a int, b int);
+CREATE TABLE another2 (a int, b int);
+
+ALTER TABLE table1 ADD TABLE another1 AS PARTITION FROM 10 TO 10;
+ALTER TABLE table1 ADD TABLE another2 AS PARTITION FROM 11 TO 11;
+
+plan select 1 from table1 where a = 10; --only first child passes
+
+plan select 1 from table1 where a = 11; --only second child passes
+
+plan select 1 from table1 where a = 10 or a = 11; --nothing gets pruned
+
+plan select 1 from table1 where a >= 10; --nothing gets pruned
+
+plan select 1 from table1 where a > 10; --only second child passes
+
+plan select 1 from table1 where a <= 10; --only first child passes
+
+plan select 1 from table1 where a between 10 and 10; --only first child passes
+
+plan select 1 from table1 where a = 10 or b = 11; --nothing gets pruned
+
+plan select 1 from table1 where a not between 10 and 11; --all children pruned
+
+plan select 1 from table1 where a not between 10 and 10; --only second child
passes
+
ROLLBACK;
diff --git a/sql/test/merge-partitions/Tests/mergepart31.stable.out
b/sql/test/merge-partitions/Tests/mergepart31.stable.out
--- a/sql/test/merge-partitions/Tests/mergepart31.stable.out
+++ b/sql/test/merge-partitions/Tests/mergepart31.stable.out
@@ -598,6 +598,132 @@ project (
% %5 # name
% tinyint # type
% 1 # length
+#CREATE MERGE TABLE table1 (a int) PARTITION BY RANGE ON (a);
+#CREATE TABLE another1 (a int);
+#CREATE TABLE another2 (a int);
+#ALTER TABLE table1 ADD TABLE another1 AS PARTITION FROM 10 TO 10;
+#ALTER TABLE table1 ADD TABLE another2 AS PARTITION FROM 11 TO 11;
+#plan select 1 from table1 where a = 10; --only first child passes
+% .plan # table_name
+% rel # name
+% clob # type
+% 47 # length
+project (
+| select (
+| | table(sys.another1) [ "table1"."a" ] COUNT
+| ) [ "table1"."a" = int "10" ]
+) [ tinyint "1" ]
+#plan select 1 from table1 where a = 11; --only second child passes
+% .plan # table_name
+% rel # name
+% clob # type
+% 47 # length
+project (
+| select (
+| | table(sys.another2) [ "table1"."a" ] COUNT
+| ) [ "table1"."a" = int "11" ]
+) [ tinyint "1" ]
+#plan select 1 from table1 where a = 10 or a = 11; --nothing gets pruned
+% .plan # table_name
+% rel # name
+% clob # type
+% 67 # length
+union (
+| project (
+| | select (
+| | | table(sys.another1) [ "another1"."a" as "table1"."a" ] COUNT
+| | ) [ "table1"."a" in (int "10", int "11") ]
+| ) [ tinyint "1" ],
+| project (
+| | select (
+| | | table(sys.another2) [ "another2"."a" as "table1"."a" ] COUNT
+| | ) [ "table1"."a" in (int "10", int "11") ]
+| ) [ tinyint "1" ]
+) [ "%3"."%3" NOT NULL ]
+#plan select 1 from table1 where a >= 10; --nothing gets pruned
+% .plan # table_name
+% rel # name
+% clob # type
+% 67 # length
+union (
+| project (
+| | select (
+| | | table(sys.another1) [ "another1"."a" as "table1"."a" ] COUNT
+| | ) [ "table1"."a" >= int "10" ]
+| ) [ tinyint "1" ],
+| project (
+| | select (
+| | | table(sys.another2) [ "another2"."a" as "table1"."a" ] COUNT
+| | ) [ "table1"."a" >= int "10" ]
+| ) [ tinyint "1" ]
+) [ "%3"."%3" NOT NULL ]
+#plan select 1 from table1 where a > 10; --only second child passes
+% .plan # table_name
+% rel # name
+% clob # type
+% 47 # length
+project (
+| select (
+| | table(sys.another2) [ "table1"."a" ] COUNT
+| ) [ "table1"."a" > int "10" ]
+) [ tinyint "1" ]
+#plan select 1 from table1 where a <= 10; --only first child passes
+% .plan # table_name
+% rel # name
+% clob # type
+% 47 # length
+project (
+| select (
+| | table(sys.another1) [ "table1"."a" ] COUNT
+| ) [ "table1"."a" <= int "10" ]
+) [ tinyint "1" ]
+#plan select 1 from table1 where a between 10 and 10; --only first child passes
+% .plan # table_name
+% rel # name
+% clob # type
+% 53 # length
+project (
+| select (
+| | table(sys.another1) [ "table1"."a" ] COUNT
+| ) [ int "10" <= "table1"."a" <= int "10" BETWEEN ]
+) [ tinyint "1" ]
+#plan select 1 from table1 where a = 10 or b = 11; --nothing gets pruned
+% .plan # table_name
+% rel # name
+% clob # type
+% 99 # length
+union (
+| project (
+| | select (
+| | | table(sys.another1) [ "another1"."a" as "table1"."a", "another1"."b" as
"table1"."b" ] COUNT
+| | ) [ ("table1"."a" = int "10") or ("table1"."b" = int "11") ]
+| ) [ tinyint "1" ],
+| project (
+| | select (
+| | | table(sys.another2) [ "another2"."a" as "table1"."a", "another2"."b" as
"table1"."b" ] COUNT
+| | ) [ ("table1"."a" = int "10") or ("table1"."b" = int "11") ]
+| ) [ tinyint "1" ]
+) [ "%3"."%3" NOT NULL ]
+#plan select 1 from table1 where a not between 10 and 11; --all children pruned
+% .plan # table_name
+% rel # name
+% clob # type
+% 23 # length
+project (
+| select (
+| | [ boolean "true" ]
+| ) [ boolean "false" ]
+) [ tinyint "1" ]
+#plan select 1 from table1 where a not between 10 and 10; --only second child
passes
+% .plan # table_name
+% rel # name
+% clob # type
+% 57 # length
+project (
+| select (
+| | table(sys.another2) [ "table1"."a" ] COUNT
+| ) [ int "10" ! <= "table1"."a" ! <= int "10" BETWEEN ]
+) [ tinyint "1" ]
#ROLLBACK;
# 11:53:07 >
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
