[Monetdb-developers] Single column select vs aggregation

Stefan Manegold Stefan.Manegold at cwi.nl
Thu Feb 18 18:23:12 CET 2010


On Thu, Feb 18, 2010 at 05:46:31PM +0100, Stefan de Konink wrote:
> Attached is a trace of a single column select operation, with a max,
> avg and equality.
> 
> The equality takes about 4 times more time than the avg operation.
> 
> From the plan I see that the operation is executed paralel, but I do
> wonder why suddenly the uselect takes such a high amount of time.

Which version of MonetDB?
How configured / compiled?
How was the mserver5 started (which command line options were given)?
Any changes compared to the default monetdb5.conf?

Is the "kvk" column sorted?

Could you run the select query in the MAL debugger
(DEBUG select kvk from kvk where kvk = 412657690010;),
single step through it,
set the debug mask to 2097152
(debug 2097152)
just before the algebra.uselect() is executed,
and report the output on the server console after the algebra.uselect() has
been executed?

Stefan


> 
> Stefan

> sql>TRACE select max(kvk) from kvk;
> +--------------+
> | L7           |
> +==============+
> | 412657690010 |
> +--------------+
> 1 tuple
> +-------+---------------------------------------------------------------------------------------------------------+
> | ticks | stmt                                                                                                    |
> +=======+=========================================================================================================+
> |    39 | _33:bat[:oid,:lng] <tmp_6412>[1196533] := sql.bind("sys","kvk","kvk",0,1196532 at 0,nil:oid);              |
> |     7 | _11:bat[:oid,:oid] <tmp_5375>[0] := sql.bind_dbat("sys","kvk",1);                                       |
> |     7 | _5:bat[:oid,:lng] <tmp_5406>[0] := sql.bind("sys","kvk","kvk",2);                                       |
> |    12 | _31:bat[:oid,:lng] <tmp_6422>[1196532] := sql.bind("sys","kvk","kvk",0,0 at 0,1196532 at 0);                  |
> |     9 | _35<tmp_6412>[1196533] := algebra.selectNotNil(_33=<tmp_6412>:bat[:oid,:lng][1196533]);                 |
> |     7 | _12<tmpr_5375>[0] := bat.reverse(_11=<tmp_5375>:bat[:oid,:oid][0]);                                     |
> |     6 | _7<tmp_5406>[0] := algebra.selectNotNil(_5=<tmp_5406>:bat[:oid,:lng][0]);                               |
> |     5 | _34<tmp_6422>[1196532] := algebra.selectNotNil(_31=<tmp_6422>:bat[:oid,:lng][1196532]);                 |
> |    19 | _37<tmp_6373>[1196533] := algebra.kdifference(_35=<tmp_6412>[1196533],_5=<tmp_5406>:bat[:oid,:lng][0]); |
> |     6 | _11:bat[:oid,:oid]  := nil:BAT;                                                                         |
> |    24 | _38<tmpr_6347>[0] := algebra.semijoin(_7=<tmp_5406>[0],_31=<tmp_6422>:bat[:oid,:lng][1196532]);         |
> |    12 | _36<tmp_6351>[1196532] := algebra.kdifference(_34=<tmp_6422>[1196532],_5=<tmp_5406>:bat[:oid,:lng][0]); |
> |     5 | _35 := nil:BAT;                                                                                         |
> |    15 | _40<tmp_6376>[1196532] := algebra.kunion(_36=<tmp_6351>[1196532],_38=<tmpr_6347>[0]);                   |
> |     4 | _31:bat[:oid,:lng]  := nil:BAT;                                                                         |
> |    17 | _34 := nil:BAT;                                                                                         |
> |     5 | _5:bat[:oid,:lng]  := nil:BAT;                                                                          |
> |    11 | _42<tmp_6422>[1196532] := algebra.kdifference(_40=<tmp_6376>[1196532],_12=<tmpr_5375>[0]);              |
> |     9 | _38 := nil:BAT;                                                                                         |
> |     8 | _36 := nil:BAT;                                                                                         |
> |     6 | _8:bat[:oid,:lng] <tmp_5407>[0] := sql.bind("sys","kvk","kvk",1);                                       |
> |    13 | _39<tmpr_6351>[0] := algebra.semijoin(_7=<tmp_5406>[0],_33=<tmp_6412>:bat[:oid,:lng][1196533]);         |
> |    18 | _33:bat[:oid,:lng]  := nil:BAT;                                                                         |
> |     5 | _7 := nil:BAT;                                                                                          |
> |     8 | _40 := nil:BAT;                                                                                         |
> |     7 | _10<tmp_5407>[0] := algebra.selectNotNil(_8=<tmp_5407>:bat[:oid,:lng][0]);                              |
> |    23 | _41<tmp_6376>[1196533] := algebra.kunion(_37=<tmp_6373>[1196533],_39=<tmpr_6351>[0]);                   |
> |    12 | _43<tmp_6412>[1196533] := algebra.kdifference(_41=<tmp_6376>[1196533],_12=<tmpr_5375>[0]);              |
> |     9 | _39 := nil:BAT;                                                                                         |
> |     8 | _37 := nil:BAT;                                                                                         |
> |     5 | _8:bat[:oid,:lng]  := nil:BAT;                                                                          |
> | 12528 | _46 := aggr.max(_42=<tmp_6422>[1196532]);                                                               |
> |    25 | _41 := nil:BAT;                                                                                         |
> | 15285 | _47 := aggr.max(_43=<tmp_6412>[1196533]);                                                               |
> |    26 | _43 := nil:BAT;                                                                                         |
> |   117 | _44<tmp_6376>[0] := algebra.kdifference(_10=<tmp_5407>[0],_12=<tmpr_5375>[0]);                          |
> |    12 | _42 := nil:BAT;                                                                                         |
> |    10 | _48 := aggr.max(_44=<tmp_6376>[0]);                                                                     |
> |     6 | _12 := nil:BAT;                                                                                         |
> |     6 | _10 := nil:BAT;                                                                                         |
> |    15 | _45<tmp_6124>[3] := mat.pack(_46=243753400000:lng,_47=412657690010:lng,_48=nil:lng);                    |
> |    13 | _44 := nil:BAT;                                                                                         |
> |    27 | _49<tmp_6325>[2] := algebra.selectNotNil(_45=<tmp_6124>[3]);                                            |
> |    13 | _45 := nil:BAT;                                                                                         |
> |    53 | _13 := aggr.max(_49=<tmp_6325>[2]);                                                                     |
> |    10 | _49 := nil:BAT;                                                                                         |
> | 23668 | barrier _72 := language.dataflow();                                                                     |
> |    15 | sql.exportValue(1,"sys.kvk","L7","bigint",64,0,6,_13=412657690010:lng,"");                              |
> |    11 | end s4_1;                                                                                               |
> | 23919 | user.s4_1();                                                                                            |
> +-------+---------------------------------------------------------------------------------------------------------+
> 50 tuples
> 
> sql>TRACE select kvk from kvk where kvk = 412657690010;
> +--------------+
> | kvk          |
> +==============+
> | 412657690010 |
> +--------------+
> 1 tuple
> +--------+------------------------------------------------------------------------------------------------------------------------+
> | ticks  | stmt                                                                                                                   |
> +========+========================================================================================================================+
> |     10 | _12:bat[:oid,:oid] <tmp_5375>[0] := sql.bind_dbat("sys","kvk",1);                                                      |
> |     40 | _34:bat[:oid,:lng] <tmp_6422>[1196533] := sql.bind("sys","kvk","kvk",0,1196532 at 0,nil:oid);                             |
> |      9 | _13<tmpr_5375>[0] := bat.reverse(_12=<tmp_5375>:bat[:oid,:oid][0]);                                                    |
> |      8 | _6:bat[:oid,:lng] <tmp_5406>[0] := sql.bind("sys","kvk","kvk",2);                                                      |
> |      6 | _12:bat[:oid,:oid]  := nil:BAT;                                                                                        |
> |     20 | _63<tmp_6376>[1196533] := algebra.kdifference(_34=<tmp_6422>:bat[:oid,:lng][1196533],_6=<tmp_5406>:bat[:oid,:lng][0]); |
> |     23 | _65<tmpr_6412>[0] := algebra.semijoin(_6=<tmp_5406>:bat[:oid,:lng][0],_34=<tmp_6422>:bat[:oid,:lng][1196533]);         |
> |     16 | _67<tmp_6373>[1196533] := algebra.kunion(_63=<tmp_6376>[1196533],_65=<tmpr_6412>[0]);                                  |
> |     10 | _63 := nil:BAT;                                                                                                        |
> |     11 | _65 := nil:BAT;                                                                                                        |
> |     10 | _9:bat[:oid,:lng] <tmp_5407>[0] := sql.bind("sys","kvk","kvk",1);                                                      |
> |     21 | _11<tmp_6376>[0] := algebra.uselect(_9=<tmp_5407>:bat[:oid,:lng][0],A0=412657690010:lng);                              |
> |     10 | _45<tmp_6412>[0] := algebra.kdifference(_11=<tmp_6376>[0],_13=<tmpr_5375>[0]);                                         |
> |      7 | _11 := nil:BAT;                                                                                                        |
> |      9 | _52<tmp_6376>[0] := algebra.markT(_45=<tmp_6412>[0],3,2);                                                              |
> |      6 | _45 := nil:BAT;                                                                                                        |
> |      7 | _57<tmpr_6376>[0] := bat.reverse(_52=<tmp_6376>[0]);                                                                   |
> |      5 | _52 := nil:BAT;                                                                                                        |
> |     18 | _71<tmp_6325>[0] := algebra.leftjoin(_57=<tmpr_6376>[0],_9=<tmp_5407>:bat[:oid,:lng][0]);                              |
> |     12 | _57 := nil:BAT;                                                                                                        |
> |      6 | _9:bat[:oid,:lng]  := nil:BAT;                                                                                         |
> |     33 | _8<tmp_6412>[0] := algebra.uselect(_6=<tmp_5406>:bat[:oid,:lng][0],A0=412657690010:lng);                               |
> |     18 | _40<tmpr_6376>[0] := algebra.semijoin(_8=<tmp_6412>[0],_34=<tmp_6422>:bat[:oid,:lng][1196533]);                        |
> |     18 | _32:bat[:oid,:lng] <tmp_6351>[1196532] := sql.bind("sys","kvk","kvk",0,0 at 0,1196532 at 0);                                 |
> |     14 | _60<tmp_6347>[1196532] := algebra.kdifference(_32=<tmp_6351>:bat[:oid,:lng][1196532],_6=<tmp_5406>:bat[:oid,:lng][0]); |
> |     15 | _64<tmpr_6354>[0] := algebra.semijoin(_6=<tmp_5406>:bat[:oid,:lng][0],_32=<tmp_6351>:bat[:oid,:lng][1196532]);         |
> |     15 | _66<tmp_6147>[1196532] := algebra.kunion(_60=<tmp_6347>[1196532],_64=<tmpr_6354>[0]);                                  |
> |     11 | _60 := nil:BAT;                                                                                                        |
> |      9 | _64 := nil:BAT;                                                                                                        |
> |     13 | _39<tmpr_6354>[0] := algebra.semijoin(_8=<tmp_6412>[0],_32=<tmp_6351>:bat[:oid,:lng][1196532]);                        |
> |      9 | _8 := nil:BAT;                                                                                                         |
> | 350476 | _36<tmp_5711>[1] := algebra.uselect(_34=<tmp_6422>:bat[:oid,:lng][1196533],A0=412657690010:lng);                       |
> |  21359 | _34:bat[:oid,:lng]  := nil:BAT;                                                                                        |
> |     37 | _38<tmp_6422>[1] := algebra.kdifference(_36=<tmp_5711>[1],_6=<tmp_5406>:bat[:oid,:lng][0]);                            |
> |     17 | _42<tmp_6412>[1] := algebra.kunion(_38=<tmp_6422>[1],_40=<tmpr_6376>[0]);                                              |
> |      6 | _36 := nil:BAT;                                                                                                        |
> |     15 | _40 := nil:BAT;                                                                                                        |
> |     13 | _44<tmp_6376>[1] := algebra.kdifference(_42=<tmp_6412>[1],_13=<tmpr_5375>[0]);                                         |
> |     11 | _38 := nil:BAT;                                                                                                        |
> |     16 | _49<tmp_6422>[1] := algebra.markT(_44=<tmp_6376>[1],3,1);                                                              |
> |     10 | _42 := nil:BAT;                                                                                                        |
> |      9 | _56<tmpr_6422>[1] := bat.reverse(_49=<tmp_6422>[1]);                                                                   |
> |      9 | _44 := nil:BAT;                                                                                                        |
> |    176 | _70<tmp_6124>[1] := algebra.leftjoin(_56=<tmpr_6422>[1],_67=<tmp_6373>[1196533]);                                      |
> |      8 | _49 := nil:BAT;                                                                                                        |
> |     11 | _67 := nil:BAT;                                                                                                        |
> |     14 | _56 := nil:BAT;                                                                                                        |
> | 385653 | _35<tmp_6264>[0] := algebra.uselect(_32=<tmp_6351>:bat[:oid,:lng][1196532],A0=412657690010:lng);                       |
> |     33 | _37<tmp_6422>[0] := algebra.kdifference(_35=<tmp_6264>[0],_6=<tmp_5406>:bat[:oid,:lng][0]);                            |
> |      8 | _6:bat[:oid,:lng]  := nil:BAT;                                                                                         |
> |     17 | _41<tmp_6373>[0] := algebra.kunion(_37=<tmp_6422>[0],_39=<tmpr_6354>[0]);                                              |
> |     20 | _39 := nil:BAT;                                                                                                        |
> |     13 | _43<tmp_6354>[0] := algebra.kdifference(_41=<tmp_6373>[0],_13=<tmpr_5375>[0]);                                         |
> |      9 | _41 := nil:BAT;                                                                                                        |
> |     15 | _46<tmp_6373>[0] := algebra.markT(_43=<tmp_6354>[0],3,0);                                                              |
> |      8 | _43 := nil:BAT;                                                                                                        |
> |  21008 | _32:bat[:oid,:lng]  := nil:BAT;                                                                                        |
> |     13 | _55<tmpr_6373>[0] := bat.reverse(_46=<tmp_6373>[0]);                                                                   |
> |     18 | _37 := nil:BAT;                                                                                                        |
> |      7 | _46 := nil:BAT;                                                                                                        |
> |      8 | _13 := nil:BAT;                                                                                                        |
> |     24 | _68<tmp_6151>[0] := algebra.leftjoin(_55=<tmpr_6373>[0],_66=<tmp_6147>[1196532]);                                      |
> |     11 | _35 := nil:BAT;                                                                                                        |
> |     24 | _14<tmp_6241>[1] := mat.pack(_68=<tmp_6151>[0],_70=<tmp_6124>[1],_71=<tmp_6325>[0]);                                   |
> |     12 | _66 := nil:BAT;                                                                                                        |
> |     11 | _70 := nil:BAT;                                                                                                        |
> |      9 | _68 := nil:BAT;                                                                                                        |
> |      7 | _71 := nil:BAT;                                                                                                        |
> |     12 | _55 := nil:BAT;                                                                                                        |
> | 414894 | barrier _109 := language.dataflow();                                                                                   |
> |     13 | _15 := sql.resultSet(1,1,_14=<tmp_6241>[1]);                                                                           |
> |     12 | sql.rsColumn(_15=5,"sys.kvk","kvk","bigint",64,0,_14=<tmp_6241>[1]);                                                   |
> |     25 | _14 := nil:BAT;                                                                                                        |
> |      6 | _20 := io.stdout();                                                                                                    |
> |     37 | sql.exportResult(_20=24201696,_15=5);                                                                                  |
> |     13 | end s5_1;                                                                                                              |
> | 415505 | user.s5_1(412657690010:lng);                                                                                           |
> +--------+------------------------------------------------------------------------------------------------------------------------+
> 
> sql>TRACE select avg(kvk) from kvk;
> +------------------------+
> | L10                    |
> +========================+
> |     180371715703.39435 |
> +------------------------+
> 1 tuple
> +--------+------------------------------------------------------------------------------------------------------------------------+
> | ticks  | stmt                                                                                                                   |
> +========+========================================================================================================================+
> |     40 | _36:bat[:oid,:lng] <tmp_6147>[1196532] := sql.bind("sys","kvk","kvk",0,0 at 0,1196532 at 0);                                 |
> |      8 | _9:bat[:oid,:oid] <tmp_5375>[0] := sql.bind_dbat("sys","kvk",1);                                                       |
> |     26 | _5:bat[:oid,:lng] <tmp_5406>[0] := sql.bind("sys","kvk","kvk",2);                                                      |
> |      8 | _7:bat[:oid,:lng] <tmp_5407>[0] := sql.bind("sys","kvk","kvk",1);                                                      |
> |     11 | _10<tmpr_5375>[0] := bat.reverse(_9=<tmp_5375>:bat[:oid,:oid][0]);                                                     |
> |     18 | _38:bat[:oid,:lng] <tmp_6373>[1196533] := sql.bind("sys","kvk","kvk",0,1196532 at 0,nil:oid);                             |
> |     58 | _41<tmpr_6422>[0] := algebra.semijoin(_5=<tmp_5406>:bat[:oid,:lng][0],_36=<tmp_6147>:bat[:oid,:lng][1196532]);         |
> |     26 | _39<tmp_6351>[1196532] := algebra.kdifference(_36=<tmp_6147>:bat[:oid,:lng][1196532],_5=<tmp_5406>:bat[:oid,:lng][0]); |
> |     29 | _40<tmp_6354>[1196533] := algebra.kdifference(_38=<tmp_6373>:bat[:oid,:lng][1196533],_5=<tmp_5406>:bat[:oid,:lng][0]); |
> |     10 | _9:bat[:oid,:oid]  := nil:BAT;                                                                                         |
> |     27 | _43<tmp_6376>[1196532] := algebra.kunion(_39=<tmp_6351>[1196532],_41=<tmpr_6422>[0]);                                  |
> |     22 | _36:bat[:oid,:lng]  := nil:BAT;                                                                                        |
> |     34 | _47<tmp_6147>[0] := algebra.kdifference(_7=<tmp_5407>:bat[:oid,:lng][0],_10=<tmpr_5375>[0]);                           |
> |     36 | _42<tmpr_6412>[0] := algebra.semijoin(_5=<tmp_5406>:bat[:oid,:lng][0],_38=<tmp_6373>:bat[:oid,:lng][1196533]);         |
> |     24 | _41 := nil:BAT;                                                                                                        |
> |     11 | _39 := nil:BAT;                                                                                                        |
> |      9 | _5:bat[:oid,:lng]  := nil:BAT;                                                                                         |
> |     16 | _45<tmp_6351>[1196532] := algebra.kdifference(_43=<tmp_6376>[1196532],_10=<tmpr_5375>[0]);                             |
> |     21 | _44<tmp_6422>[1196533] := algebra.kunion(_40=<tmp_6354>[1196533],_42=<tmpr_6412>[0]);                                  |
> |     12 | _38:bat[:oid,:lng]  := nil:BAT;                                                                                        |
> |     10 | _43 := nil:BAT;                                                                                                        |
> |     13 | _46<tmp_6376>[1196533] := algebra.kdifference(_44=<tmp_6422>[1196533],_10=<tmpr_5375>[0]);                             |
> |     12 | _42 := nil:BAT;                                                                                                        |
> |     10 | _40 := nil:BAT;                                                                                                        |
> |      6 | _7:bat[:oid,:lng]  := nil:BAT;                                                                                         |
> | 124025 | _49<tmp_6412>[1196533] := batcalc.dbl(_46=<tmp_6376>[1196533]);                                                        |
> |     24 | _46 := nil:BAT;                                                                                                        |
> |      8 | _52<tmp_6412>[1196533] := algebra.selectNotNil(_49=<tmp_6412>[1196533]);                                               |
> |      5 | _49 := nil:BAT;                                                                                                        |
> | 143744 | _48<tmp_6354>[1196532] := batcalc.dbl(_45=<tmp_6351>[1196532]);                                                        |
> |     18 | _45 := nil:BAT;                                                                                                        |
> |      7 | _51<tmp_6354>[1196532] := algebra.selectNotNil(_48=<tmp_6354>[1196532]);                                               |
> |      5 | _48 := nil:BAT;                                                                                                        |
> |   8902 | return sum := aggr.sum(b=<tmp_6412>[1196533],true);                                                                    |
> |  37847 | _56 := aggr.sum(_52=<tmp_6412>[1196533]);                                                                              |
> |     19 | _50<tmp_6241>[0] := batcalc.dbl(_47=<tmp_6147>[0]);                                                                    |
> |     22 | _47 := nil:BAT;                                                                                                        |
> |      7 | _53<tmp_6241>[0] := algebra.selectNotNil(_50=<tmp_6241>[0]);                                                           |
> |      5 | _50 := nil:BAT;                                                                                                        |
> |      9 | return sum := aggr.sum(b=<tmp_6241>[0],true);                                                                          |
> |     73 | _57 := aggr.sum(_53=<tmp_6241>[0]);                                                                                    |
> |      6 | _64 := aggr.count(_51=<tmp_6354>[1196532]);                                                                            |
> |      5 | _65 := aggr.count(_52=<tmp_6412>[1196533]);                                                                            |
> |   8776 | return sum := aggr.sum(b=<tmp_6354>[1196532],true);                                                                    |
> |  33809 | _55 := aggr.sum(_51=<tmp_6354>[1196532]);                                                                              |
> |     15 | _54<tmp_6325>[3] := mat.pack(_55=1.396432356977703e+17,_56=2.9199800414197306e+17,_57=nil);                            |
> |  14669 | _52 := nil:BAT;                                                                                                        |
> |   6346 | _58<tmp_6151>[2] := algebra.selectNotNil(_54=<tmp_6325>[3]);                                                           |
> |     10 | _54 := nil:BAT;                                                                                                        |
> |     11 | return sum := aggr.sum(b=<tmp_6151>[2],true);                                                                          |
> |     80 | _11:dbl  := aggr.sum(_58=<tmp_6151>[2]);                                                                               |
> |      6 | _58 := nil:BAT;                                                                                                        |
> |      6 | _66 := aggr.count(_53=<tmp_6241>[0]);                                                                                  |
> |      6 | _53 := nil:BAT;                                                                                                        |
> |     10 | _63<tmp_6241>[3] := mat.pack(_64=1196532:wrd,_65=1196533:wrd,_66=0:wrd);                                               |
> |      6 | _67<tmp_6241>[3] := algebra.selectNotNil(_63=<tmp_6241>[3]);                                                           |
> |      6 | _63 := nil:BAT;                                                                                                        |
> |   5077 | return sum := aggr.sum(b=<tmp_6241>[3],true);                                                                          |
> |   5203 | _12 := aggr.sum(_67=<tmp_6241>[3]);                                                                                    |
> |     10 | _67 := nil:BAT;                                                                                                        |
> |  12456 | _51 := nil:BAT;                                                                                                        |
> |     17 | _14 := calc.dbl(_12=2393065:wrd);                                                                                      |
> |      6 | _10 := nil:BAT;                                                                                                        |
> |     14 | _44 := nil:BAT;                                                                                                        |
> |   9195 | _13 := calc.==(_12=2393065:wrd,0:wrd);                                                                                 |
> |     21 | _15 := calc.ifthenelse(_13=false,nil,_14=2393065);                                                                     |
> |     12 | _16 := calc./(_11=4.3164123983974336e+17:dbl,_15=2393065);                                                             |
> | 204610 | barrier _94 := language.dataflow();                                                                                    |
> |     21 | sql.exportValue(1,"sys.","L10","double",53,0,9,_16=180371715703.39435,"");                                             |
> |     12 | end s6_1;                                                                                                              |
> | 204854 | user.s6_1();                                                                                                           |
> +--------+------------------------------------------------------------------------------------------------------------------------+
> 71 tuples

> ------------------------------------------------------------------------------
> Download Intel® Parallel Studio Eval
> Try the new software tools for yourself. Speed compiling, find bugs
> proactively, and fine-tune applications for parallel performance.
> See why Intel Parallel Studio got high marks during beta.
> http://p.sf.net/sfu/intel-sw-dev

> _______________________________________________
> Monetdb-developers mailing list
> Monetdb-developers at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/monetdb-developers


-- 
| Dr. Stefan Manegold | mailto:Stefan.Manegold at cwi.nl |
| CWI,  P.O.Box 94079 | http://www.cwi.nl/~manegold/  |
| 1090 GB Amsterdam   | Tel.: +31 (20) 592-4212       |
| The Netherlands     | Fax : +31 (20) 592-4199       |




More information about the developers-list mailing list