Changeset: 17fed0f3e977 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=17fed0f3e977
Modified Files:
monetdb5/optimizer/opt_mergetable.c
monetdb5/optimizer/opt_prelude.c
monetdb5/optimizer/opt_prelude.h
monetdb5/optimizer/opt_support.c
monetdb5/optimizer/opt_support.h
sql/backends/monet5/rel_bin.c
sql/backends/monet5/sql_gencode.c
sql/benchmarks/tpch/alter.sql
sql/benchmarks/tpch/load.sh
sql/include/sql_catalog.h
sql/include/sql_relation.h
sql/rel.txt
sql/server/rel_updates.c
Branch: default
Log Message:
use a 'subleftjoin' for foreignkey - join indices, solves reorder/hash problems
diffs (truncated from 352 to 300 lines):
diff --git a/monetdb5/optimizer/opt_mergetable.c
b/monetdb5/optimizer/opt_mergetable.c
--- a/monetdb5/optimizer/opt_mergetable.c
+++ b/monetdb5/optimizer/opt_mergetable.c
@@ -1491,6 +1491,17 @@ OPTmergetableImplementation(Client cntxt
actions++;
continue;
}
+ if (match > 0 && isMatLeftJoinOp(p) && p->argc >= 3 && p->retc
== 2 &&
+ match == 1 && bats == 2) {
+ m = is_a_mat(getArg(p,p->retc), mat, mtop);
+ n = -1;
+
+ if (m >= 0) {
+ mtop = mat_join2(mb, p, mat, mtop, m, n);
+ actions++;
+ continue;
+ }
+ }
/*
* Aggregate handling is a prime target for optimization.
* The simple cases are dealt with first.
diff --git a/monetdb5/optimizer/opt_prelude.c b/monetdb5/optimizer/opt_prelude.c
--- a/monetdb5/optimizer/opt_prelude.c
+++ b/monetdb5/optimizer/opt_prelude.c
@@ -107,6 +107,7 @@ str antijoinRef;
str bandjoinRef;
str thetajoinRef;
str subjoinRef;
+str subleftjoinRef;
str subantijoinRef;
str subbandjoinRef;
str subrangejoinRef;
@@ -358,6 +359,7 @@ void optimizerInit(void)
bandjoinRef = putName("bandjoin",8);
thetajoinRef = putName("thetajoin",9);
subjoinRef = putName("subjoin",7);
+ subleftjoinRef = putName("subleftjoin",11);
subantijoinRef = putName("subantijoin",11);
subbandjoinRef = putName("subbandjoin",11);
subrangejoinRef = putName("subrangejoin",12);
diff --git a/monetdb5/optimizer/opt_prelude.h b/monetdb5/optimizer/opt_prelude.h
--- a/monetdb5/optimizer/opt_prelude.h
+++ b/monetdb5/optimizer/opt_prelude.h
@@ -105,6 +105,7 @@ opt_export str antijoinRef;
opt_export str bandjoinRef;
opt_export str thetajoinRef;
opt_export str subjoinRef;
+opt_export str subleftjoinRef;
opt_export str subantijoinRef;
opt_export str subbandjoinRef;
opt_export str subrangejoinRef;
diff --git a/monetdb5/optimizer/opt_support.c b/monetdb5/optimizer/opt_support.c
--- a/monetdb5/optimizer/opt_support.c
+++ b/monetdb5/optimizer/opt_support.c
@@ -673,7 +673,9 @@ int isOrderby(InstrPtr p){
getFunctionId(p) == sortReverseRef);
}
-int isMatJoinOp(InstrPtr p){
+int
+isMatJoinOp(InstrPtr p)
+{
return (isSubJoin(p) || (getModuleId(p) == algebraRef &&
(getFunctionId(p) == crossRef ||
getFunctionId(p) == joinRef ||
@@ -685,6 +687,13 @@ int isMatJoinOp(InstrPtr p){
));
}
+int
+isMatLeftJoinOp(InstrPtr p)
+{
+ return (getModuleId(p) == algebraRef &&
+ getFunctionId(p) == subleftjoinRef);
+}
+
int isDelta(InstrPtr p){
return
(getModuleId(p)== sqlRef && (
diff --git a/monetdb5/optimizer/opt_support.h b/monetdb5/optimizer/opt_support.h
--- a/monetdb5/optimizer/opt_support.h
+++ b/monetdb5/optimizer/opt_support.h
@@ -102,6 +102,7 @@ opt_export int isFragmentGroup(InstrPtr
opt_export int isFragmentGroup2(InstrPtr q);
opt_export int isDelta(InstrPtr q);
opt_export int isMatJoinOp(InstrPtr q);
+opt_export int isMatLeftJoinOp(InstrPtr q);
opt_export int isMapOp(InstrPtr q);
opt_export int isLikeOp(InstrPtr q);
opt_export int isTopn(InstrPtr q);
diff --git a/sql/backends/monet5/rel_bin.c b/sql/backends/monet5/rel_bin.c
--- a/sql/backends/monet5/rel_bin.c
+++ b/sql/backends/monet5/rel_bin.c
@@ -1424,7 +1424,7 @@ join_hash_key( mvc *sql, list *l )
}
static stmt *
-releqjoin( mvc *sql, list *l1, list *l2, int used_hash, comp_type cmp_op )
+releqjoin( mvc *sql, list *l1, list *l2, int used_hash, comp_type cmp_op, int
need_left )
{
node *n1 = l1->h, *n2 = l2->h;
stmt *l, *r, *res;
@@ -1432,7 +1432,10 @@ releqjoin( mvc *sql, list *l1, list *l2,
if (list_length(l1) <= 1) {
l = l1->h->data;
r = l2->h->data;
- return stmt_join(sql->sa, l, r, cmp_op);
+ r = stmt_join(sql->sa, l, r, cmp_op);
+ if (need_left)
+ r->flag = cmp_left;
+ return r;
}
if (used_hash) {
l = n1->data;
@@ -1445,6 +1448,8 @@ releqjoin( mvc *sql, list *l1, list *l2,
r = join_hash_key(sql, l2);
res = stmt_join(sql->sa, l, r, cmp_op);
}
+ if (need_left)
+ res->flag = cmp_left;
l = stmt_result(sql->sa, res, 0);
r = stmt_result(sql->sa, res, 1);
for (; n1 && n2; n1 = n1->next, n2 = n2->next) {
@@ -1478,6 +1483,7 @@ rel2bin_join( mvc *sql, sql_rel *rel, li
node *en = NULL, *n;
stmt *left = NULL, *right = NULL, *join = NULL, *jl, *jr;
stmt *ld = NULL, *rd = NULL;
+ int need_left = (rel->flag == LEFT_JOIN);
if (rel->l) /* first construct the left sub relation */
left = subrel_bin(sql, rel->l, refs);
@@ -1582,9 +1588,11 @@ rel2bin_join( mvc *sql, sql_rel *rel, li
list_append(rje, s->op2);
}
if (list_length(lje) > 1) {
- join = releqjoin(sql, lje, rje, used_hash, cmp_equal);
+ join = releqjoin(sql, lje, rje, used_hash, cmp_equal,
need_left);
} else if (!join) {
join = stmt_join(sql->sa, lje->h->data, rje->h->data,
cmp_equal);
+ if (need_left)
+ join->flag = cmp_left;
}
} else {
stmt *l = bin_first_column(sql->sa, left);
@@ -1740,7 +1748,7 @@ rel2bin_semijoin( mvc *sql, sql_rel *rel
}
}
if (list_length(lje) > 1) {
- join = releqjoin(sql, lje, rje, 0 /* no hash used */,
cmp_equal);
+ join = releqjoin(sql, lje, rje, 0 /* no hash used */,
cmp_equal, 0);
} else if (!join) {
join = stmt_join(sql->sa, lje->h->data, rje->h->data,
cmp_equal);
}
@@ -1989,7 +1997,7 @@ rel2bin_except( mvc *sql, sql_rel *rel,
list_append(lje, l);
list_append(rje, r);
}
- s = releqjoin(sql, lje, rje, 1 /* no hash used */, cmp_equal_nil);
+ s = releqjoin(sql, lje, rje, 1 /* no hash used */, cmp_equal_nil, 0);
lm = stmt_result(sql->sa, s, 0);
rm = stmt_result(sql->sa, s, 1);
@@ -2099,7 +2107,7 @@ rel2bin_inter( mvc *sql, sql_rel *rel, l
list_append(lje, l);
list_append(rje, r);
}
- s = releqjoin(sql, lje, rje, 1 /* no hash used */, cmp_equal_nil);
+ s = releqjoin(sql, lje, rje, 1 /* no hash used */, cmp_equal_nil, 0);
lm = stmt_result(sql->sa, s, 0);
rm = stmt_result(sql->sa, s, 1);
@@ -2756,7 +2764,7 @@ insert_check_ukey(mvc *sql, list *insert
list_append(lje, col);
list_append(rje, cs->op1);
}
- s = releqjoin(sql, lje, rje, 1 /* hash used */,
cmp_equal);
+ s = releqjoin(sql, lje, rje, 1 /* hash used */,
cmp_equal, 0);
s = stmt_result(sql->sa, s, 0);
}
s = stmt_binop(sql->sa, stmt_aggr(sql->sa, s, NULL, NULL, cnt,
1, 0), stmt_atom_wrd(sql->sa, 0), ne);
@@ -3162,7 +3170,7 @@ update_check_ukey(mvc *sql, stmt **updat
list_append(lje, stmt_col(sql, c->c, nu_tids));
list_append(rje, upd);
}
- s = releqjoin(sql, lje, rje, 1 /* hash used */,
cmp_equal);
+ s = releqjoin(sql, lje, rje, 1 /* hash used */,
cmp_equal, 0);
s = stmt_result(sql->sa, s, 0);
s = stmt_binop(sql->sa, stmt_aggr(sql->sa, s, NULL,
NULL, cnt, 1, 0), stmt_atom_wrd(sql->sa, 0), ne);
}
@@ -3394,7 +3402,7 @@ join_updated_pkey(mvc *sql, sql_key * k,
list_append(lje, upd);
list_append(rje, col);
}
- s = releqjoin(sql, lje, rje, 1 /* hash used */, cmp_equal);
+ s = releqjoin(sql, lje, rje, 1 /* hash used */, cmp_equal, 0);
s = stmt_result(sql->sa, s, 0);
/* add missing nulls */
@@ -3698,7 +3706,7 @@ join_idx_update(mvc *sql, sql_idx * i, s
list_append(lje, check_types(sql, &rc->c->type, upd,
type_equal));
list_append(rje, stmt_col(sql, rc->c, ptids));
}
- s = releqjoin(sql, lje, rje, 0 /* no hash used */, cmp_equal);
+ s = releqjoin(sql, lje, rje, 0 /* no hash used */, cmp_equal, 0);
l = stmt_result(sql->sa, s, 0);
r = stmt_result(sql->sa, s, 1);
diff --git a/sql/backends/monet5/sql_gencode.c
b/sql/backends/monet5/sql_gencode.c
--- a/sql/backends/monet5/sql_gencode.c
+++ b/sql/backends/monet5/sql_gencode.c
@@ -1559,19 +1559,25 @@ static int
case st_join:{
int l;
int r;
+ int cmp = s->flag;
+ int left = (cmp == cmp_left);
char *sjt = "subjoin";
+ if (left) {
+ cmp = cmp_equal;
+ sjt = "subleftjoin";
+ }
if ((l = _dumpstmt(sql, mb, s->op1)) < 0)
return -1;
if ((r = _dumpstmt(sql, mb, s->op2)) < 0)
return -1;
assert(l >= 0 && r >= 0);
- if (s->flag == cmp_joined) {
+ if (cmp == cmp_joined) {
s->nr = l;
return s->nr;
}
- if (s->flag == cmp_project || s->flag ==
cmp_reorder_project) {
+ if (cmp == cmp_project || cmp == cmp_reorder_project) {
int ins;
/* delta bat */
@@ -1596,7 +1602,7 @@ static int
return s->nr;
}
/* projections, ie left is void headed */
- if (s->flag == cmp_project)
+ if (cmp == cmp_project)
q = newStmt1(mb, algebraRef,
"leftfetchjoin");
else
q = newStmt2(mb, algebraRef,
leftjoinRef);
@@ -1609,7 +1615,7 @@ static int
}
- switch (s->flag) {
+ switch (cmp) {
case cmp_equal:
q = newStmt1(mb, algebraRef, sjt);
q = pushReturn(mb, q, newTmpVariable(mb,
TYPE_any));
@@ -1656,13 +1662,13 @@ static int
q = pushArgument(mb, q, r);
q = pushNil(mb, q, TYPE_bat);
q = pushNil(mb, q, TYPE_bat);
- if (s->flag == cmp_lt)
+ if (cmp == cmp_lt)
q = pushInt(mb, q, -1);
- else if (s->flag == cmp_lte)
+ else if (cmp == cmp_lte)
q = pushInt(mb, q, -2);
- else if (s->flag == cmp_gt)
+ else if (cmp == cmp_gt)
q = pushInt(mb, q, 1);
- else if (s->flag == cmp_gte)
+ else if (cmp == cmp_gte)
q = pushInt(mb, q, 2);
q = pushBit(mb, q, TRUE);
q = pushNil(mb, q, TYPE_lng);
diff --git a/sql/benchmarks/tpch/alter.sql b/sql/benchmarks/tpch/alter.sql
--- a/sql/benchmarks/tpch/alter.sql
+++ b/sql/benchmarks/tpch/alter.sql
@@ -56,7 +56,7 @@ ADD CONSTRAINT ORDERS_FK1 FOREIGN KEY (O
ALTER TABLE LINEITEM
ADD CONSTRAINT LINEITEM_FK1 FOREIGN KEY (L_ORDERKEY) references ORDERS;
-ALTER TABLE LINEITEM
+plan ALTER TABLE LINEITEM
ADD CONSTRAINT LINEITEM_FK2 FOREIGN KEY (L_PARTKEY,L_SUPPKEY) references
PARTSUPP;
diff --git a/sql/benchmarks/tpch/load.sh b/sql/benchmarks/tpch/load.sh
--- a/sql/benchmarks/tpch/load.sh
+++ b/sql/benchmarks/tpch/load.sh
@@ -24,7 +24,8 @@ SF='sf-0.01'
#SF='sf-5'
dir=`echo $SF | tr '[a-z]' '[A-Z]'`
-$SQL < c.sql-primary-foreign
+$SQL < c.sql
+#$SQL < c.sql-primary-foreign
#cat load-$SF-LOCKED.sql$T | sed -e s+PWD+$PWD/$dir+ | $SQL
cat load-$SF.sql$T | sed -e s+PWD+$PWD/$dir+ | $SQL
#$SQL < alter.sql
diff --git a/sql/include/sql_catalog.h b/sql/include/sql_catalog.h
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
