Changeset: 2c50b80d80c7 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2c50b80d80c7
Modified Files:
sql/backends/monet5/sql_cat.c
sql/backends/monet5/sql_statistics.c
sql/backends/monet5/sql_statistics.h
sql/server/rel_optimizer.c
sql/server/rel_optimizer.h
Branch: Apr2019
Log Message:
used patch from feature request Bug 6326, added cleanup of the
statistics on changing the access (read only vs read write)
diffs (219 lines):
diff --git a/sql/backends/monet5/sql_cat.c b/sql/backends/monet5/sql_cat.c
--- a/sql/backends/monet5/sql_cat.c
+++ b/sql/backends/monet5/sql_cat.c
@@ -19,6 +19,7 @@
#include "sql_mvc.h"
#include "sql_qc.h"
#include "sql_partition.h"
+#include "sql_statistics.h"
#include "mal_namespace.h"
#include "opt_prelude.h"
#include "querylog.h"
@@ -386,7 +387,7 @@ alter_table_del_table(mvc *sql, char *ms
}
static char *
-alter_table_set_access(mvc *sql, char *sname, char *tname, int access)
+alter_table_set_access(Client cntxt, mvc *sql, char *sname, char *tname, int
access)
{
sql_schema *s = mvc_bind_schema(sql, sname);
sql_table *t = NULL;
@@ -401,6 +402,8 @@ alter_table_set_access(mvc *sql, char *s
throw(SQL,"sql.alter_table_set_access",SQLSTATE(40000) "ALTER TABLE: set READ
or INSERT ONLY not possible with outstanding updates (wait until updates are
flushed)\n");
mvc_access(sql, t, access);
+ if (access == 0)
+ sql_drop_statistics(cntxt, t);
}
} else {
throw(SQL,"sql.alter_table_set_access",SQLSTATE(42S02) "ALTER
TABLE: no such table '%s' in schema '%s'", tname, sname);
@@ -1504,7 +1507,7 @@ SQLalter_set_table(Client cntxt, MalBlkP
int access = *getArgReference_int(stk, pci, 3);
initcontext();
- msg = alter_table_set_access(sql, sname, tname, access);
+ msg = alter_table_set_access(cntxt, sql, sname, tname, access);
return msg;
}
diff --git a/sql/backends/monet5/sql_statistics.c
b/sql/backends/monet5/sql_statistics.c
--- a/sql/backends/monet5/sql_statistics.c
+++ b/sql/backends/monet5/sql_statistics.c
@@ -55,6 +55,29 @@ strToStrSQuote(char **dst, size_t *len,
}
str
+sql_drop_statistics(Client cntxt, sql_table *t)
+{
+ node *ncol;
+ char *dquery, *msg;
+
+ dquery = (char *) GDKzalloc(96);
+ if (dquery == NULL) {
+ throw(SQL, "analyze", SQLSTATE(HY001) MAL_MALLOC_FAIL);
+ }
+ if (isTable(t) && t->columns.set) {
+ for (ncol = (t)->columns.set->h; ncol; ncol = ncol->next) {
+ sql_column *c = ncol->data;
+
+ snprintf(dquery, 96, "delete from sys.statistics where
\"column_id\" = %d;", c->base.id);
+ msg = SQLstatementIntern(cntxt, &dquery, "SQLanalyze",
TRUE, FALSE, NULL);
+ if (msg)
+ return msg;
+ }
+ }
+ return NULL;
+}
+
+str
sql_analyze(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
mvc *m = NULL;
diff --git a/sql/backends/monet5/sql_statistics.h
b/sql/backends/monet5/sql_statistics.h
--- a/sql/backends/monet5/sql_statistics.h
+++ b/sql/backends/monet5/sql_statistics.h
@@ -25,5 +25,6 @@
#endif
sql5_export str sql_analyze(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
+sql5_export str sql_drop_statistics(Client cntxt, sql_table *t);
#endif /* _SQL_STATISTICS_DEF */
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
@@ -51,7 +51,7 @@ static sql_subfunc *find_func( mvc *sql,
/* currently we only find simple column expressions */
void *
-name_find_column( sql_rel *rel, char *rname, char *name, int pnr, sql_rel **bt
)
+name_find_column( sql_rel *rel, const char *rname, const char *name, int pnr,
sql_rel **bt )
{
sql_exp *alias = NULL;
sql_column *c = NULL;
@@ -8455,6 +8455,103 @@ rel_merge_table_rewrite(int *changes, mv
return rel;
}
+static sql_rel*
+exp_skip_output_parts(sql_rel *rel)
+{
+ while ((is_topn(rel->op) || is_project(rel->op) || is_sample(rel->op))
&& rel->l) {
+ if (rel->op == op_groupby && list_empty(rel->r))
+ return rel; /* a group-by with no
columns is a plain aggregate and hence always returns one row */
+ rel = rel->l;
+ }
+ return rel;
+}
+
+/* return true if the given expression is guaranteed to have no rows */
+static int
+exp_is_zero_rows(mvc *sql, sql_rel *rel, sql_rel *sel)
+{
+ sql_table *t;
+ node *n;
+
+ if (!rel)
+ return 0;
+ rel = exp_skip_output_parts(rel);
+ if (is_select(rel->op) && rel->l) {
+ sel = rel;
+ rel = exp_skip_output_parts(rel->l);
+ }
+ if (!sel)
+ return 0;
+ if (rel->op == op_join)
+ return exp_is_zero_rows(sql, rel->l, sel) ||
exp_is_zero_rows(sql, rel->r, sel);
+ if (rel->op == op_left || is_semi(rel->op))
+ return exp_is_zero_rows(sql, rel->l, sel);
+ if (rel->op == op_right)
+ return exp_is_zero_rows(sql, rel->r, sel);
+ if (!is_basetable(rel->op) || !rel->l)
+ return 0;
+ t = rel->l;
+ if (!isTable(t) || t->access != TABLE_READONLY)
+ return 0;
+
+ if (sel->exps) for (n = sel->exps->h; n; n = n->next) {
+ sql_exp *e = n->data;
+ atom *lval = NULL, *hval = NULL;
+
+ if (e->type == e_cmp && (e->flag == cmp_equal || e->f)) { /*
half-ranges are theoretically optimizable here, but not implemented */
+ sql_exp *c = e->l;
+ if (c->type == e_column) {
+ sql_exp *l = e->r;
+ sql_exp *h = e->f;
+
+ lval = exp_flatten(sql, l);
+ hval = h ? exp_flatten(sql, h) : lval;
+ if (lval && hval) {
+ sql_rel *bt;
+ sql_column *col = name_find_column(sel,
c->rname, c->name, -2, &bt);
+ void *min, *max;
+ if (col
+ && col->t == t
+ &&
sql_trans_ranges(sql->session->tr, col, &min, &max)
+ && !exp_range_overlap(sql, c,
min, max, lval, hval)) {
+ return 1;
+ }
+ }
+ }
+ }
+ }
+ return 0;
+}
+
+/* discard sides of UNION or UNION ALL which cannot produce any rows, as per
+statistics, similarly to the merge table optimizer, e.g.
+ select * from a where x between 1 and 2 union all select * from b where
x between 1 and 2
+-> select * from b where x between 1 and 2 [assuming a has no rows with
1<=x<=2]
+*/
+static sql_rel *
+rel_remove_union_partitions(int *changes, mvc *sql, sql_rel *rel)
+{
+ if (!is_union(rel->op))
+ return rel;
+ if (exp_is_zero_rows(sql, rel->l, NULL)) {
+ sql_rel *r = rel->r;
+ rel->r = NULL;
+ rel_destroy(rel);
+ (*changes)++;
+ sql->caching = 0;
+ return r;
+ }
+ if (exp_is_zero_rows(sql, rel->r, NULL)) {
+ sql_rel *l = rel->l;
+ rel->l = NULL;
+ rel_destroy(rel);
+ (*changes)++;
+ sql->caching = 0;
+ return l;
+ }
+ return rel;
+}
+
/* TODO move all apply related stuff in to rel_apply.c/h */
static int exps_uses_exps(list *users, list *exps);
@@ -9552,6 +9649,9 @@ optimize_rel(mvc *sql, sql_rel *rel, int
if (gp.cnt[op_select])
rel = rewrite_topdown(sql, rel, &rel_push_select_down_union,
&changes);
+ if (gp.cnt[op_union] && gp.cnt[op_select])
+ rel = rewrite(sql, rel, &rel_remove_union_partitions,
&changes);
+
if (gp.cnt[op_groupby]) {
rel = rewrite_topdown(sql, rel, &rel_push_aggr_down, &changes);
rel = rewrite_topdown(sql, rel, &rel_push_groupby_down,
&changes);
diff --git a/sql/server/rel_optimizer.h b/sql/server/rel_optimizer.h
--- a/sql/server/rel_optimizer.h
+++ b/sql/server/rel_optimizer.h
@@ -16,7 +16,7 @@ extern sql_rel * rel_optimizer(mvc *sql,
extern int exp_joins_rels(sql_exp *e, list *rels);
-extern void *name_find_column( sql_rel *rel, char *rname, char *name, int pnr,
sql_rel **bt );
+extern void *name_find_column( sql_rel *rel, const char *rname, const char
*name, int pnr, sql_rel **bt );
extern sql_rel * rel_dce(mvc *sql, sql_rel *rel);
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
