Roberto Cornacchia
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] =