Re: [Monetdb-developers] Single column select vs aggregation

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] [1196533] := sql.bind("sys","kvk","kvk",0,1196532@0,nil:oid); | | 7 | _11:bat[:oid,:oid] [0] := sql.bind_dbat("sys","kvk",1); | | 7 | _5:bat[:oid,:lng] [0] := sql.bind("sys","kvk","kvk",2); | | 12 | _31:bat[:oid,:lng] [1196532] := sql.bind("sys","kvk","kvk",0,0@0,1196532@0); | | 9 | _35[1196533] := algebra.selectNotNil(_33=:bat[:oid,:lng][1196533]); | | 7 | _12[0] := bat.reverse(_11=:bat[:oid,:oid][0]); | | 6 | _7[0] := algebra.selectNotNil(_5=:bat[:oid,:lng][0]); | | 5 | _34[1196532] := algebra.selectNotNil(_31=:bat[:oid,:lng][1196532]); | | 19 | _37[1196533] := algebra.kdifference(_35=[1196533],_5=:bat[:oid,:lng][0]); | | 6 | _11:bat[:oid,:oid] := nil:BAT; | | 24 | _38[0] := algebra.semijoin(_7=[0],_31=:bat[:oid,:lng][1196532]); | | 12 | _36[1196532] := algebra.kdifference(_34=[1196532],_5=:bat[:oid,:lng][0]); | | 5 | _35 := nil:BAT; | | 15 | _40[1196532] := algebra.kunion(_36=[1196532],_38=[0]); | | 4 | _31:bat[:oid,:lng] := nil:BAT; | | 17 | _34 := nil:BAT; | | 5 | _5:bat[:oid,:lng] := nil:BAT; | | 11 | _42[1196532] := algebra.kdifference(_40=[1196532],_12=[0]); | | 9 | _38 := nil:BAT; | | 8 | _36 := nil:BAT; | | 6 | _8:bat[:oid,:lng] [0] := sql.bind("sys","kvk","kvk",1); | | 13 | _39[0] := algebra.semijoin(_7=[0],_33=:bat[:oid,:lng][1196533]); | | 18 | _33:bat[:oid,:lng] := nil:BAT; | | 5 | _7 := nil:BAT; | | 8 | _40 := nil:BAT; | | 7 | _10[0] := algebra.selectNotNil(_8=:bat[:oid,:lng][0]); | | 23 | _41[1196533] := algebra.kunion(_37=[1196533],_39=[0]); | | 12 | _43[1196533] := algebra.kdifference(_41=[1196533],_12=[0]); | | 9 | _39 := nil:BAT; | | 8 | _37 := nil:BAT; | | 5 | _8:bat[:oid,:lng] := nil:BAT; | | 12528 | _46 := aggr.max(_42=[1196532]); | | 25 | _41 := nil:BAT; | | 15285 | _47 := aggr.max(_43=[1196533]); | | 26 | _43 := nil:BAT; | | 117 | _44[0] := algebra.kdifference(_10=[0],_12=[0]); | | 12 | _42 := nil:BAT; | | 10 | _48 := aggr.max(_44=[0]); | | 6 | _12 := nil:BAT; | | 6 | _10 := nil:BAT; | | 15 | _45[3] := mat.pack(_46=243753400000:lng,_47=412657690010:lng,_48=nil:lng); | | 13 | _44 := nil:BAT; | | 27 | _49[2] := algebra.selectNotNil(_45=[3]); | | 13 | _45 := nil:BAT; | | 53 | _13 := aggr.max(_49=[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] [0] := sql.bind_dbat("sys","kvk",1); | | 40 | _34:bat[:oid,:lng] [1196533] := sql.bind("sys","kvk","kvk",0,1196532@0,nil:oid); | | 9 | _13[0] := bat.reverse(_12=:bat[:oid,:oid][0]); | | 8 | _6:bat[:oid,:lng] [0] := sql.bind("sys","kvk","kvk",2); | | 6 | _12:bat[:oid,:oid] := nil:BAT; | | 20 | _63[1196533] := algebra.kdifference(_34=:bat[:oid,:lng][1196533],_6=:bat[:oid,:lng][0]); | | 23 | _65[0] := algebra.semijoin(_6=:bat[:oid,:lng][0],_34=:bat[:oid,:lng][1196533]); | | 16 | _67[1196533] := algebra.kunion(_63=[1196533],_65=[0]); | | 10 | _63 := nil:BAT; | | 11 | _65 := nil:BAT; | | 10 | _9:bat[:oid,:lng] [0] := sql.bind("sys","kvk","kvk",1); | | 21 | _11[0] := algebra.uselect(_9=:bat[:oid,:lng][0],A0=412657690010:lng); | | 10 | _45[0] := algebra.kdifference(_11=[0],_13=[0]); | | 7 | _11 := nil:BAT; | | 9 | _52[0] := algebra.markT(_45=[0],3,2); | | 6 | _45 := nil:BAT; | | 7 | _57[0] := bat.reverse(_52=[0]); | | 5 | _52 := nil:BAT; | | 18 | _71[0] := algebra.leftjoin(_57=[0],_9=:bat[:oid,:lng][0]); | | 12 | _57 := nil:BAT; | | 6 | _9:bat[:oid,:lng] := nil:BAT; | | 33 | _8[0] := algebra.uselect(_6=:bat[:oid,:lng][0],A0=412657690010:lng); | | 18 | _40[0] := algebra.semijoin(_8=[0],_34=:bat[:oid,:lng][1196533]); | | 18 | _32:bat[:oid,:lng] [1196532] := sql.bind("sys","kvk","kvk",0,0@0,1196532@0); | | 14 | _60[1196532] := algebra.kdifference(_32=:bat[:oid,:lng][1196532],_6=:bat[:oid,:lng][0]); | | 15 | _64[0] := algebra.semijoin(_6=:bat[:oid,:lng][0],_32=:bat[:oid,:lng][1196532]); | | 15 | _66[1196532] := algebra.kunion(_60=[1196532],_64=[0]); | | 11 | _60 := nil:BAT; | | 9 | _64 := nil:BAT; | | 13 | _39[0] := algebra.semijoin(_8=[0],_32=:bat[:oid,:lng][1196532]); | | 9 | _8 := nil:BAT; | | 350476 | _36[1] := algebra.uselect(_34=:bat[:oid,:lng][1196533],A0=412657690010:lng); | | 21359 | _34:bat[:oid,:lng] := nil:BAT; | | 37 | _38[1] := algebra.kdifference(_36=[1],_6=:bat[:oid,:lng][0]); | | 17 | _42[1] := algebra.kunion(_38=[1],_40=[0]); | | 6 | _36 := nil:BAT; | | 15 | _40 := nil:BAT; | | 13 | _44[1] := algebra.kdifference(_42=[1],_13=[0]); | | 11 | _38 := nil:BAT; | | 16 | _49[1] := algebra.markT(_44=[1],3,1); | | 10 | _42 := nil:BAT; | | 9 | _56[1] := bat.reverse(_49=[1]); | | 9 | _44 := nil:BAT; | | 176 | _70[1] := algebra.leftjoin(_56=[1],_67=[1196533]); | | 8 | _49 := nil:BAT; | | 11 | _67 := nil:BAT; | | 14 | _56 := nil:BAT; | | 385653 | _35[0] := algebra.uselect(_32=:bat[:oid,:lng][1196532],A0=412657690010:lng); | | 33 | _37[0] := algebra.kdifference(_35=[0],_6=:bat[:oid,:lng][0]); | | 8 | _6:bat[:oid,:lng] := nil:BAT; | | 17 | _41[0] := algebra.kunion(_37=[0],_39=[0]); | | 20 | _39 := nil:BAT; | | 13 | _43[0] := algebra.kdifference(_41=[0],_13=[0]); | | 9 | _41 := nil:BAT; | | 15 | _46[0] := algebra.markT(_43=[0],3,0); | | 8 | _43 := nil:BAT; | | 21008 | _32:bat[:oid,:lng] := nil:BAT; | | 13 | _55[0] := bat.reverse(_46=[0]); | | 18 | _37 := nil:BAT; | | 7 | _46 := nil:BAT; | | 8 | _13 := nil:BAT; | | 24 | _68[0] := algebra.leftjoin(_55=[0],_66=[1196532]); | | 11 | _35 := nil:BAT; | | 24 | _14[1] := mat.pack(_68=[0],_70=[1],_71=[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=[1]); | | 12 | sql.rsColumn(_15=5,"sys.kvk","kvk","bigint",64,0,_14=[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] [1196532] := sql.bind("sys","kvk","kvk",0,0@0,1196532@0); | | 8 | _9:bat[:oid,:oid] [0] := sql.bind_dbat("sys","kvk",1); | | 26 | _5:bat[:oid,:lng] [0] := sql.bind("sys","kvk","kvk",2); | | 8 | _7:bat[:oid,:lng] [0] := sql.bind("sys","kvk","kvk",1); | | 11 | _10[0] := bat.reverse(_9=:bat[:oid,:oid][0]); | | 18 | _38:bat[:oid,:lng] [1196533] := sql.bind("sys","kvk","kvk",0,1196532@0,nil:oid); | | 58 | _41[0] := algebra.semijoin(_5=:bat[:oid,:lng][0],_36=:bat[:oid,:lng][1196532]); | | 26 | _39[1196532] := algebra.kdifference(_36=:bat[:oid,:lng][1196532],_5=:bat[:oid,:lng][0]); | | 29 | _40[1196533] := algebra.kdifference(_38=:bat[:oid,:lng][1196533],_5=:bat[:oid,:lng][0]); | | 10 | _9:bat[:oid,:oid] := nil:BAT; | | 27 | _43[1196532] := algebra.kunion(_39=[1196532],_41=[0]); | | 22 | _36:bat[:oid,:lng] := nil:BAT; | | 34 | _47[0] := algebra.kdifference(_7=:bat[:oid,:lng][0],_10=[0]); | | 36 | _42[0] := algebra.semijoin(_5=:bat[:oid,:lng][0],_38=:bat[:oid,:lng][1196533]); | | 24 | _41 := nil:BAT; | | 11 | _39 := nil:BAT; | | 9 | _5:bat[:oid,:lng] := nil:BAT; | | 16 | _45[1196532] := algebra.kdifference(_43=[1196532],_10=[0]); | | 21 | _44[1196533] := algebra.kunion(_40=[1196533],_42=[0]); | | 12 | _38:bat[:oid,:lng] := nil:BAT; | | 10 | _43 := nil:BAT; | | 13 | _46[1196533] := algebra.kdifference(_44=[1196533],_10=[0]); | | 12 | _42 := nil:BAT; | | 10 | _40 := nil:BAT; | | 6 | _7:bat[:oid,:lng] := nil:BAT; | | 124025 | _49[1196533] := batcalc.dbl(_46=[1196533]); | | 24 | _46 := nil:BAT; | | 8 | _52[1196533] := algebra.selectNotNil(_49=[1196533]); | | 5 | _49 := nil:BAT; | | 143744 | _48[1196532] := batcalc.dbl(_45=[1196532]); | | 18 | _45 := nil:BAT; | | 7 | _51[1196532] := algebra.selectNotNil(_48=[1196532]); | | 5 | _48 := nil:BAT; | | 8902 | return sum := aggr.sum(b=[1196533],true); | | 37847 | _56 := aggr.sum(_52=[1196533]); | | 19 | _50[0] := batcalc.dbl(_47=[0]); | | 22 | _47 := nil:BAT; | | 7 | _53[0] := algebra.selectNotNil(_50=[0]); | | 5 | _50 := nil:BAT; | | 9 | return sum := aggr.sum(b=[0],true); | | 73 | _57 := aggr.sum(_53=[0]); | | 6 | _64 := aggr.count(_51=[1196532]); | | 5 | _65 := aggr.count(_52=[1196533]); | | 8776 | return sum := aggr.sum(b=[1196532],true); | | 33809 | _55 := aggr.sum(_51=[1196532]); | | 15 | _54[3] := mat.pack(_55=1.396432356977703e+17,_56=2.9199800414197306e+17,_57=nil); | | 14669 | _52 := nil:BAT; | | 6346 | _58[2] := algebra.selectNotNil(_54=[3]); | | 10 | _54 := nil:BAT; | | 11 | return sum := aggr.sum(b=[2],true); | | 80 | _11:dbl := aggr.sum(_58=[2]); | | 6 | _58 := nil:BAT; | | 6 | _66 := aggr.count(_53=[0]); | | 6 | _53 := nil:BAT; | | 10 | _63[3] := mat.pack(_64=1196532:wrd,_65=1196533:wrd,_66=0:wrd); | | 6 | _67[3] := algebra.selectNotNil(_63=[3]); | | 6 | _63 := nil:BAT; | | 5077 | return sum := aggr.sum(b=[3],true); | | 5203 | _12 := aggr.sum(_67=[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 |