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.
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 Monetdb-developers@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-developers
