I see. Thank you for your kind help, Prof. Kersten. Best, Wenjian On Fri, Apr 1, 2016 at 2:54 PM, Martin Kersten wrote:

On 01/04/16 07:00, Xu,Wenjian wrote:

...

Hi all,

I would like to see the intermediate result (i.e., MAL instructions) after each optimizer step in a optimizer pipeline.

So I searched Monetdb's documents and found the following sentence in https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/OptimizerPipeline... :

"Alternatively, the SQL DEBUG statement modifier in combination with the 'o' command provides access to the intermediate optimizer results."

I cannot figure out what this sentence means. What I know about SQL DEBUG statement is to use "debug <SQL statement>" to enter the debug mode and iterate through each (final) MAL instruction one by one. How could I use 'o' command to access intermediate optimizer results?

if you step iterate through the plan in MAL debugger you have several commands at your disposal. For this type 'help'. One of the options is 'optimizer' or abreviated with 'o', which shows you the optimizer steps. Using the modified 'list' command, you can access each intermediate, e.g. 'list[3]' to illustrate the plan after the 3rd optimizer step.

regards, Martin

...

I am using MonetDB 11.21.17 (JUL2015 SP3) and my environment is Ubuntu14.04.

Thank you very much.

Best regards, XU Wenjian

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

'

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Dead code should have reported 0 On 01/04/16 09:42, Xu,Wenjian wrote:

I tried to use 'list[<step>]' to show the plan after each optimizer step, but I found that the intermediate plan did not change no matter what value of 'step' is.

The procedures I take is as follows:

1): 'sql> debug SELECT COUNT(*) from customer;' to enter the debugger

2): use 'next' commands several times to enter into the body of the MAL plan.

3): use 'optimizer' command to show the effectiveness of optimizer steps:

======================================================== user.s6_1[ 0] optimizer.SQLgetstatistics actions= 3 time=19 usec user.s6_1[ 1] inline actions= 0 time=3 usec user.s6_1[ 2] remap actions= 0 time=2 usec user.s6_1[ 3] costModel actions= 1 time=1 usec user.s6_1[ 4] coercions actions= 0 time=1 usec user.s6_1[ 5] evaluate actions= 0 time=2 usec user.s6_1[ 6] aliases actions= 0 time=3 usec user.s6_1[ 7] pushselect actions= 0 time=2 usec user.s6_1[ 8] mitosis actions= 0 time=4 usec user.s6_1[ 9] mergetable actions= 0 time=14 usec user.s6_1[10] deadcode actions= 9 time=4 usec user.s6_1[11] commonTerms actions= 0 time=3 usec user.s6_1[12] joinPath actions= 0 time=2 usec user.s6_1[13] reorder actions= 1 time=8 usec user.s6_1[14] deadcode actions= 9 time=3 usec user.s6_1[15] reduce actions=15 time=6 usec user.s6_1[16] matpack actions= 0 time=1 usec user.s6_1[17] dataflow actions= 0 time=0 usec user.s6_1[18] querylog actions= 0 time=1 usec user.s6_1[19] multiplex actions= 0 time=1 usec user.s6_1[20] generator actions= 0 time=3 usec user.s6_1[21] garbageCollector actions= 1 time=3 usec user.s6_1[22] garbageCollector actions= 1 time=3 usec =========================================================

From the summary we can see at least *deadcode (step 14)* and *reduce (step 15)* DO change the plan (actions=9 and actions=15, respectively.)

4) I would like to see what instructions have changed in detail, so I use commands 'list[13]' and 'list[15]'.

Result of 'list[13]':

====================================================================== function user.s6_1{autoCommit=true}():void; # X_2 := sql.mvc(); # SQLmvc X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","customer"); # SQLtid X_6:bat[:oid,:int] := sql.bind(X_2,"sys","customer","c_custkey",0); # mvc_bind_wrap (X_9,r1_9) := sql.bind(X_2,"sys","customer","c_custkey",2); # mvc_bind_wrap X_12:bat[:oid,:int] := sql.bind(X_2,"sys","customer","c_custkey",1); # mvc_bind_wrap X_14 := sql.delta(X_6,X_9,r1_9,X_12); # DELTAbat X_15 := algebra.leftfetchjoin(X_3,X_14); # ALGleftfetchjoin X_16 := aggr.count(X_15); # ALGcount_bat sql.resultSet("sys.L1","L1","wrd",64,0,7,X_16); # mvc_scalar_value_wrap end user.s6_1; # # querylog.define("debug select count(*) from customer;","default_pipe") # optimizer.deadcode(); # OPTwrapper optimizer.reduce(); # OPTwrapper optimizer.matpack(); # OPTwrapper optimizer.dataflow(); # OPTwrapper optimizer.querylog(); # OPTwrapper optimizer.multiplex(); # OPTwrapper optimizer.generator(); # OPTwrapper optimizer.garbageCollector(); # OPTwrapper #optimizer.SQLgetstatistics actions= 3 time=19 usec # #inline actions= 0 time=3 usec # #remap actions= 0 time=2 usec # #costModel actions= 1 time=1 usec # #coercions actions= 0 time=1 usec # #evaluate actions= 0 time=2 usec # #aliases actions= 0 time=3 usec # #pushselect actions= 0 time=2 usec # #mitosis actions= 0 time=4 usec # #mergetable actions= 0 time=14 usec # #deadcode actions= 9 time=4 usec # #commonTerms actions= 0 time=3 usec # #joinPath actions= 0 time=2 usec # #reorder actions= 1 time=8 usec # ========================================================

Result of 'list[15]':

====================================================================== function user.s6_1{autoCommit=true}():void; # X_2 := sql.mvc(); # SQLmvc X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","customer"); # SQLtid X_6:bat[:oid,:int] := sql.bind(X_2,"sys","customer","c_custkey",0); # mvc_bind_wrap (X_9,r1_9) := sql.bind(X_2,"sys","customer","c_custkey",2); # mvc_bind_wrap X_12:bat[:oid,:int] := sql.bind(X_2,"sys","customer","c_custkey",1); # mvc_bind_wrap X_14 := sql.delta(X_6,X_9,r1_9,X_12); # DELTAbat X_15 := algebra.leftfetchjoin(X_3,X_14); # ALGleftfetchjoin X_16 := aggr.count(X_15); # ALGcount_bat sql.resultSet("sys.L1","L1","wrd",64,0,7,X_16); # mvc_scalar_value_wrap end user.s6_1; # # querylog.define("debug select count(*) from customer;","default_pipe") # optimizer.matpack(); # OPTwrapper optimizer.dataflow(); # OPTwrapper optimizer.querylog(); # OPTwrapper optimizer.multiplex(); # OPTwrapper optimizer.generator(); # OPTwrapper optimizer.garbageCollector(); # OPTwrapper #optimizer.SQLgetstatistics actions= 3 time=19 usec # #inline actions= 0 time=3 usec # #remap actions= 0 time=2 usec # #costModel actions= 1 time=1 usec # #coercions actions= 0 time=1 usec # #evaluate actions= 0 time=2 usec # #aliases actions= 0 time=3 usec # #pushselect actions= 0 time=2 usec # #mitosis actions= 0 time=4 usec # #mergetable actions= 0 time=14 usec # #deadcode actions= 9 time=4 usec # #commonTerms actions= 0 time=3 usec # #joinPath actions= 0 time=2 usec # #reorder actions= 1 time=8 usec # #deadcode actions= 9 time=3 usec # #reduce actions=15 time=6 usec # =========================================================================

My question is, why the above two plans are identical? Also, the plan is exactly the *final plan* (I use EXPLAIN statement to check that).

Why 'list[<step>]' command does not work? Did I miss something? Thank you very much for your help.

Best, Wenjian

On Fri, Apr 1, 2016 at 2:54 PM, Martin Kersten mailto:martin.kersten@cwi.nl> wrote:

On 01/04/16 07:00, Xu,Wenjian wrote:

Hi all,

I would like to see the intermediate result (i.e., MAL instructions) after each optimizer step in a optimizer pipeline.

So I searched Monetdb's documents and found the following sentence in https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/OptimizerPipeline...:

"Alternatively, the SQL DEBUG statement modifier in combination with the 'o' command provides access to the intermediate optimizer results."

I cannot figure out what this sentence means. What I know about SQL DEBUG statement is to use "debug <SQL statement>" to enter the debug mode and iterate through each (final) MAL instruction one by one. How could I use 'o' command to access intermediate optimizer results?

if you step iterate through the plan in MAL debugger you have several commands at your disposal. For this type 'help'. One of the options is 'optimizer' or abreviated with 'o', which shows you the optimizer steps. Using the modified 'list' command, you can access each intermediate, e.g. 'list[3]' to illustrate the plan after the 3rd optimizer step.

regards, Martin

I am using MonetDB 11.21.17 (JUL2015 SP3) and my environment is Ubuntu14.04.

Thank you very much.

Best regards, XU Wenjian

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

' _______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list