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.
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();
--
Frédéric Jolliton
Sécuractive
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list