Update of /cvsroot/monetdb/sql/src/server
In directory 23jxhf1.ch3.sourceforge.com:/tmp/cvs-serv8090/src/server
Modified Files:
Tag: Nov2009
rel_optimizer.mx sql_mvc.mx
Log Message:
new optimizer to fix (performance) bug. We should always start grouping with
a sorted column (if its part of a group by list).
Index: sql_mvc.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_mvc.mx,v
retrieving revision 1.226.2.2
retrieving revision 1.226.2.3
diff -u -d -r1.226.2.2 -r1.226.2.3
--- sql_mvc.mx 9 Oct 2009 08:52:22 -0000 1.226.2.2
+++ sql_mvc.mx 20 Nov 2009 13:06:36 -0000 1.226.2.3
@@ -192,6 +192,7 @@
extern sql_column *mvc_null(mvc *c, sql_column *col, int flag);
extern sql_column *mvc_default(mvc *c, sql_column *col, char *val);
extern sql_column *mvc_drop_default(mvc *c, sql_column *col);
+extern int mvc_is_sorted(mvc *c, sql_column *col);
extern sql_key *mvc_create_ukey(mvc *m, sql_table *t, char *kname, key_type
kt);
extern sql_key *mvc_create_ukey_done(mvc *m, sql_key *k);
@@ -1415,6 +1416,15 @@
return sql_trans_alter_default(m->session->tr, col, NULL);
}
+int
+mvc_is_sorted(mvc *m, sql_column *col)
+{
+ if (mvc_debug)
+ fprintf(stderr, "mvc_is_sorted %s\n", col->base.name);
+
+ return sql_trans_is_sorted(m->session->tr, col);
+}
+
/* variable management */
void
stack_push_var(mvc *sql, char *name, stmt *var, sql_subtype *type)
Index: rel_optimizer.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_optimizer.mx,v
retrieving revision 1.71.2.3
retrieving revision 1.71.2.4
diff -u -d -r1.71.2.3 -r1.71.2.4
--- rel_optimizer.mx 11 Nov 2009 19:41:57 -0000 1.71.2.3
+++ rel_optimizer.mx 20 Nov 2009 13:06:35 -0000 1.71.2.4
@@ -2233,6 +2233,47 @@
return rel;
}
+/* Compute the efficiency of using this expression early in a group by list */
+static int
+score_gbe( mvc *sql, sql_rel *rel, sql_exp *e)
+{
+ int res = 10;
+ sql_subtype *t = exp_subtype(e);
+ sql_column *c = NULL;
+
+ /* can we find out if the underlying table is sorted */
+ if ( (c = exp_find_column(rel, e)) != NULL) {
+ if (mvc_is_sorted (sql, c))
+ res += 500;
+ }
+
+ /* is the column selective */
+ if (t->digits)
+ res -= t->digits;
+ return res;
+}
+
+/* reorder group by expressions */
+static sql_rel *
+rel_groupby_order(int *changes, mvc *sql, sql_rel *rel)
+{
+ list *gbe = rel->r;
+
+ *changes = 0;
+ if (is_groupby(rel->op) && list_length(gbe) > 1 && list_length(gbe)<9) {
+ node *n;
+ int i, *scores = alloca(sizeof(int) * list_length(gbe));
+
+ memset(scores, 0, sizeof(int)*list_length(gbe));
+ for (i = 0, n = gbe->h; n; i++, n = n->next) {
+ scores[i] = score_gbe(sql, rel, n->data);
+ }
+ rel->r = list_keysort(gbe, scores, (fdup)exp_dup);
+ list_destroy(gbe);
+ }
+ return rel;
+}
+
/* Pushing projects up the tree. Done very early in the optimizer.
* Makes later steps easier.
*/
@@ -3251,8 +3292,10 @@
if (gp.cnt[op_join] && gp.cnt[op_groupby])
rel = rewrite(sql, rel, &rel_push_join_down);
- if (gp.cnt[op_groupby])
+ if (gp.cnt[op_groupby]) {
rel = rewrite(sql, rel, &rel_avg2sum_count);
+ rel = rewrite(sql, rel, &rel_groupby_order);
+ }
if (gp.cnt[op_join] || gp.cnt[op_left])
rel = rewrite(sql, rel, &rel_join_order);
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Monetdb-sql-checkins mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
