Changeset: 2aaf60e5c587 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2aaf60e5c587
Modified Files:
sql/benchmarks/tpch/Tests/11-explain.stable.out.int128
sql/benchmarks/tpch/Tests/11-plan.stable.out.int128
sql/server/rel_optimizer.c
Branch: default
Log Message:
also push semi/anti join functions (casts etc) down.
diffs (256 lines):
diff --git a/sql/benchmarks/tpch/Tests/11-explain.stable.out.int128
b/sql/benchmarks/tpch/Tests/11-explain.stable.out.int128
--- a/sql/benchmarks/tpch/Tests/11-explain.stable.out.int128
+++ b/sql/benchmarks/tpch/Tests/11-explain.stable.out.int128
@@ -72,57 +72,57 @@ function user.s2_1{autoCommit=true}(A0:s
X_25:bat[:oid,:str] := sql.bind(X_5,"sys","nation","n_name",1);
X_26 := sql.delta(X_21,X_23,r1_23,X_25);
X_27 := algebra.leftfetchjoin(X_19,X_26);
- X_28 := algebra.subselect(X_27,A0,A0,true,true,false);
- X_30 := algebra.leftfetchjoin(X_28,X_19);
- (X_31,r1_32) := algebra.subjoin(X_18,X_30,nil:BAT,nil:BAT,false,nil:lng);
- X_35 := algebra.leftfetchjoin(X_31,X_6);
+ X_84 := algebra.subselect(X_27,A2,A2,true,true,false);
+ X_86 := algebra.leftfetchjoin(X_84,X_19);
+ (X_87,r1_116) := algebra.subjoin(X_18,X_86,nil:BAT,nil:BAT,false,nil:lng);
+ X_91 := algebra.leftfetchjoin(X_87,X_6);
X_36:bat[:oid,:oid] := sql.tid(X_5,"sys","partsupp");
X_38:bat[:oid,:oid] :=
sql.bind_idxbat(X_5,"sys","partsupp","partsupp_ps_suppkey_fkey",0);
(X_41,r1_42) :=
sql.bind_idxbat(X_5,"sys","partsupp","partsupp_ps_suppkey_fkey",2);
X_43:bat[:oid,:oid] :=
sql.bind_idxbat(X_5,"sys","partsupp","partsupp_ps_suppkey_fkey",1);
X_44 := sql.delta(X_38,X_41,r1_42,X_43);
X_45 := algebra.leftfetchjoin(X_36,X_44);
- (X_46,r1_47) := algebra.subjoin(X_35,X_45,nil:BAT,nil:BAT,false,nil:lng);
- X_48:bat[:oid,:int] := sql.bind(X_5,"sys","partsupp","ps_partkey",0);
- (X_51,r1_52) := sql.bind(X_5,"sys","partsupp","ps_partkey",2);
- X_54:bat[:oid,:int] := sql.bind(X_5,"sys","partsupp","ps_partkey",1);
- X_56 := sql.delta(X_48,X_51,r1_52,X_54);
- X_57:bat[:oid,:int] := algebra.leftfetchjoinPath(r1_47,X_36,X_56);
- (X_58,r1_60,r2_60) := group.subgroupdone(X_57);
- X_61 := algebra.leftfetchjoin(r1_60,X_57);
+ (X_92,r1_131) := algebra.subjoin(X_91,X_45,nil:BAT,nil:BAT,false,nil:lng);
X_63:bat[:oid,:lng] := sql.bind(X_5,"sys","partsupp","ps_supplycost",0);
(X_65,r1_67) := sql.bind(X_5,"sys","partsupp","ps_supplycost",2);
X_67:bat[:oid,:lng] := sql.bind(X_5,"sys","partsupp","ps_supplycost",1);
X_68 := sql.delta(X_63,X_65,r1_67,X_67);
X_69 := algebra.leftfetchjoin(X_36,X_68);
- X_70 := algebra.leftfetchjoin(r1_47,X_69);
+ X_95 := algebra.leftfetchjoin(r1_131,X_69);
X_71:bat[:oid,:int] := sql.bind(X_5,"sys","partsupp","ps_availqty",0);
(X_75,r1_81) := sql.bind(X_5,"sys","partsupp","ps_availqty",2);
X_77:bat[:oid,:int] := sql.bind(X_5,"sys","partsupp","ps_availqty",1);
X_78 := sql.delta(X_71,X_75,r1_81,X_77);
X_79 := algebra.leftfetchjoin(X_36,X_78);
- X_80 := algebra.leftfetchjoin(r1_47,X_79);
- X_81:bat[:oid,:hge] := batcalc.*(X_70,X_80);
- X_82:bat[:oid,:hge] := aggr.subsum(X_81,X_58,r1_60,true,true);
- X_62 := bat.mirror(X_61);
- X_84 := algebra.subselect(X_27,A2,A2,true,true,false);
- X_86 := algebra.leftfetchjoin(X_84,X_19);
- (X_87,r1_116) := algebra.subjoin(X_18,X_86,nil:BAT,nil:BAT,false,nil:lng);
- X_91 := algebra.leftfetchjoin(X_87,X_6);
- (X_92,r1_131) := algebra.subjoin(X_91,X_45,nil:BAT,nil:BAT,false,nil:lng);
- X_95 := algebra.leftfetchjoin(r1_131,X_69);
X_96 := algebra.leftfetchjoin(r1_131,X_79);
X_97:bat[:oid,:hge] := batcalc.*(X_95,X_96);
X_98:hge := aggr.sum(X_97);
X_99 := calc.*(X_98,A1);
- X_100 := sql.single(X_99);
- X_102:bat[:oid,:hge] := batsql.dec_round(X_100,100);
- (X_103,r1_167) :=
algebra.subthetajoin(X_82,X_102,nil:BAT,nil:BAT,1,true,nil:lng);
+ X_28 := algebra.subselect(X_27,A0,A0,true,true,false);
+ X_30 := algebra.leftfetchjoin(X_28,X_19);
+ (X_31,r1_32) := algebra.subjoin(X_18,X_30,nil:BAT,nil:BAT,false,nil:lng);
+ X_35 := algebra.leftfetchjoin(X_31,X_6);
+ (X_46,r1_47) := algebra.subjoin(X_35,X_45,nil:BAT,nil:BAT,false,nil:lng);
+ X_70 := algebra.leftfetchjoin(r1_47,X_69);
+ X_80 := algebra.leftfetchjoin(r1_47,X_79);
+ X_81:bat[:oid,:hge] := batcalc.*(X_70,X_80);
+ X_48:bat[:oid,:int] := sql.bind(X_5,"sys","partsupp","ps_partkey",0);
+ (X_51,r1_52) := sql.bind(X_5,"sys","partsupp","ps_partkey",2);
+ X_54:bat[:oid,:int] := sql.bind(X_5,"sys","partsupp","ps_partkey",1);
+ X_56 := sql.delta(X_48,X_51,r1_52,X_54);
+ X_57:bat[:oid,:int] := algebra.leftfetchjoinPath(r1_47,X_36,X_56);
+ (X_58,r1_60,r2_60) := group.subgroupdone(X_57);
+ X_61 := algebra.leftfetchjoin(r1_60,X_57);
+ X_82:bat[:oid,:hge] := aggr.subsum(X_81,X_58,r1_60,true,true);
+ X_62 := bat.mirror(X_61);
+ X_101 := sql.dec_round(X_99,100);
+ X_102 := sql.single(X_101);
+ (X_103,r1_165) :=
algebra.subthetajoin(X_82,X_102,nil:BAT,nil:BAT,1,true,nil:lng);
X_108 := algebra.tinter(X_62,X_103);
X_109 := algebra.leftfetchjoin(X_108,X_82);
- (X_110,r1_174,r2_174) := algebra.subsort(X_109,true,false);
- X_114:bat[:oid,:int] := algebra.leftfetchjoinPath(r1_174,X_108,X_61);
- X_115 := algebra.leftfetchjoin(r1_174,X_109);
+ (X_110,r1_172,r2_172) := algebra.subsort(X_109,true,false);
+ X_114:bat[:oid,:int] := algebra.leftfetchjoinPath(r1_172,X_108,X_61);
+ X_115 := algebra.leftfetchjoin(r1_172,X_109);
X_116 := sql.resultSet(2,1,X_114);
sql.rsColumn(X_116,"sys.partsupp","ps_partkey","int",32,0,X_114);
sql.rsColumn(X_116,"sys.L1","value","decimal",39,2,X_115);
diff --git a/sql/benchmarks/tpch/Tests/11-plan.stable.out.int128
b/sql/benchmarks/tpch/Tests/11-plan.stable.out.int128
--- a/sql/benchmarks/tpch/Tests/11-plan.stable.out.int128
+++ b/sql/benchmarks/tpch/Tests/11-plan.stable.out.int128
@@ -55,34 +55,38 @@ Ready.
% .plan # table_name
% rel # name
% clob # type
-% 250 # length
+% 253 # length
project (
-| semijoin (
-| | group by (
-| | | join (
-| | | | join (
-| | | | | table(sys.supplier) [ supplier.%TID% NOT NULL,
supplier.%supplier_s_nationkey_fkey NOT NULL JOINIDX
sys.supplier.supplier_s_nationkey_fkey ] COUNT ,
-| | | | | select (
-| | | | | | table(sys.nation) [ nation.n_name NOT NULL, nation.%TID% NOT NULL
] COUNT
-| | | | | ) [ nation.n_name NOT NULL = char(25) "GERMANY" ]
-| | | | ) [ supplier.%supplier_s_nationkey_fkey NOT NULL = nation.%TID% NOT
NULL JOINIDX sys.supplier.supplier_s_nationkey_fkey ],
-| | | | table(sys.partsupp) [ partsupp.ps_partkey NOT NULL HASHCOL ,
partsupp.ps_availqty NOT NULL, partsupp.ps_supplycost NOT NULL,
partsupp.%partsupp_ps_suppkey_fkey NOT NULL JOINIDX
sys.partsupp.partsupp_ps_suppkey_fkey ] COUNT
-| | | ) [ partsupp.%partsupp_ps_suppkey_fkey NOT NULL = supplier.%TID% NOT
NULL JOINIDX sys.partsupp.partsupp_ps_suppkey_fkey ]
-| | ) [ partsupp.ps_partkey NOT NULL HASHCOL ] [ partsupp.ps_partkey NOT NULL
HASHCOL , sys.sum no nil (sys.sql_mul(decimal(39,2)[partsupp.ps_supplycost NOT
NULL] as partsupp.ps_supplycost, partsupp.ps_availqty NOT NULL)) as L1.L1,
L1.L1 as L2.L2 ],
-| | project (
-| | | group by (
-| | | | join (
+| project (
+| | semijoin (
+| | | project (
+| | | | group by (
| | | | | join (
-| | | | | | table(sys.supplier) [ supplier.%TID% NOT NULL,
supplier.%supplier_s_nationkey_fkey NOT NULL JOINIDX
sys.supplier.supplier_s_nationkey_fkey ] COUNT ,
-| | | | | | select (
-| | | | | | | table(sys.nation) [ nation.n_name NOT NULL, nation.%TID% NOT
NULL ] COUNT
-| | | | | | ) [ nation.n_name NOT NULL = char(25) "GERMANY" ]
-| | | | | ) [ supplier.%supplier_s_nationkey_fkey NOT NULL = nation.%TID% NOT
NULL JOINIDX sys.supplier.supplier_s_nationkey_fkey ],
-| | | | | table(sys.partsupp) [ partsupp.ps_availqty NOT NULL,
partsupp.ps_supplycost NOT NULL, partsupp.%partsupp_ps_suppkey_fkey NOT NULL
JOINIDX sys.partsupp.partsupp_ps_suppkey_fkey ] COUNT
-| | | | ) [ partsupp.%partsupp_ps_suppkey_fkey NOT NULL = supplier.%TID% NOT
NULL JOINIDX sys.partsupp.partsupp_ps_suppkey_fkey ]
-| | | ) [ ] [ sys.sum no nil
(sys.sql_mul(decimal(39,2)[partsupp.ps_supplycost NOT NULL] as
partsupp.ps_supplycost, partsupp.ps_availqty NOT NULL)) as L3.L3 ]
-| | ) [ sys.sql_mul(L3, decimal(4,3) "1") as L4.L4 ]
-| ) [ L2 > sys.scale_down(L4.L4, hugeint "100") ]
+| | | | | | join (
+| | | | | | | table(sys.supplier) [ supplier.%TID% NOT NULL,
supplier.%supplier_s_nationkey_fkey NOT NULL JOINIDX
sys.supplier.supplier_s_nationkey_fkey ] COUNT ,
+| | | | | | | select (
+| | | | | | | | table(sys.nation) [ nation.n_name NOT NULL, nation.%TID% NOT
NULL ] COUNT
+| | | | | | | ) [ nation.n_name NOT NULL = char(25) "GERMANY" ]
+| | | | | | ) [ supplier.%supplier_s_nationkey_fkey NOT NULL = nation.%TID%
NOT NULL JOINIDX sys.supplier.supplier_s_nationkey_fkey ],
+| | | | | | table(sys.partsupp) [ partsupp.ps_partkey NOT NULL HASHCOL ,
partsupp.ps_availqty NOT NULL, partsupp.ps_supplycost NOT NULL,
partsupp.%partsupp_ps_suppkey_fkey NOT NULL JOINIDX
sys.partsupp.partsupp_ps_suppkey_fkey ] COUNT
+| | | | | ) [ partsupp.%partsupp_ps_suppkey_fkey NOT NULL = supplier.%TID% NOT
NULL JOINIDX sys.partsupp.partsupp_ps_suppkey_fkey ]
+| | | | ) [ partsupp.ps_partkey NOT NULL HASHCOL ] [ partsupp.ps_partkey NOT
NULL HASHCOL , sys.sum no nil (sys.sql_mul(decimal(39,2)[partsupp.ps_supplycost
NOT NULL] as partsupp.ps_supplycost, partsupp.ps_availqty NOT NULL)) as L1.L1,
L1.L1 as L2.L2 ]
+| | | ) [ partsupp.ps_partkey NOT NULL HASHCOL , L1.L1, L2.L2, L2 as L5.L5 ],
+| | | project (
+| | | | group by (
+| | | | | join (
+| | | | | | join (
+| | | | | | | table(sys.supplier) [ supplier.%TID% NOT NULL,
supplier.%supplier_s_nationkey_fkey NOT NULL JOINIDX
sys.supplier.supplier_s_nationkey_fkey ] COUNT ,
+| | | | | | | select (
+| | | | | | | | table(sys.nation) [ nation.n_name NOT NULL, nation.%TID% NOT
NULL ] COUNT
+| | | | | | | ) [ nation.n_name NOT NULL = char(25) "GERMANY" ]
+| | | | | | ) [ supplier.%supplier_s_nationkey_fkey NOT NULL = nation.%TID%
NOT NULL JOINIDX sys.supplier.supplier_s_nationkey_fkey ],
+| | | | | | table(sys.partsupp) [ partsupp.ps_availqty NOT NULL,
partsupp.ps_supplycost NOT NULL, partsupp.%partsupp_ps_suppkey_fkey NOT NULL
JOINIDX sys.partsupp.partsupp_ps_suppkey_fkey ] COUNT
+| | | | | ) [ partsupp.%partsupp_ps_suppkey_fkey NOT NULL = supplier.%TID% NOT
NULL JOINIDX sys.partsupp.partsupp_ps_suppkey_fkey ]
+| | | | ) [ ] [ sys.sum no nil
(sys.sql_mul(decimal(39,2)[partsupp.ps_supplycost NOT NULL] as
partsupp.ps_supplycost, partsupp.ps_availqty NOT NULL)) as L3.L3 ]
+| | | ) [ sys.sql_mul(L3, decimal(4,3) "1") as L4.L4, sys.scale_down(L4.L4,
hugeint "100") as L6.L6 ]
+| | ) [ L5.L5 > L6.L6 ]
+| ) [ partsupp.ps_partkey NOT NULL HASHCOL , L1.L1, L2.L2 ]
) [ partsupp.ps_partkey NOT NULL, L1 as L1.value ] [ L1.value ]
# 22:46:31 >
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -1381,7 +1381,7 @@ exps_can_push_func(list *exps, sql_rel *
sql_exp *e = n->data;
int must = 0, mustl = 0, mustr = 0;
- if (is_join(rel->op) && ((can_push_func(e, rel->l, &mustl) &&
mustl) || (can_push_func(e, rel->r, &mustr) && mustr)))
+ if (is_joinop(rel->op) && ((can_push_func(e, rel->l, &mustl) &&
mustl) || (can_push_func(e, rel->r, &mustr) && mustr)))
return 1;
else if (is_select(rel->op) && can_push_func(e, NULL, &must) &&
must)
return 1;
@@ -1424,7 +1424,7 @@ exps_need_push_down( list *exps )
static sql_rel *
rel_push_func_down(int *changes, mvc *sql, sql_rel *rel)
{
- if ((is_select(rel->op) || is_join(rel->op) || is_semi(rel->op)) &&
rel->l && rel->exps && !(rel_is_ref(rel))) {
+ if ((is_select(rel->op) || is_joinop(rel->op)) && rel->l && rel->exps
&& !(rel_is_ref(rel))) {
list *exps = rel->exps;
if (is_select(rel->op) && list_length(rel->exps) <= 1) /*
only push down when thats useful */
@@ -1443,7 +1443,7 @@ rel_push_func_down(int *changes, mvc *sq
rel->l = l = rel_project(sql->sa, l,
rel_projections(sql, l, NULL, 1, 1));
}
- if (is_join(rel->op) && r->op != op_project) {
+ if (is_joinop(rel->op) && r->op != op_project) {
if (is_subquery(r))
return rel;
rel->r = r = rel_project(sql->sa, r,
@@ -1456,11 +1456,11 @@ rel_push_func_down(int *changes, mvc *sq
if (e->type == e_column)
continue;
- if ((is_join(rel->op) && ((can_push_func(e, l,
&mustl) && mustl) || (can_push_func(e, r, &mustr) && mustr))) ||
+ if ((is_joinop(rel->op) && ((can_push_func(e,
l, &mustl) && mustl) || (can_push_func(e, r, &mustr) && mustr))) ||
(is_select(rel->op) && can_push_func(e,
NULL, &must) && must)) {
must = 0; mustl = 0; mustr = 0;
if (e->type != e_cmp) { /* predicate */
- if ((is_join(rel->op) &&
((can_push_func(e, l, &mustl) && mustl) || (can_push_func(e, r, &mustr) &&
mustr))) ||
+ if ((is_joinop(rel->op) &&
((can_push_func(e, l, &mustl) && mustl) || (can_push_func(e, r, &mustr) &&
mustr))) ||
(is_select(rel->op) &&
can_push_func(e, NULL, &must) && must)) {
exp_label(sql->sa, e,
++sql->label);
if (mustr)
@@ -1473,7 +1473,7 @@ rel_push_func_down(int *changes, mvc *sq
}
} else {
ne = e->l;
- if ((is_join(rel->op) &&
((can_push_func(ne, l, &mustl) && mustl) || (can_push_func(ne, r, &mustr) &&
mustr))) ||
+ if ((is_joinop(rel->op) &&
((can_push_func(ne, l, &mustl) && mustl) || (can_push_func(ne, r, &mustr) &&
mustr))) ||
(is_select(rel->op) &&
can_push_func(ne, NULL, &must) && must)) {
exp_label(sql->sa, ne,
++sql->label);
if (mustr)
@@ -1487,7 +1487,7 @@ rel_push_func_down(int *changes, mvc *sq
must = 0; mustl = 0; mustr = 0;
ne = e->r;
- if ((is_join(rel->op) &&
((can_push_func(ne, l, &mustl) && mustl) || (can_push_func(ne, r, &mustr) &&
mustr))) ||
+ if ((is_joinop(rel->op) &&
((can_push_func(ne, l, &mustl) && mustl) || (can_push_func(ne, r, &mustr) &&
mustr))) ||
(is_select(rel->op) &&
can_push_func(ne, NULL, &must) && must)) {
exp_label(sql->sa, ne,
++sql->label);
if (mustr)
@@ -1502,7 +1502,7 @@ rel_push_func_down(int *changes, mvc *sq
if (e->f) {
must = 0; mustl = 0;
mustr = 0;
ne = e->f;
- if ((is_join(rel->op)
&& ((can_push_func(ne, l, &mustl) && mustl) || (can_push_func(ne, r, &mustr) &&
mustr))) ||
+ if ((is_joinop(rel->op)
&& ((can_push_func(ne, l, &mustl) && mustl) || (can_push_func(ne, r, &mustr) &&
mustr))) ||
(is_select(rel->op)
&& can_push_func(ne, NULL, &must) && must)) {
exp_label(sql->sa, ne, ++sql->label);
if (mustr)
@@ -1522,7 +1522,7 @@ rel_push_func_down(int *changes, mvc *sq
} else {
if (l != ol)
rel->l = ol;
- if (is_join(rel->op) && r != or)
+ if (is_joinop(rel->op) && r != or)
rel->r = or;
}
}
@@ -1530,7 +1530,7 @@ rel_push_func_down(int *changes, mvc *sq
if (rel->op == op_project && rel->l && rel->exps) {
sql_rel *pl = rel->l;
- if (is_join(pl->op) && exps_can_push_func(rel->exps, rel)) {
+ if (is_joinop(pl->op) && exps_can_push_func(rel->exps, rel)) {
node *n;
sql_rel *l = pl->l, *r = pl->r;
list *nexps;
@@ -1541,7 +1541,7 @@ rel_push_func_down(int *changes, mvc *sq
pl->l = l = rel_project(sql->sa, l,
rel_projections(sql, l, NULL, 1, 1));
}
- if (is_join(rel->op) && r->op != op_project) {
+ if (is_joinop(rel->op) && r->op != op_project) {
if (is_subquery(r))
return rel;
pl->r = r = rel_project(sql->sa, r,
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
