accessing intermediate optimizer results.

Xu,Wenjian zeroxwj at gmail.com
Fri Apr 1 09:42:10 CEST 2016


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 at 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 at monetdb.org
>> https://www.monetdb.org/mailman/listinfo/users-list
>>
>> '
> _______________________________________________
> users-list mailing list
> users-list at monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.monetdb.org/pipermail/users-list/attachments/20160401/95c3d6f0/attachment-0001.html>


More information about the users-list mailing list