Roberto Cornacchia roberto.cornacchia@gmail.com 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] =<tmp_7341>[0] := bat.new(nil:oid,nil:str); 13 X_6:bat[:oid,:str] =<tmp_7753>[0] := bat.new(nil:oid,nil:str); 4 X_2 := sql.mvc(); 15 "X_7:bat[:oid,:oid] =<tmp_7240>[11217990] := sql.tid(X_2=0,""sys"",""a"");" 9 "X_10=<tmp_20662>[11217990] := sql.bind(X_2=0,""sys"",""a"",""name"",0);" 11 "(X_13=<tmp_2523>[0],r1_13=<tmp_4253>[0]) := sql.bind(X_2=0,""sys"",""a"",""name"",2);" 6 "X_16=<tmp_20354>[0] := sql.bind(X_2=0,""sys"",""a"",""name"",1);" 4 X_18=<tmp_20662>[11217990] := sql.delta(X_10=<tmp_20662>[11217990],X_13=<tmp_2523>[0],r1_13=<tmp_4253>[0],X_16=<tmp_20354>[0]); 21 X_19=<tmp_7444>[11217990] := algebra.leftfetchjoin(X_7=<tmp_7240>:bat[:oid,:oid][11217990],X_18=<tmp_20662>[11217990]); 2427642 X_20=<tmp_7753>[11217990] := bat.append(X_6=<tmp_7753>:bat[:oid,:str][11217990],X_19=<tmp_7444>[11217990],true); 27 "X_21:bat[:oid,:oid] =<tmp_7444>[11217990] := sql.tid(X_2=0,""sys"",""b"");" 10 "X_23=<tmp_21702>[11217990] := sql.bind(X_2=0,""sys"",""b"",""name"",0);" 9 "(X_24=<tmp_2523>[0],r1_24=<tmp_4253>[0]) := sql.bind(X_2=0,""sys"",""b"",""name"",2);" 6 "X_26=<tmp_21066>[0] := sql.bind(X_2=0,""sys"",""b"",""name"",1);" 3 X_27=<tmp_21702>[11217990] := sql.delta(X_23=<tmp_21702>[11217990],X_24=<tmp_2523>[0],r1_24=<tmp_4253>[0],X_26=<tmp_21066>[0]); 22 X_28=<tmp_10474>[11217990] := algebra.leftfetchjoin(X_21=<tmp_7444>:bat[:oid,:oid][11217990],X_27=<tmp_21702>[11217990]); 2512634 X_29=<tmp_7753>[22435980] := bat.append(X_20=<tmp_7753>[22435980],X_28=<tmp_10474>[11217990],true); 4840265 X_30=<tmp_7341>[22435980] := bat.append(X_3=<tmp_7341>:bat[:oid,:str][22435980],X_29=<tmp_7753>[22435980],true); 30 "X_31:bat[:oid,:oid] =<tmp_7753>[11217990] := sql.tid(X_2=0,""sys"",""c"");" 12 "X_33=<tmp_21373>[11217990] := sql.bind(X_2=0,""sys"",""c"",""name"",0);" 10 "(X_34=<tmp_2523>[0],r1_34=<tmp_4253>[0]) := sql.bind(X_2=0,""sys"",""c"",""name"",2);" 5 "X_36=<tmp_17232>[0] := sql.bind(X_2=0,""sys"",""c"",""name"",1);" 3 X_37=<tmp_21373>[11217990] := sql.delta(X_33=<tmp_21373>[11217990],X_34=<tmp_2523>[0],r1_34=<tmp_4253>[0],X_36=<tmp_17232>[0]); 20 X_38=<tmp_10474>[11217990] := algebra.leftfetchjoin(X_31=<tmp_7753>:bat[:oid,:oid][11217990],X_37=<tmp_21373>[11217990]); 2604326 X_39=<tmp_7341>[33653970] := bat.append(X_30=<tmp_7341>[33653970],X_38=<tmp_10474>[11217990],true); 533287 (X_41=<tmp_10474>[33653970],r1_41=<tmp_10056>[1],r2_41=<tmp_10265>[1]) := group.subgroupdone(X_39=<tmp_7341>[33653970]); 30 X_44=<tmp_10265>[1] := algebra.leftfetchjoin(r1_41=<tmp_10056>[1],X_39=<tmp_7341>[33653970]); 13 X_45:bat[:oid,:lng] =<tmp_7341>[0] := bat.new(nil:oid,nil:lng); 8 X_48:bat[:oid,:lng] =<tmp_7136>[0] := bat.new(nil:oid,nil:lng); 18 "X_49=<tmp_13127>[11217990] := sql.bind(X_2=0,""sys"",""a"",""value"",0);" 9 "(X_54=<tmp_2523>[0],r1_54=<tmp_4047>[0]) := sql.bind(X_2=0,""sys"",""a"",""value"",2);" 5 "X_57=<tmp_13230>[0] := sql.bind(X_2=0,""sys"",""a"",""value"",1);" 3 X_59=<tmp_13127>[11217990] := sql.delta(X_49=<tmp_13127>[11217990],X_54=<tmp_2523>[0],r1_54=<tmp_4047>[0],X_57=<tmp_13230>[0]); 14 X_60=<tmp_10370>[11217990] := algebra.leftfetchjoin(X_7=<tmp_7240>:bat[:oid,:oid][11217990],X_59=<tmp_13127>[11217990]); 145390 X_61=<tmp_7136>[11217990] := bat.append(X_48=<tmp_7136>:bat[:oid,:lng][11217990],X_60=<tmp_10370>[11217990],true); 18 "X_62=<tmp_14140>[11217990] := sql.bind(X_2=0,""sys"",""b"",""value"",0);" 10 "(X_64=<tmp_2523>[0],r1_64=<tmp_4047>[0]) := sql.bind(X_2=0,""sys"",""b"",""value"",2);" 4 "X_66=<tmp_12063>[0] := sql.bind(X_2=0,""sys"",""b"",""value"",1);" 4 X_67=<tmp_14140>[11217990] := sql.delta(X_62=<tmp_14140>[11217990],X_64=<tmp_2523>[0],r1_64=<tmp_4047>[0],X_66=<tmp_12063>[0]); 23 X_68=<tmp_10370>[11217990] := algebra.leftfetchjoin(X_21=<tmp_7444>:bat[:oid,:oid][11217990],X_67=<tmp_14140>[11217990]); 1092752 X_69=<tmp_7136>[22435980] := bat.append(X_61=<tmp_7136>[22435980],X_68=<tmp_10370>[11217990],true); 287822 X_70=<tmp_7341>[22435980] := bat.append(X_45=<tmp_7341>:bat[:oid,:lng][22435980],X_69=<tmp_7136>[22435980],true); 29 "X_71=<tmp_11036>[11217990] := sql.bind(X_2=0,""sys"",""c"",""value"",0);" 11 "(X_73=<tmp_2523>[0],r1_73=<tmp_4047>[0]) := sql.bind(X_2=0,""sys"",""c"",""value"",2);" 6 "X_75=<tmp_11761>[0] := sql.bind(X_2=0,""sys"",""c"",""value"",1);" 3 X_76=<tmp_11036>[11217990] := sql.delta(X_71=<tmp_11036>[11217990],X_73=<tmp_2523>[0],r1_73=<tmp_4047>[0],X_75=<tmp_11761>[0]); 26 X_77=<tmp_7136>[11217990] := algebra.leftfetchjoin(X_31=<tmp_7753>:bat[:oid,:oid][11217990],X_76=<tmp_11036>[11217990]); 1512937 X_78=<tmp_7341>[33653970] := bat.append(X_70=<tmp_7341>[33653970],X_77=<tmp_7136>[11217990],true); 545408 X_80=<tmp_7136>[33653970] := batcalc.dbl(X_78=<tmp_7341>[33653970]); 1199559 X_81:bat[:oid,:dbl] =<tmp_7341>[1] := aggr.subavg(X_80=<tmp_7136>[33653970],X_41=<tmp_10474>[33653970],r1_41=<tmp_10056>[1],true,true); 18 X_82 := sql.resultSet(2,1,X_44=<tmp_10265>[1]); 10 "sql.rsColumn(X_82=9,"".my_view"",""name"",""clob"",0,0,X_44=<tmp_10265>[1]);" 4 "sql.rsColumn(X_82=9,"".L1"",""L1"",""double"",53,0,X_81=<tmp_7341>: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();