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 checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list