accessing intermediate optimizer results.

Martin Kersten martin.kersten at cwi.nl
Fri Apr 1 09:52:55 CEST 2016


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 <martin.kersten at cwi.nl <mailto: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 <mailto:users-list at monetdb.org>
>         https://www.monetdb.org/mailman/listinfo/users-list
>
>     '
>     _______________________________________________
>     users-list mailing list
>     users-list at monetdb.org <mailto: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
>



More information about the users-list mailing list