Changeset: 94db23a8d2ea for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=94db23a8d2ea
Modified Files:
sql/server/rel_dump.c
sql/server/rel_exp.c
sql/server/rel_exp.h
sql/server/rel_optimizer.c
sql/server/rel_rel.c
sql/server/rel_rel.h
sql/server/rel_select.c
sql/server/rel_unnest.c
sql/server/rel_updates.c
Branch: Jun2020
Log Message:
Performance fix for tcpds q80 and related.
1. Fixed handling of cardinality of atomic expressions through groupings.
2. Added rel_setop_set_exps call which sets the cardinality of expressions
under a set relation, which was missing.
3. Updated exp_reset_card_and_freevar for these cases.
diffs (truncated from 428 to 300 lines):
diff --git a/sql/server/rel_dump.c b/sql/server/rel_dump.c
--- a/sql/server/rel_dump.c
+++ b/sql/server/rel_dump.c
@@ -1803,7 +1803,7 @@ rel_read(mvc *sql, char *r, int *pos, li
if (!(exps = read_exps(sql, NULL, NULL, NULL, r, pos, '[', 0)))
return NULL;
rel = rel_setop(sql->sa, lrel, rrel, j);
- rel->exps = exps;
+ rel_setop_set_exps(sql, rel, exps);
if (rel_set_types(sql, rel) < 0)
return sql_error(sql, -1, SQLSTATE(42000) "Setop:
number of expressions don't match\n");
set_processed(rel);
diff --git a/sql/server/rel_exp.c b/sql/server/rel_exp.c
--- a/sql/server/rel_exp.c
+++ b/sql/server/rel_exp.c
@@ -675,16 +675,16 @@ exp_alias_or_copy( mvc *sql, const char
tname = exp_relname(old);
if (!cname && exp_name(old) && has_label(old)) {
- ne = exp_column(sql->sa, exp_relname(old), exp_name(old),
exp_subtype(old), orel?orel->card:CARD_ATOM, has_nil(old), is_intern(old));
+ ne = exp_column(sql->sa, exp_relname(old), exp_name(old),
exp_subtype(old), orel && old->card != CARD_ATOM?orel->card:CARD_ATOM,
has_nil(old), is_intern(old));
return exp_propagate(sql->sa, ne, old);
} else if (!cname) {
exp_label(sql->sa, old, ++sql->label);
- ne = exp_column(sql->sa, exp_relname(old), exp_name(old),
exp_subtype(old), orel?orel->card:CARD_ATOM, has_nil(old), is_intern(old));
+ ne = exp_column(sql->sa, exp_relname(old), exp_name(old),
exp_subtype(old), orel && old->card != CARD_ATOM?orel->card:CARD_ATOM,
has_nil(old), is_intern(old));
return exp_propagate(sql->sa, ne, old);
} else if (cname && !old->alias.name) {
exp_setname(sql->sa, old, tname, cname);
}
- ne = exp_column(sql->sa, tname, cname, exp_subtype(old),
orel?orel->card:CARD_ATOM, has_nil(old), is_intern(old));
+ ne = exp_column(sql->sa, tname, cname, exp_subtype(old), orel &&
old->card != CARD_ATOM?orel->card:CARD_ATOM, has_nil(old), is_intern(old));
return exp_propagate(sql->sa, ne, old);
}
@@ -1574,7 +1574,7 @@ rel_find_exp_and_corresponding_rel_( sql
}
sql_exp *
-rel_find_exp_and_corresponding_rel(sql_rel *rel, sql_exp *e, sql_rel **res)
+rel_find_exp_and_corresponding_rel(sql_rel *rel, sql_exp *e, sql_rel **res,
bool *under_join)
{
sql_exp *ne = rel_find_exp_and_corresponding_rel_(rel, e, res);
@@ -1584,9 +1584,11 @@ rel_find_exp_and_corresponding_rel(sql_r
case op_right:
case op_full:
case op_join:
- ne = rel_find_exp_and_corresponding_rel(rel->l, e, res);
+ ne = rel_find_exp_and_corresponding_rel(rel->l, e, res,
under_join);
if (!ne)
- ne = rel_find_exp_and_corresponding_rel(rel->r,
e, res);
+ ne = rel_find_exp_and_corresponding_rel(rel->r,
e, res, under_join);
+ if (ne && under_join)
+ *under_join = true;
break;
case op_table:
if (rel->exps && e->type == e_column && e->l &&
exps_bind_column2(rel->exps, e->l, e->r))
@@ -1599,7 +1601,7 @@ rel_find_exp_and_corresponding_rel(sql_r
case op_inter:
{
if (rel->l)
- ne = rel_find_exp_and_corresponding_rel(rel->l,
e, res);
+ ne = rel_find_exp_and_corresponding_rel(rel->l,
e, res, under_join);
else if (rel->exps && e->l) {
ne = exps_bind_column2(rel->exps, e->l, e->r);
if (ne && res)
@@ -1619,7 +1621,9 @@ rel_find_exp_and_corresponding_rel(sql_r
break;
default:
if (!is_project(rel->op) && rel->l)
- ne = rel_find_exp_and_corresponding_rel(rel->l,
e, res);
+ ne = rel_find_exp_and_corresponding_rel(rel->l,
e, res, under_join);
+ if (ne && (rel->op == op_semi || rel->op == op_anti) &&
under_join)
+ *under_join = true;
}
}
return ne;
@@ -1628,7 +1632,7 @@ rel_find_exp_and_corresponding_rel(sql_r
sql_exp *
rel_find_exp( sql_rel *rel, sql_exp *e)
{
- return rel_find_exp_and_corresponding_rel(rel, e, NULL);
+ return rel_find_exp_and_corresponding_rel(rel, e, NULL, NULL);
}
int
diff --git a/sql/server/rel_exp.h b/sql/server/rel_exp.h
--- a/sql/server/rel_exp.h
+++ b/sql/server/rel_exp.h
@@ -125,7 +125,7 @@ extern unsigned int exp_card(sql_exp *e)
extern const char *exp_find_rel_name(sql_exp *e);
extern sql_exp *rel_find_exp( sql_rel *rel, sql_exp *e);
-extern sql_exp *rel_find_exp_and_corresponding_rel(sql_rel *rel, sql_exp *e,
sql_rel **res);
+extern sql_exp *rel_find_exp_and_corresponding_rel(sql_rel *rel, sql_exp *e,
sql_rel **res, bool *under_join);
extern int exp_cmp( sql_exp *e1, sql_exp *e2);
extern int exp_equal( sql_exp *e1, sql_exp *e2);
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
@@ -4191,8 +4191,7 @@ rel_push_aggr_down(mvc *sql, sql_rel *re
}
u = rel_setop(sql->sa, ul, ur, op_union);
- u->exps = rel_projections(sql, ul, NULL, 1, 1);
- u->nrcols = list_length(u->exps);
+ rel_setop_set_exps(sql, u, rel_projections(sql, ul, NULL, 1,
1));
set_processed(u);
if (rel->r) {
@@ -4709,12 +4708,13 @@ find_simple_projection_for_join2semi(sql
if (e->type == e_column) {
sql_rel *res = NULL;
sql_exp *found = NULL;
+ bool underjoin = false;
if (is_groupby(rel->op) || need_distinct(rel) ||
find_prop(e->p, PROP_HASHCOL))
return true;
- found = rel_find_exp_and_corresponding_rel(rel->l, e,
&res); /* grouping column on inner relation */
- if (found) {
+ found = rel_find_exp_and_corresponding_rel(rel->l, e,
&res, &underjoin); /* grouping column on inner relation */
+ if (found && !underjoin) {
if (find_prop(found->p, PROP_HASHCOL)) /*
primary key always unique */
return true;
if (found->type == e_column && found->card <=
CARD_AGGR) {
@@ -5589,6 +5589,8 @@ score_gbe(mvc *sql, sql_rel *rel, sql_ex
sql_subtype *t = exp_subtype(e);
sql_column *c = NULL;
+ if (e->card == CARD_ATOM) /* constants are trivial to group */
+ res += 1000;
/* can we find out if the underlying table is sorted */
if ((c = exp_find_column(rel, e, -2)) && mvc_is_sorted(sql, c))
res += 600;
@@ -8525,8 +8527,7 @@ rel_split_outerjoin(mvc *sql, sql_rel *r
add_nulls( sql, nr, r);
exps = rel_projections(sql, nl, NULL, 1, 1);
nl = rel_setop(sql->sa, nl, nr, op_union);
- nl->exps = exps;
- nr->nrcols = list_length(exps);
+ rel_setop_set_exps(sql, nl, exps);
set_processed(nl);
}
if (rel->op == op_right || rel->op == op_full) {
@@ -8545,8 +8546,7 @@ rel_split_outerjoin(mvc *sql, sql_rel *r
(fdup)NULL);
exps = rel_projections(sql, nl, NULL, 1, 1);
nl = rel_setop(sql->sa, nl, nr, op_union);
- nl->exps = exps;
- nl->nrcols = list_length(exps);
+ rel_setop_set_exps(sql, nl, exps);
set_processed(nl);
}
@@ -8949,7 +8949,7 @@ rel_merge_table_rewrite(mvc *sql, sql_re
sql_rel *l = n->data;
sql_rel *r =
n->next->data;
nrel =
rel_setop(sql->sa, l, r, op_union);
- rel_set_exps(nrel,
rel_projections(sql, rel, NULL, 1, 1));
+ rel_setop_set_exps(sql,
nrel, rel_projections(sql, rel, NULL, 1, 1));
set_processed(nrel);
append(ntables, nrel);
}
diff --git a/sql/server/rel_rel.c b/sql/server/rel_rel.c
--- a/sql/server/rel_rel.c
+++ b/sql/server/rel_rel.c
@@ -484,15 +484,9 @@ rel_setop(sql_allocator *sa, sql_rel *l,
rel->r = r;
rel->op = setop;
rel->exps = NULL;
- if (setop == op_union) {
- rel->card = CARD_MULTI;
- } else {
- rel->card = l->card;
- }
- if (l && r) {
- assert(l->nrcols == r->nrcols);
- rel->nrcols = l->nrcols;
- }
+ rel->card = is_union(setop) ? CARD_MULTI : l->card;
+ assert(l->nrcols == r->nrcols);
+ rel->nrcols = l->nrcols;
return rel;
}
@@ -522,6 +516,28 @@ rel_setop_check_types(mvc *sql, sql_rel
return rel_setop(sql->sa, l, r, op);
}
+void
+rel_setop_set_exps(mvc *sql, sql_rel *rel, list *exps)
+{
+ sql_rel *l = rel->l, *r = rel->r;
+ list *lexps = l->exps, *rexps = r->exps;
+
+ if (!is_project(l->op))
+ lexps = rel_projections(sql, l, NULL, 0, 1);
+ if (!is_project(r->op))
+ rexps = rel_projections(sql, r, NULL, 0, 1);
+
+ assert(is_set(rel->op) && list_length(lexps) == list_length(rexps) &&
list_length(exps) == list_length(lexps));
+
+ for (node *n = exps->h, *m = lexps->h, *o = rexps->h ; m && m && o ; n
= n->next, m = m->next,o = o->next) {
+ sql_exp *e = n->data, *f = m->data, *g = o->data;
+
+ e->card = is_union(rel->op) ? MAX(f->card, g->card) : f->card;
+ }
+ rel->nrcols = l->nrcols;
+ rel->exps = exps;
+}
+
sql_rel *
rel_crossproduct(sql_allocator *sa, sql_rel *l, sql_rel *r, operator_type join)
{
@@ -922,7 +938,7 @@ rel_groupby(mvc *sql, sql_rel *l, list *
sql_exp *e = en->data, *ne;
/* after the group by the cardinality reduces */
- e->card = rel->card;
+ e->card = MIN(e->card, rel->card); /* if the column is
an atom, the cardinality should not change */
ne = exp_ref(sql, e);
ne = exp_propagate(sql->sa, ne, e);
append(aggrs, ne);
diff --git a/sql/server/rel_rel.h b/sql/server/rel_rel.h
--- a/sql/server/rel_rel.h
+++ b/sql/server/rel_rel.h
@@ -75,6 +75,7 @@ extern sql_rel *rel_inplace_groupby(sql_
extern int rel_convert_types(mvc *sql, sql_rel *ll, sql_rel *rr, sql_exp **L,
sql_exp **R, int scale_fixing, check_type tpe);
extern sql_rel *rel_setop(sql_allocator *sa, sql_rel *l, sql_rel *r,
operator_type setop);
extern sql_rel *rel_setop_check_types(mvc *sql, sql_rel *l, sql_rel *r, list
*ls, list *rs, operator_type op);
+extern void rel_setop_set_exps(mvc *sql, sql_rel *rel, list *exps);
extern sql_rel *rel_crossproduct(sql_allocator *sa, sql_rel *l, sql_rel *r,
operator_type join);
/* in case e is an constant and rel is a simple project of only e, free rel */
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -5773,8 +5773,7 @@ rel_setquery_(sql_query *query, sql_rel
rel = rel_setop(sql->sa, l, r, (operator_type)op);
}
if (rel) {
- rel->exps = rel_projections(sql, rel, NULL, 0, 1);
- rel->nrcols = list_length(rel->exps);
+ rel_setop_set_exps(sql, rel, rel_projections(sql, rel, NULL, 0,
1));
set_processed(rel);
}
return rel;
diff --git a/sql/server/rel_unnest.c b/sql/server/rel_unnest.c
--- a/sql/server/rel_unnest.c
+++ b/sql/server/rel_unnest.c
@@ -1630,16 +1630,6 @@ exp_reset_card_and_freevar(mvc *sql, sql
return e;
switch(rel->op){
- case op_basetable:
- case op_truncate:
- case op_topn:
- case op_sample:
- case op_insert:
- case op_update:
- case op_delete:
- case op_ddl:
- case op_table:
- break;
case op_select:
case op_join:
case op_left:
@@ -1657,22 +1647,20 @@ exp_reset_card_and_freevar(mvc *sql, sql
e->card = exps_card(e->l);
} break;
case e_column: {
- sql_rel *l = rel->l, *r = rel->r, *ll = NULL, *rr =
NULL;
sql_exp *le = NULL, *re = NULL;
-
- if (is_set(rel->op)) {
- le = rel_find_exp_and_corresponding_rel(l, e,
&ll);
- re = rel_find_exp_and_corresponding_rel(r, e,
&rr);
- if (ll && rr) /* TODO fix this? */
- e->card = MAX(ll->card, rr->card);
- } else {
- if (l && (le =
rel_find_exp_and_corresponding_rel(l, e, &ll)) && ll) {
- e->card = ll->card;
- } else if (!is_simple_project(rel->op) && r &&
(re = rel_find_exp_and_corresponding_rel(r, e, &rr)) && rr) {
- e->card = rr->card;
- }
+ bool underjoinl = false, underjoinr = false;
+
+ le = rel_find_exp_and_corresponding_rel(rel->l, e,
NULL, &underjoinl);
+ if (!is_simple_project(rel->op) && !is_inter(rel->op)
&& !is_except(rel->op) && !is_semi(rel->op) && rel->r) {
+ re = rel_find_exp_and_corresponding_rel(rel->r,
e, NULL, &underjoinr);
+ /* if the expression is found under a join, the
cardinality expands to multi */
+ e->card =
MAX(le?underjoinl?CARD_MULTI:le->card:CARD_ATOM,
re?underjoinr?CARD_MULTI:re->card:CARD_ATOM);
+ } else if (e->card == CARD_ATOM) { /* unnested columns
vs atoms */
+ e->card =
le?underjoinl?CARD_MULTI:le->card:CARD_ATOM;
+ } else { /* general case */
+ e->card = (le &&
!underjoinl)?le->card:CARD_MULTI;
}
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
