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,1196...@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...@0,1196...@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,1196...@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...@0,1196...@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...@0,1196...@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,1196...@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
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/monetdb-developers
--
| Dr. Stefan Manegold | mailto:[email protected] |
| 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 |
------------------------------------------------------------------------------
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-developers
