Changeset: fcca8a34ec38 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/fcca8a34ec38
Modified Files:
clients/mapiclient/mhelp.c
sql/ChangeLog.Dec2025
sql/backends/monet5/sql.c
sql/backends/monet5/sql_execute.c
sql/backends/monet5/sql_gencode.c
sql/backends/monet5/sql_scenario.c
sql/server/rel_optimizer.c
sql/server/sql_mvc.c
sql/server/sql_mvc.h
sql/server/sql_parser.y
sql/server/sql_scan.c
sql/server/sqlparse.c
Branch: Dec2025
Log Message:
More improvements to EXPLAIN statement
diffs (truncated from 478 to 300 lines):
diff --git a/clients/mapiclient/mhelp.c b/clients/mapiclient/mhelp.c
--- a/clients/mapiclient/mhelp.c
+++ b/clients/mapiclient/mhelp.c
@@ -412,12 +412,15 @@ SQLhelp sqlhelp1[] = {
{"EXPLAIN",
"Display logical or physical execution plan for the SQL statement.",
"EXPLAIN [BEFORE|AFTER] [step] [SHOW DETAILS] statement\n"
- "\twhere step is REL_UNNEST|REL_REWRITE|PHYSICAL",
+ "\twhere step is LOGICAL UNNEST | LOGICAL REWRITE [posint] [posint] |
PHYSICAL",
NULL,
"Plain EXPLAIN defaults to logical plan.\n"
- "Use REL_UNNEST|REL_REWRITE|PHYSICAL to specify compilation step to
show.\n"
+ "Use LOGICAL UNNEST|LOGICAL REWRITE|PHYSICAL to specify compilation
step to show.\n"
"Use BEFORE|AFTER to specify moment of compilation step to output.\n"
"The default is AFTER.\n"
+ "Two positive numbers can be passed to LOGICAL REWRITE to stop at
specific\n"
+ "rewriter index or rewrite loop cycle, respectively.\n"
+ "If only one positive number is passed to LOGICAL REWRITE, rewrite
stop cycle defaults to 0.\n"
"SHOW DETAILS displays column properties, rewriter number of changes\n"
"and time spent.\n"
"See also
https://www.monetdb.org/documentation/admin-guide/debugging-features/explain-sql-stmt/"},
diff --git a/sql/ChangeLog.Dec2025 b/sql/ChangeLog.Dec2025
--- a/sql/ChangeLog.Dec2025
+++ b/sql/ChangeLog.Dec2025
@@ -34,12 +34,13 @@
guaranteed.
* Fri Oct 3 2025 Lucas Pereira <[email protected]>
-- EXPLAIN now supports a BEFORE/AFTER REL_UNNEST/REL_REWRITE/PHYSICAL
+- EXPLAIN now supports a BEFORE/AFTER LOGICAL UNNEST/LOGICAL REWRITE/PHYSICAL
clause to indicate which phase of query compilation to show. A plain
- EXPLAIN is equivalent to EXPLAIN BEFORE PHYSICAL, which is what PLAN
- used to do. The old EXPLAIN is now EXPLAIN PHYSICAL. SHOW DETAILS
- includes more information about properties, rewriters number of
- changes and time spent.
+ EXPLAIN is equivalent to EXPLAIN AFTER REWRITE, which is what PLAN
+ used to do. The old EXPLAIN is now EXPLAIN PHYSICAL. LOGICAL REWRITE also
+ supports specifying two positive values, rewriter index number and
+ optimizer loop cycle stop counter. SHOW DETAILS includes more information
+ about properties, rewriters number of changes and time spent.
- The PLAN keyword has been removed.
* Tue Sep 16 2025 Sjoerd Mullender <[email protected]>
diff --git a/sql/backends/monet5/sql.c b/sql/backends/monet5/sql.c
--- a/sql/backends/monet5/sql.c
+++ b/sql/backends/monet5/sql.c
@@ -138,7 +138,7 @@ sql_symbol2relation(backend *be, symbol
sql_rel *rel;
sql_query *query = query_create(be->mvc);
int value_based_opt = be->mvc->emode != m_prepare, storage_based_opt;
- int profile = be->mvc->emode == m_plan;
+ int profile = be->mvc->emode == m_explain;
rel = rel_semantic(query, sym);
@@ -5623,8 +5623,6 @@ SQLread_dump_rel(Client cntxt, MalBlkPtr
if (refs == NULL)
goto bailout;
- m->step = S_REL_REWRITE;
- m->temporal = T_AFTER;
m->show_details = true;
rel_print_refs(m, s, rel, 0, refs, 0);
diff --git a/sql/backends/monet5/sql_execute.c
b/sql/backends/monet5/sql_execute.c
--- a/sql/backends/monet5/sql_execute.c
+++ b/sql/backends/monet5/sql_execute.c
@@ -61,7 +61,7 @@ SQLrun(Client c, backend *be)
TRC_INFO(SQL_EXECUTION, "Executing: %s", c->query);
MT_thread_setworking(c->query);
- if (m->emod == mod_explain) {
+ if (m->emod == mod_explain_phys) {
if (c->curprg->def)
printFunction(c->fdout, mb, 0, LIST_MAL_NAME |
LIST_MAL_VALUE | LIST_MAL_TYPE | LIST_MAL_MAPI);
} else {
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
@@ -988,7 +988,7 @@ sql_relation2stmt(backend *be, sql_rel *
sql_error(c, 003, SQLSTATE(42000) "Missing relation to convert
into statements");
return NULL;
} else {
- if (c->emode == m_plan) {
+ if (c->emode == m_explain) {
rel_print(c, r, 0);
} else {
s = output_rel_bin(be, r, top);
diff --git a/sql/backends/monet5/sql_scenario.c
b/sql/backends/monet5/sql_scenario.c
--- a/sql/backends/monet5/sql_scenario.c
+++ b/sql/backends/monet5/sql_scenario.c
@@ -1423,6 +1423,8 @@ SQLparser_body(Client c, backend *be)
m->temporal = T_NONE;
m->step = S_NONE;
m->show_details = false;
+ m->rewriter_stop_idx = -1;
+ m->rewriter_stop_cycle = -1;
m->trace = false;
c->query = NULL;
c->qryctx.starttime = GDKusec();
@@ -1552,7 +1554,9 @@ SQLparser_body(Client c, backend *be)
opt = ((m->emod == mod_exec) == 0); /* no need
to optimize prepare - execute */
}
- if (be->mvc->emod == mod_explain && be->mvc->step ==
S_PHYSICAL && be->mvc->temporal == T_BEFORE)
+ if (be->mvc->emod == mod_explain_phys &&
+ be->mvc->step == S_PHYSICAL &&
+ be->mvc->temporal == T_BEFORE)
opt = 0;
if (err)
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
@@ -621,9 +621,21 @@ run_optimizer_set(visitor *v, sql_optimi
run->name = set[i].name;
int changes = v->changes;
lng clk = GDKusec();
+
+ if (BEFORE_LOGICAL_REWRITE(v->sql) &&
+ v->sql->rewriter_stop_idx >= 0 &&
+ set[i].index ==
v->sql->rewriter_stop_idx)
+ return rel;
+
rel = opt(v, gp, rel);
run->time += (GDKusec() - clk);
run->nchanges += (v->changes - changes);
+
+ if (AFTER_LOGICAL_REWRITE(v->sql) &&
+ v->sql->rewriter_stop_idx >= 0 &&
+ set[i].index ==
v->sql->rewriter_stop_idx)
+ return rel;
+
} else {
rel = opt(v, gp, rel);
}
@@ -637,38 +649,64 @@ run_optimizer_set(visitor *v, sql_optimi
static sql_rel *
rel_optimizer_one(mvc *sql, sql_rel *rel, int profile, int instantiate, int
value_based_opt, int storage_based_opt)
{
- global_props gp = {.cnt = {0}, .instantiate = (uint8_t)instantiate,
.opt_cycle = 0 };
- visitor v = { .sql = sql, .value_based_opt = value_based_opt,
.storage_based_opt = storage_based_opt, .changes = 1, .data = &gp };
+ global_props gp = {
+ .cnt = {0},
+ .instantiate = (uint8_t)instantiate,
+ .opt_cycle = 0
+ };
+
+ visitor v = {
+ .sql = sql,
+ .value_based_opt = value_based_opt,
+ .storage_based_opt = storage_based_opt,
+ .changes = 1,
+ .data = &gp
+ };
- sql->runs = !(ATOMIC_GET(&GDKdebug) & TESTINGMASK) && profile ?
ma_zalloc(sql->sa, NSQLREWRITERS * sizeof(sql_optimizer_run)) : NULL;
- for ( ;rel && gp.opt_cycle < 20 && v.changes; gp.opt_cycle++) {
+ sql->runs = !(ATOMIC_GET(&GDKdebug) & TESTINGMASK) && profile ?
+ ma_zalloc(sql->sa, NSQLREWRITERS * sizeof(sql_optimizer_run)) :
+ NULL;
+
+ for ( ; rel && gp.opt_cycle < 20 && v.changes; gp.opt_cycle++) {
v.changes = 0;
- gp = (global_props) {.cnt = {0}, .instantiate =
(uint8_t)instantiate, .opt_cycle = gp.opt_cycle};
- rel = rel_visitor_topdown(&v, rel, &rel_properties); /* collect
relational tree properties */
+ gp = (global_props) {
+ .cnt = {0},
+ .instantiate = (uint8_t)instantiate,
+ .opt_cycle = gp.opt_cycle
+ };
+ /* collect relational tree properties */
+ rel = rel_visitor_topdown(&v, rel, &rel_properties);
gp.opt_level = calculate_opt_level(sql, rel);
if (gp.opt_level == 0 && !gp.needs_mergetable_rewrite)
break;
sql->recursive = gp.recursive;
rel = run_optimizer_set(&v, sql->runs, rel, &gp,
pre_sql_optimizers);
+
+ if (sql->step == S_LOGICAL_REWRITE &&
+ sql->rewriter_stop_cycle >= 0 &&
+ gp.opt_cycle == sql->rewriter_stop_cycle)
+ return rel;
}
+
#ifndef NDEBUG
assert(gp.opt_cycle < 20);
#endif
- /* these optimizers run statistics gathered by the last optimization
cycle */
+ /* these opts run statistics gathered by the last optimization cycle */
rel = run_optimizer_set(&v, sql->runs, rel, &gp, post_sql_optimizers);
+
return rel;
}
static sql_exp *
exp_optimize_one(visitor *v, sql_rel *rel, sql_exp *e, int depth )
{
- (void)rel;
- (void)depth;
- if (e->type == e_psm && e->flag == PSM_REL && e->l) {
- e->l = rel_optimizer_one(v->sql, e->l, 0, v->changes,
v->value_based_opt, v->storage_based_opt);
- }
- return e;
+ (void)rel;
+ (void)depth;
+ if (e->type == e_psm && e->flag == PSM_REL && e->l) {
+ e->l = rel_optimizer_one(v->sql, e->l, 0, v->changes,
v->value_based_opt, v->storage_based_opt);
+ }
+ return e;
}
sql_rel *
@@ -677,8 +715,13 @@ rel_optimizer(mvc *sql, sql_rel *rel, in
if (rel && rel->op == op_ddl && rel->flag == ddl_psm) {
if (!list_empty(rel->exps)) {
bool changed = 0;
- visitor v = { .sql = sql, .value_based_opt =
value_based_opt, .storage_based_opt = storage_based_opt, .changes = instantiate
};
- for(node *n = rel->exps->h; n; n = n->next) {
+ visitor v = {
+ .sql = sql,
+ .value_based_opt = value_based_opt,
+ .storage_based_opt = storage_based_opt,
+ .changes = instantiate
+ };
+ for (node *n = rel->exps->h; n; n = n->next) {
sql_exp *e = n->data;
n->data = exp_visitor(&v, rel, e, 1,
exp_optimize_one, true, true, true, &changed);
}
diff --git a/sql/server/sql_mvc.c b/sql/server/sql_mvc.c
--- a/sql/server/sql_mvc.c
+++ b/sql/server/sql_mvc.c
@@ -805,6 +805,8 @@ mvc_create(sql_store *store, allocator *
m->emod = mod_none;
m->temporal = T_NONE;
m->step = S_NONE;
+ m->rewriter_stop_idx = -1;
+ m->rewriter_stop_cycle = -1;
m->show_details = false;
m->trace = false;
m->reply_size = 100;
@@ -1582,11 +1584,14 @@ sql_processrelation(mvc *sql, sql_rel *r
int emode = sql->emode;
if (!instantiate)
sql->emode = m_deps;
- if (emode == m_plan && BEFORE_REL_UNNEST(sql))
+ if (emode == m_explain && BEFORE_LOGICAL_UNNEST(sql))
return rel;
if (rel)
rel = rel_unnest(sql, rel);
- if (emode == m_plan && (AFTER_REL_UNNEST(sql) ||
BEFORE_REL_REWRITE(sql)))
+ if (emode == m_explain &&
+ (AFTER_LOGICAL_UNNEST(sql) ||
+ (BEFORE_LOGICAL_REWRITE(sql) &&
+ sql->rewriter_stop_idx == -1 && sql->rewriter_stop_cycle ==
-1)))
return rel;
sql->emode = emode;
if (rel)
diff --git a/sql/server/sql_mvc.h b/sql/server/sql_mvc.h
--- a/sql/server/sql_mvc.h
+++ b/sql/server/sql_mvc.h
@@ -55,36 +55,36 @@
/* allowed to reduce (in the where and having parts we can reduce) */
/* different query execution modes (emode) */
-#define m_normal 0
+#define m_normal 0
#define m_deallocate 1
-#define m_prepare 2
-#define m_plan 3
+#define m_prepare 2
+#define m_explain 3
/* special modes for function/procedure and view instantiation and
dependency generation */
#define m_instantiate 5
-#define m_deps 6
+#define m_deps 6
/* different query execution modifiers (emod) */
-#define mod_none 0
-#define mod_explain 1
-#define mod_exec 2
+#define mod_none 0
+#define mod_explain_phys 1
+#define mod_exec 2
-#define S_NONE 0
-#define S_REL_UNNEST 1
-#define S_REL_REWRITE 2
-#define S_PHYSICAL 3
+#define S_NONE 0
+#define S_LOGICAL_UNNEST 1
+#define S_LOGICAL_REWRITE 2
+#define S_PHYSICAL 3
-#define T_NONE 0
-#define T_BEFORE 1
-#define T_AFTER 2
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
