You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Hanumath Rao Maduri (JIRA)" <ji...@apache.org> on 2019/01/28 18:59:00 UTC
[jira] [Created] (CALCITE-2813) Semijoins are not being considered
in LoptOptimizeJoinRule for ordering the joins
Hanumath Rao Maduri created CALCITE-2813:
--------------------------------------------
Summary: Semijoins are not being considered in LoptOptimizeJoinRule for ordering the joins
Key: CALCITE-2813
URL: https://issues.apache.org/jira/browse/CALCITE-2813
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.17.0
Reporter: Hanumath Rao Maduri
Assignee: Julian Hyde
During the debugging a performance issue in Apache Drill, I have noticed that the semi joins are not being considered in LoptOptimizeJoinRule. Due to this sub-optimal join ordering is resulting for some of the Drill queries.
Here is an example for the same.
: jdbc:drill:zk=local> alter session set `planner.enable_semijoin` = true;
+-------+-----------------------------------+
| ok | summary |
+-------+-----------------------------------+
| true | planner.enable_semijoin updated. |
+-------+-----------------------------------+
1 row selected (0.073 seconds)
0: jdbc:drill:zk=local> explain plan without implementation for select * from dfs.`/home/mapr/data/sf1/parquet/web_sales` ws1, dfs.`/home/mapr/data/sf1/parquet/web_sales` ws2 where ws1.ws_ship_date_sk in (select dd.d_date_sk from dfs.`/home/mapr/data/sf1/parquet/date_dim` dd) and ws1.ws_order_number = ws2.ws_order_number;
+----------------------------------------------------------------------------------+----------------------------------------------------------------------------------+
| text | json |
+----------------------------------------------------------------------------------+----------------------------------------------------------------------------------+
| DrillScreenRel
DrillProjectRel(**=[$0], **0=[$3])
DrillSemiJoinRel(condition=[=($1, $6)], joinType=[inner])
DrillJoinRel(condition=[=($2, $5)], joinType=[inner])
DrillScanRel(table=[[dfs, /home/mapr/data/sf1/parquet/web_sales]], groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=file:/home/mapr/data/sf1/parquet/web_sales]], selectionRoot=file:/home/mapr/data/sf1/parquet/web_sales, numFiles=1, numRowGroups=1, usedMetadataFile=false, columns=[`**`, `ws_ship_date_sk`, `ws_order_number`]]])
DrillScanRel(table=[[dfs, /home/mapr/data/sf1/parquet/web_sales]], groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=file:/home/mapr/data/sf1/parquet/web_sales]], selectionRoot=file:/home/mapr/data/sf1/parquet/web_sales, numFiles=1, numRowGroups=1, usedMetadataFile=false, columns=[`**`, `ws_ship_date_sk`, `ws_order_number`]]])
DrillScanRel(table=[[dfs, /home/mapr/data/sf1/parquet/date_dim]], groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=file:/home/mapr/data/sf1/parquet/date_dim]], selectionRoot=file:/home/mapr/data/sf1/parquet/date_dim, numFiles=1, numRowGroups=1, usedMetadataFile=false, columns=[`d_date_sk`]]])
0: jdbc:drill:zk=local> alter session set `planner.enable_semijoin` = false;
+-------+-----------------------------------+
| ok | summary |
+-------+-----------------------------------+
| true | planner.enable_semijoin updated. |
+-------+-----------------------------------+
1 row selected (0.077 seconds)
0: jdbc:drill:zk=local> explain plan without implementation for select * from dfs.`/home/mapr/data/sf1/parquet/web_sales` ws1, dfs.`/home/mapr/data/sf1/parquet/web_sales` ws2 where ws1.ws_ship_date_sk in (select dd.d_date_sk from dfs.`/home/mapr/data/sf1/parquet/date_dim` dd) and ws1.ws_order_number = ws2.ws_order_number;
+----------------------------------------------------------------------------------+----------------------------------------------------------------------------------+
| text | json |
+----------------------------------------------------------------------------------+----------------------------------------------------------------------------------+
| DrillScreenRel
DrillProjectRel(**=[$0], **0=[$3])
DrillProjectRel(**=[$0], ws_ship_date_sk=[$1], ws_order_number=[$2], **0=[$4], ws_ship_date_sk0=[$5], ws_order_number0=[$6], d_date_sk=[$3])
DrillJoinRel(condition=[=($2, $6)], joinType=[inner])
DrillJoinRel(condition=[=($1, $3)], joinType=[inner])
DrillScanRel(table=[[dfs, /home/mapr/data/sf1/parquet/web_sales]], groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=file:/home/mapr/data/sf1/parquet/web_sales]], selectionRoot=file:/home/mapr/data/sf1/parquet/web_sales, numFiles=1, numRowGroups=1, usedMetadataFile=false, columns=[`**`, `ws_ship_date_sk`, `ws_order_number`]]])
DrillAggregateRel(group=[\{0}])
DrillScanRel(table=[[dfs, /home/mapr/data/sf1/parquet/date_dim]], groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=file:/home/mapr/data/sf1/parquet/date_dim]], selectionRoot=file:/home/mapr/data/sf1/parquet/date_dim, numFiles=1, numRowGroups=1, usedMetadataFile=false, columns=[`d_date_sk`]]])
DrillScanRel(table=[[dfs, /home/mapr/data/sf1/parquet/web_sales]], groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=file:/home/mapr/data/sf1/parquet/web_sales]], selectionRoot=file:/home/mapr/data/sf1/parquet/web_sales, numFiles=1, numRowGroups=1, usedMetadataFile=false, columns=[`**`, `ws_ship_date_sk`, `ws_order_number`]]])
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)