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@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,1196532@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@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,1196532@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,1196532@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@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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-developers
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@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 |