Changeset: e70b4fc6b415 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/e70b4fc6b415
Modified Files:
sql/server/rel_optimizer.c
sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-3join-query.stable.out
sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-3join-query.stable.out.32bit
Branch: default
Log Message:
Make join2semi run after join order optimizer again, so hopefully tpch
performance doesn't hurt. I see a join idx not being used in semijoins, maybe
that's the reason
diffs (183 lines):
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
@@ -9492,6 +9492,7 @@ rel_optimize_joins(visitor *v, sql_rel *
{
rel = rel_push_join_exps_down(v, rel);
rel = rel_out2inner(v, rel);
+ rel = rel_join2semijoin(v, rel);
rel = rel_push_join_down_outer(v, rel);
return rel;
}
@@ -9521,7 +9522,6 @@ rel_optimize_select_and_joins_topdown(vi
rel = rel_simplify_fk_joins(v, rel);
rel = rel_push_select_down(v, rel);
- rel = rel_join2semijoin(v, rel);
if (rel && rel->l && (is_select(rel->op) || is_join(rel->op)))
rel = rel_use_index(v, rel);
diff --git
a/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-3join-query.stable.out
b/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-3join-query.stable.out
---
a/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-3join-query.stable.out
+++
b/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-3join-query.stable.out
@@ -1,22 +1,24 @@
% .plan # table_name
% rel # name
% clob # type
-% 80 # length
+% 65 # length
project (
| group by (
-| | select (
-| | | table("sys"."fk") [ "fk"."%fk_fk1_fkey" JOINIDX "sys"."fk"."fk_fk1_fkey"
]
-| | ) [ ("fk"."%fk_fk1_fkey") ! * = (oid(63) NULL) ]
+| | semijoin (
+| | | table("sys"."fk") [ "fk"."fk1" ],
+| | | table("sys"."pk1") [ "pk1"."pk1" NOT NULL UNIQUE HASHCOL ]
+| | ) [ ("fk"."fk1") = ("pk1"."pk1" NOT NULL HASHCOL ) ]
| ) [ ] [ "sys"."count"() NOT NULL as "%1"."%1" ]
) [ "%1"."%1" NOT NULL ]
% .plan # table_name
% rel # name
% clob # type
-% 114 # length
+% 73 # length
project (
-| select (
-| | table("sys"."fk") [ "fk"."id" NOT NULL UNIQUE HASHCOL ,
"fk"."%fk_fk1_fkey" JOINIDX "sys"."fk"."fk_fk1_fkey" ]
-| ) [ ("fk"."%fk_fk1_fkey") ! * = (oid(63) NULL) ]
+| semijoin (
+| | table("sys"."fk") [ "fk"."id" NOT NULL UNIQUE HASHCOL , "fk"."fk1" ],
+| | table("sys"."pk1") [ "pk1"."pk1" NOT NULL UNIQUE HASHCOL ]
+| ) [ ("fk"."fk1") = ("pk1"."pk1" NOT NULL HASHCOL ) ]
) [ "fk"."id" NOT NULL HASHCOL ] [ "fk"."id" ASC NOT NULL HASHCOL ]
% .plan # table_name
% rel # name
@@ -36,30 +38,30 @@ project (
project (
| select (
| | table("sys"."fk") [ "fk"."id" NOT NULL UNIQUE HASHCOL ,
"fk"."%fk_fk1_fkey" JOINIDX "sys"."fk"."fk_fk1_fkey", "fk"."%fk_fk2_fkey"
JOINIDX "sys"."fk"."fk_fk2_fkey" ]
-| ) [ ("fk"."%fk_fk2_fkey") ! * = (oid(63) NULL), ("fk"."%fk_fk1_fkey") ! * =
(oid(63) NULL) ]
+| ) [ ("fk"."%fk_fk1_fkey") ! * = (oid(63) NULL), ("fk"."%fk_fk2_fkey") ! * =
(oid(63) NULL) ]
) [ "fk"."id" NOT NULL HASHCOL ] [ "fk"."id" ASC NOT NULL HASHCOL ]
% .plan # table_name
% rel # name
% clob # type
+% 171 # length
+project (
+| join (
+| | semijoin (
+| | | table("sys"."fk") [ "fk"."id" NOT NULL UNIQUE HASHCOL ,
"fk"."%fk_fk1_fkey" JOINIDX "sys"."fk"."fk_fk1_fkey", "fk"."%fk_fk2_fkey"
JOINIDX "sys"."fk"."fk_fk2_fkey" ],
+| | | table("sys"."pk2") [ "pk2"."%TID%" NOT NULL UNIQUE ]
+| | ) [ ("fk"."%fk_fk2_fkey") = ("pk2"."%TID%" NOT NULL UNIQUE) JOINIDX
"sys"."fk"."fk_fk2_fkey" ],
+| | table("sys"."pk1") [ "pk1"."v1", "pk1"."%TID%" NOT NULL UNIQUE ]
+| ) [ ("fk"."%fk_fk1_fkey") = ("pk1"."%TID%" NOT NULL UNIQUE) JOINIDX
"sys"."fk"."fk_fk1_fkey" ]
+) [ "fk"."id" NOT NULL HASHCOL , "pk1"."v1" ] [ "fk"."id" ASC NOT NULL HASHCOL
]
+% .plan # table_name
+% rel # name
+% clob # type
% 170 # length
project (
| join (
| | select (
| | | table("sys"."fk") [ "fk"."id" NOT NULL UNIQUE HASHCOL ,
"fk"."%fk_fk1_fkey" JOINIDX "sys"."fk"."fk_fk1_fkey", "fk"."%fk_fk2_fkey"
JOINIDX "sys"."fk"."fk_fk2_fkey" ]
-| | ) [ ("fk"."%fk_fk2_fkey") ! * = (oid(63) NULL) ],
-| | table("sys"."pk1") [ "pk1"."v1", "pk1"."%TID%" NOT NULL UNIQUE ]
-| ) [ ("fk"."%fk_fk1_fkey") = ("pk1"."%TID%" NOT NULL UNIQUE) JOINIDX
"sys"."fk"."fk_fk1_fkey" ]
-) [ "fk"."id" NOT NULL HASHCOL , "pk1"."v1" ] [ "fk"."id" ASC NOT NULL HASHCOL
]
-% .plan # table_name
-% rel # name
-% clob # type
-% 171 # length
-project (
-| join (
-| | semijoin (
-| | | table("sys"."fk") [ "fk"."id" NOT NULL UNIQUE HASHCOL ,
"fk"."%fk_fk1_fkey" JOINIDX "sys"."fk"."fk_fk1_fkey", "fk"."%fk_fk2_fkey"
JOINIDX "sys"."fk"."fk_fk2_fkey" ],
-| | | table("sys"."pk1") [ "pk1"."%TID%" NOT NULL UNIQUE ]
-| | ) [ ("fk"."%fk_fk1_fkey") = ("pk1"."%TID%" NOT NULL UNIQUE) JOINIDX
"sys"."fk"."fk_fk1_fkey" ],
+| | ) [ ("fk"."%fk_fk1_fkey") ! * = (oid(63) NULL) ],
| | table("sys"."pk2") [ "pk2"."v2", "pk2"."%TID%" NOT NULL UNIQUE ]
| ) [ ("fk"."%fk_fk2_fkey") = ("pk2"."%TID%" NOT NULL UNIQUE) JOINIDX
"sys"."fk"."fk_fk2_fkey" ]
) [ "fk"."id" NOT NULL HASHCOL , "pk2"."v2" ] [ "fk"."id" ASC NOT NULL HASHCOL
]
diff --git
a/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-3join-query.stable.out.32bit
b/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-3join-query.stable.out.32bit
---
a/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-3join-query.stable.out.32bit
+++
b/sql/test/FeatureRequests/Tests/foreign_key_outer_join_dead_code_elimination-plan-3join-query.stable.out.32bit
@@ -1,22 +1,24 @@
% .plan # table_name
% rel # name
% clob # type
-% 80 # length
+% 65 # length
project (
| group by (
-| | select (
-| | | table("sys"."fk") [ "fk"."%fk_fk1_fkey" JOINIDX "sys"."fk"."fk_fk1_fkey"
]
-| | ) [ ("fk"."%fk_fk1_fkey") ! * = (oid(31) NULL) ]
+| | semijoin (
+| | | table("sys"."fk") [ "fk"."fk1" ],
+| | | table("sys"."pk1") [ "pk1"."pk1" NOT NULL UNIQUE HASHCOL ]
+| | ) [ ("fk"."fk1") = ("pk1"."pk1" NOT NULL HASHCOL ) ]
| ) [ ] [ "sys"."count"() NOT NULL as "%1"."%1" ]
) [ "%1"."%1" NOT NULL ]
% .plan # table_name
% rel # name
% clob # type
-% 114 # length
+% 73 # length
project (
-| select (
-| | table("sys"."fk") [ "fk"."id" NOT NULL UNIQUE HASHCOL ,
"fk"."%fk_fk1_fkey" JOINIDX "sys"."fk"."fk_fk1_fkey" ]
-| ) [ ("fk"."%fk_fk1_fkey") ! * = (oid(31) NULL) ]
+| semijoin (
+| | table("sys"."fk") [ "fk"."id" NOT NULL UNIQUE HASHCOL , "fk"."fk1" ],
+| | table("sys"."pk1") [ "pk1"."pk1" NOT NULL UNIQUE HASHCOL ]
+| ) [ ("fk"."fk1") = ("pk1"."pk1" NOT NULL HASHCOL ) ]
) [ "fk"."id" NOT NULL HASHCOL ] [ "fk"."id" ASC NOT NULL HASHCOL ]
% .plan # table_name
% rel # name
@@ -36,30 +38,30 @@ project (
project (
| select (
| | table("sys"."fk") [ "fk"."id" NOT NULL UNIQUE HASHCOL ,
"fk"."%fk_fk1_fkey" JOINIDX "sys"."fk"."fk_fk1_fkey", "fk"."%fk_fk2_fkey"
JOINIDX "sys"."fk"."fk_fk2_fkey" ]
-| ) [ ("fk"."%fk_fk2_fkey") ! * = (oid(31) NULL), ("fk"."%fk_fk1_fkey") ! * =
(oid(31) NULL) ]
+| ) [ ("fk"."%fk_fk1_fkey") ! * = (oid(31) NULL), ("fk"."%fk_fk2_fkey") ! * =
(oid(31) NULL) ]
) [ "fk"."id" NOT NULL HASHCOL ] [ "fk"."id" ASC NOT NULL HASHCOL ]
% .plan # table_name
% rel # name
% clob # type
+% 171 # length
+project (
+| join (
+| | semijoin (
+| | | table("sys"."fk") [ "fk"."id" NOT NULL UNIQUE HASHCOL ,
"fk"."%fk_fk1_fkey" JOINIDX "sys"."fk"."fk_fk1_fkey", "fk"."%fk_fk2_fkey"
JOINIDX "sys"."fk"."fk_fk2_fkey" ],
+| | | table("sys"."pk2") [ "pk2"."%TID%" NOT NULL UNIQUE ]
+| | ) [ ("fk"."%fk_fk2_fkey") = ("pk2"."%TID%" NOT NULL UNIQUE) JOINIDX
"sys"."fk"."fk_fk2_fkey" ],
+| | table("sys"."pk1") [ "pk1"."v1", "pk1"."%TID%" NOT NULL UNIQUE ]
+| ) [ ("fk"."%fk_fk1_fkey") = ("pk1"."%TID%" NOT NULL UNIQUE) JOINIDX
"sys"."fk"."fk_fk1_fkey" ]
+) [ "fk"."id" NOT NULL HASHCOL , "pk1"."v1" ] [ "fk"."id" ASC NOT NULL HASHCOL
]
+% .plan # table_name
+% rel # name
+% clob # type
% 170 # length
project (
| join (
| | select (
| | | table("sys"."fk") [ "fk"."id" NOT NULL UNIQUE HASHCOL ,
"fk"."%fk_fk1_fkey" JOINIDX "sys"."fk"."fk_fk1_fkey", "fk"."%fk_fk2_fkey"
JOINIDX "sys"."fk"."fk_fk2_fkey" ]
-| | ) [ ("fk"."%fk_fk2_fkey") ! * = (oid(31) NULL) ],
-| | table("sys"."pk1") [ "pk1"."v1", "pk1"."%TID%" NOT NULL UNIQUE ]
-| ) [ ("fk"."%fk_fk1_fkey") = ("pk1"."%TID%" NOT NULL UNIQUE) JOINIDX
"sys"."fk"."fk_fk1_fkey" ]
-) [ "fk"."id" NOT NULL HASHCOL , "pk1"."v1" ] [ "fk"."id" ASC NOT NULL HASHCOL
]
-% .plan # table_name
-% rel # name
-% clob # type
-% 171 # length
-project (
-| join (
-| | semijoin (
-| | | table("sys"."fk") [ "fk"."id" NOT NULL UNIQUE HASHCOL ,
"fk"."%fk_fk1_fkey" JOINIDX "sys"."fk"."fk_fk1_fkey", "fk"."%fk_fk2_fkey"
JOINIDX "sys"."fk"."fk_fk2_fkey" ],
-| | | table("sys"."pk1") [ "pk1"."%TID%" NOT NULL UNIQUE ]
-| | ) [ ("fk"."%fk_fk1_fkey") = ("pk1"."%TID%" NOT NULL UNIQUE) JOINIDX
"sys"."fk"."fk_fk1_fkey" ],
+| | ) [ ("fk"."%fk_fk1_fkey") ! * = (oid(31) NULL) ],
| | table("sys"."pk2") [ "pk2"."v2", "pk2"."%TID%" NOT NULL UNIQUE ]
| ) [ ("fk"."%fk_fk2_fkey") = ("pk2"."%TID%" NOT NULL UNIQUE) JOINIDX
"sys"."fk"."fk_fk2_fkey" ]
) [ "fk"."id" NOT NULL HASHCOL , "pk2"."v2" ] [ "fk"."id" ASC NOT NULL HASHCOL
]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
