Changeset: 6efe8d002e59 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/6efe8d002e59
Modified Files:
sql/server/rel_optimizer.c
sql/test/miscellaneous/Tests/simple_plans.test
Branch: Jul2021
Log Message:
Attempt to pushdown complex joinable expression in the join order optimizer
diffs (90 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
@@ -1013,9 +1013,8 @@ order_joins(visitor *v, list *rels, list
}
}
if (list_length(exps)) { /* more expressions (add selects) */
- node *n;
top = rel_select(v->sql->sa, top, NULL);
- for(n=exps->h; n; n = n->next) {
+ for(node *n=exps->h; n; n = n->next) {
sql_exp *e = n->data;
/* find the involved relations */
@@ -1030,8 +1029,32 @@ order_joins(visitor *v, list *rels, list
*/
if (exp_is_join_exp(e) == 0) {
sql_rel *nr = NULL;
- if (e->flag == cmp_equal)
- nr = rel_push_join(v->sql, top->l,
e->l, e->r, NULL, e, 0);
+ if (is_theta_exp(e->flag)) {
+ nr = rel_push_join(v->sql, top->l,
e->l, e->r, e->f, e, 0);
+ } else if (e->flag == cmp_filter || e->flag ==
cmp_or) {
+ sql_exp *l = NULL, *r = NULL;
+ int skip = 0;
+
+ /* Attempt to push down a filter
expression if possible */
+ for (node *m = ((list*)e->l)->h ; m &&
!skip ; m = m->next) {
+ sql_exp *nl = m->data;
+
+ if (nl->card > CARD_ATOM) {
+ skip |= l != NULL;
+ l = nl;
+ }
+ }
+ for (node *m = ((list*)e->r)->h ; m &&
!skip ; m = m->next) {
+ sql_exp *nr = m->data;
+
+ if (nr->card > CARD_ATOM) {
+ skip |= r != NULL;
+ r = nr;
+ }
+ }
+ if (l && r && !skip)
+ nr = rel_push_join(v->sql,
top->l, l, r, NULL, e, 0);
+ }
if (!nr)
rel_join_add_exp(v->sql->sa, top->l, e);
} else
diff --git a/sql/test/miscellaneous/Tests/simple_plans.test
b/sql/test/miscellaneous/Tests/simple_plans.test
--- a/sql/test/miscellaneous/Tests/simple_plans.test
+++ b/sql/test/miscellaneous/Tests/simple_plans.test
@@ -481,6 +481,37 @@ project (
) [ "%1"."%1" NOT NULL ]
statement ok
+create table t1 (a int, b string)
+
+statement ok rowcount 4
+insert into t1 values (1,'a'), (2, 'b'), (3, 'c'), (4, 'd')
+
+statement ok
+create table t2 (a int)
+
+statement ok rowcount 4
+insert into t2 values (1),(2),(3),(4)
+
+statement ok
+create table t3 (b string)
+
+statement ok rowcount 4
+insert into t3 values ('a'),('b'),('c'),('d')
+
+query T nosort
+plan select t1.a, t1.b from t1 inner join t2 on t1.a = t2.a inner join t3 on
t1.b like t3.b
+----
+project (
+| join (
+| | join (
+| | | table("sys"."t1") [ "t1"."a", "t1"."b" ] COUNT ,
+| | | table("sys"."t2") [ "t2"."a" ] COUNT
+| | ) [ "t1"."a" = "t2"."a" ],
+| | table("sys"."t3") [ "t3"."b" ] COUNT
+| ) [ ("t1"."b") FILTER "like" ("t3"."b", clob "", boolean "false") ]
+) [ "t1"."a", "t1"."b" ]
+
+statement ok
rollback
statement ok
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
