Re: Costly bat.new/bat.append not optimized out

Fredrik, I think that you are correct, the pattern can be optimized away. Actually many of the queries I usually deal with would benefit from it. Sometime certain optimizations are not used or limited on purpose, because of performance reasons (the time spent in optimization steals from query time). I don't think this would be particularly costly, but it's up to the developers to decide about it (I'm not one of them). Best, Roberto Ps. It's actually not very difficult to write it yourself if you feel like it. There is a template in the code. On 22 Aug 2014 22:27, "Frédéric Jolliton" < frederic.jolliton+monetdb@securactive.net> wrote:

Roberto Cornacchia writes:

...

Sorry, I mixed up the names. What i meant is that X_3 seems the result container created by the union, and X_19 seems the column of a (or a view a view of it). If X_19 were really the column of a, then you could not reuse it. Most probably though X_19 is the result of the original column and the delta bats (updates,deletions). In that case i guess that yes, it could be reused as you say, but the pattern is not detected by any optimizer. I'd advise you to post a bit more complete MAL subplan, then it will be easier for the developers to help you.

Ok sorry, I don't know people here.

Here is a detailed scenario:

1. I create three tables "a", "b" and "c", each containing 11M rows. (I've not tried to find the best set / best configuration that emphasize this problem.)

sql>\d a CREATE TABLE "sys"."a" ( "name" CHARACTER LARGE OBJECT, "value" BIGINT ); sql>select count(*) from a; +----------+ | L1 | +==========+ | 11217990 | +----------+ 1 tuple (0.553ms)

Same for "b" and "c" (same content actually.)

2. I create a view that concatenate all the tables.

CREATE VIEW my_view AS (SELECT * FROM a UNION ALL SELECT * FROM b UNION ALL SELECT * FROM c);

3. I perform the following query. See the output below.

TRACE SELECT name, avg(value) FROM my_view GROUP BY name;

Note: there is only one name actually in the table, so the GROUP BY gives only one resulting row.

4. From a tool I made, I also check the resulting graph to figure where the time is spent. The red box near the middle shows the operation that is the most onerous (yellow = almost no time, red = highest cost, blue are intermediate results). A lot easier to read that the raw trace output (assuming my tool is not broken.)

See http://tuxee.net/monetdb-prof.svg

Here is the output of EXPLAIN (easier to read), before showing TRACE:

sql>\f raw sql>explain SELECT name, avg(value) FROM my_view GROUP BY name; % .explain # table_name % mal # name % clob # type % 95 # length function user.s2_2{autoCommit=true}():void; X_3:bat[:oid,:str] := bat.new(nil:oid,nil:str); X_6:bat[:oid,:str] := bat.new(nil:oid,nil:str); X_2 := sql.mvc(); X_7:bat[:oid,:oid] := sql.tid(X_2,"sys","a"); X_10 := sql.bind(X_2,"sys","a","name",0); (X_13,r1_13) := sql.bind(X_2,"sys","a","name",2); X_16 := sql.bind(X_2,"sys","a","name",1); X_18 := sql.delta(X_10,X_13,r1_13,X_16); X_19 := algebra.leftfetchjoin(X_7,X_18); X_20 := bat.append(X_6,X_19,true); X_21:bat[:oid,:oid] := sql.tid(X_2,"sys","b"); X_23 := sql.bind(X_2,"sys","b","name",0); (X_24,r1_24) := sql.bind(X_2,"sys","b","name",2); X_26 := sql.bind(X_2,"sys","b","name",1); X_27 := sql.delta(X_23,X_24,r1_24,X_26); X_28 := algebra.leftfetchjoin(X_21,X_27); X_29 := bat.append(X_20,X_28,true); X_30 := bat.append(X_3,X_29,true); X_31:bat[:oid,:oid] := sql.tid(X_2,"sys","c"); X_33 := sql.bind(X_2,"sys","c","name",0); (X_34,r1_34) := sql.bind(X_2,"sys","c","name",2); X_36 := sql.bind(X_2,"sys","c","name",1); X_37 := sql.delta(X_33,X_34,r1_34,X_36); X_38 := algebra.leftfetchjoin(X_31,X_37); X_39 := bat.append(X_30,X_38,true); (X_41,r1_41,r2_41) := group.subgroupdone(X_39); X_44 := algebra.leftfetchjoin(r1_41,X_39); X_45:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); X_48:bat[:oid,:lng] := bat.new(nil:oid,nil:lng); X_49 := sql.bind(X_2,"sys","a","value",0); (X_54,r1_54) := sql.bind(X_2,"sys","a","value",2); X_57 := sql.bind(X_2,"sys","a","value",1); X_59 := sql.delta(X_49,X_54,r1_54,X_57); X_60 := algebra.leftfetchjoin(X_7,X_59); X_61 := bat.append(X_48,X_60,true); X_62 := sql.bind(X_2,"sys","b","value",0); (X_64,r1_64) := sql.bind(X_2,"sys","b","value",2); X_66 := sql.bind(X_2,"sys","b","value",1); X_67 := sql.delta(X_62,X_64,r1_64,X_66); X_68 := algebra.leftfetchjoin(X_21,X_67); X_69 := bat.append(X_61,X_68,true); X_70 := bat.append(X_45,X_69,true); X_71 := sql.bind(X_2,"sys","c","value",0); (X_73,r1_73) := sql.bind(X_2,"sys","c","value",2); X_75 := sql.bind(X_2,"sys","c","value",1); X_76 := sql.delta(X_71,X_73,r1_73,X_75); X_77 := algebra.leftfetchjoin(X_31,X_76); X_78 := bat.append(X_70,X_77,true); X_80 := batcalc.dbl(X_78); X_81:bat[:oid,:dbl] := aggr.subavg(X_80,X_41,r1_41,true,true); X_82 := sql.resultSet(2,1,X_44);

sql.rsColumn(X_82,".my_view","name","clob",0,0,X_44);

sql.rsColumn(X_82,".L1","L1","double",53,0,X_81); X_93 := io.stdout(); sql.exportResult(X_93,X_82); end s2_2; # querylog.define("explain select name, avg(value) from my_view group by name;","default_pipe")

And the output of TRACE:

sql>\f tab sql>TRACE SELECT name, avg(value) FROM my_view GROUP BY name; foobar 509350.54538192693 30 X_3:bat[:oid,:str] =[0] := bat.new(nil:oid,nil:str); 13 X_6:bat[:oid,:str] =[0] := bat.new(nil:oid,nil:str); 4 X_2 := sql.mvc(); 15 "X_7:bat[:oid,:oid] =[11217990] := sql.tid(X_2=0,""sys"",""a"");" 9 "X_10=[11217990] := sql.bind(X_2=0,""sys"",""a"",""name"",0);" 11 "(X_13=[0],r1_13=[0]) := sql.bind(X_2=0,""sys"",""a"",""name"",2);" 6 "X_16=[0] := sql.bind(X_2=0,""sys"",""a"",""name"",1);" 4 X_18=[11217990] := sql.delta(X_10=[11217990],X_13=[0],r1_13=[0],X_16=[0]); 21 X_19=[11217990] := algebra.leftfetchjoin(X_7=:bat[:oid,:oid][11217990],X_18=[11217990]); 2427642 X_20=[11217990] := bat.append(X_6=:bat[:oid,:str][11217990],X_19=[11217990],true); 27 "X_21:bat[:oid,:oid] =[11217990] := sql.tid(X_2=0,""sys"",""b"");" 10 "X_23=[11217990] := sql.bind(X_2=0,""sys"",""b"",""name"",0);" 9 "(X_24=[0],r1_24=[0]) := sql.bind(X_2=0,""sys"",""b"",""name"",2);" 6 "X_26=[0] := sql.bind(X_2=0,""sys"",""b"",""name"",1);" 3 X_27=[11217990] := sql.delta(X_23=[11217990],X_24=[0],r1_24=[0],X_26=[0]); 22 X_28=[11217990] := algebra.leftfetchjoin(X_21=:bat[:oid,:oid][11217990],X_27=[11217990]); 2512634 X_29=[22435980] := bat.append(X_20=[22435980],X_28=[11217990],true); 4840265 X_30=[22435980] := bat.append(X_3=:bat[:oid,:str][22435980],X_29=[22435980],true); 30 "X_31:bat[:oid,:oid] =[11217990] := sql.tid(X_2=0,""sys"",""c"");" 12 "X_33=[11217990] := sql.bind(X_2=0,""sys"",""c"",""name"",0);" 10 "(X_34=[0],r1_34=[0]) := sql.bind(X_2=0,""sys"",""c"",""name"",2);" 5 "X_36=[0] := sql.bind(X_2=0,""sys"",""c"",""name"",1);" 3 X_37=[11217990] := sql.delta(X_33=[11217990],X_34=[0],r1_34=[0],X_36=[0]); 20 X_38=[11217990] := algebra.leftfetchjoin(X_31=:bat[:oid,:oid][11217990],X_37=[11217990]); 2604326 X_39=[33653970] := bat.append(X_30=[33653970],X_38=[11217990],true); 533287 (X_41=[33653970],r1_41=[1],r2_41=[1]) := group.subgroupdone(X_39=[33653970]); 30 X_44=[1] := algebra.leftfetchjoin(r1_41=[1],X_39=[33653970]); 13 X_45:bat[:oid,:lng] =[0] := bat.new(nil:oid,nil:lng); 8 X_48:bat[:oid,:lng] =[0] := bat.new(nil:oid,nil:lng); 18 "X_49=[11217990] := sql.bind(X_2=0,""sys"",""a"",""value"",0);" 9 "(X_54=[0],r1_54=[0]) := sql.bind(X_2=0,""sys"",""a"",""value"",2);" 5 "X_57=[0] := sql.bind(X_2=0,""sys"",""a"",""value"",1);" 3 X_59=[11217990] := sql.delta(X_49=[11217990],X_54=[0],r1_54=[0],X_57=[0]); 14 X_60=[11217990] := algebra.leftfetchjoin(X_7=:bat[:oid,:oid][11217990],X_59=[11217990]); 145390 X_61=[11217990] := bat.append(X_48=:bat[:oid,:lng][11217990],X_60=[11217990],true); 18 "X_62=[11217990] := sql.bind(X_2=0,""sys"",""b"",""value"",0);" 10 "(X_64=[0],r1_64=[0]) := sql.bind(X_2=0,""sys"",""b"",""value"",2);" 4 "X_66=[0] := sql.bind(X_2=0,""sys"",""b"",""value"",1);" 4 X_67=[11217990] := sql.delta(X_62=[11217990],X_64=[0],r1_64=[0],X_66=[0]); 23 X_68=[11217990] := algebra.leftfetchjoin(X_21=:bat[:oid,:oid][11217990],X_67=[11217990]); 1092752 X_69=[22435980] := bat.append(X_61=[22435980],X_68=[11217990],true); 287822 X_70=[22435980] := bat.append(X_45=:bat[:oid,:lng][22435980],X_69=[22435980],true); 29 "X_71=[11217990] := sql.bind(X_2=0,""sys"",""c"",""value"",0);" 11 "(X_73=[0],r1_73=[0]) := sql.bind(X_2=0,""sys"",""c"",""value"",2);" 6 "X_75=[0] := sql.bind(X_2=0,""sys"",""c"",""value"",1);" 3 X_76=[11217990] := sql.delta(X_71=[11217990],X_73=[0],r1_73=[0],X_75=[0]); 26 X_77=[11217990] := algebra.leftfetchjoin(X_31=:bat[:oid,:oid][11217990],X_76=[11217990]); 1512937 X_78=[33653970] := bat.append(X_70=[33653970],X_77=[11217990],true); 545408 X_80=[33653970] := batcalc.dbl(X_78=[33653970]); 1199559 X_81:bat[:oid,:dbl] =[1] := aggr.subavg(X_80=[33653970],X_41=[33653970],r1_41=[1],true,true); 18 X_82 := sql.resultSet(2,1,X_44=[1]); 10 "sql.rsColumn(X_82=9,"".my_view"",""name"",""clob"",0,0,X_44=[1]);" 4 "sql.rsColumn(X_82=9,"".L1"",""L1"",""double"",53,0,X_81=:bat[:oid,:dbl][1]);" 1 X_93 := io.stdout(); 63 "sql.exportResult(X_93==""104d2"":streams,X_82=9);" 1 end s3_2; 17943579 function user.s3_2(); 8975401 X_5:void := user.s3_2();

-- Frédéric Jolliton Sécuractive _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list