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 <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/OptimizerPipelines:

"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