[
https://issues.apache.org/jira/browse/CALCITE-2813?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hanumath Rao Maduri updated CALCITE-2813:
-----------------------------------------
Description:
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.
{code}
: 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/hanu/data/sf1/parquet/web_sales` ws1,
dfs.`/home/hanu/data/sf1/parquet/web_sales` ws2 where ws1.ws_ship_date_sk in
(select dd.d_date_sk from dfs.`/home/hanu/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/hanu/data/sf1/parquet/web_sales]],
groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=file:/home/hanu/data/sf1/parquet/web_sales]],
selectionRoot=file:/home/hanu/data/sf1/parquet/web_sales, numFiles=1,
numRowGroups=1, usedMetadataFile=false, columns=[`**`, `ws_ship_date_sk`,
`ws_order_number`]]])
DrillScanRel(table=[[dfs, /home/hanu/data/sf1/parquet/web_sales]],
groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=file:/home/hanu/data/sf1/parquet/web_sales]],
selectionRoot=file:/home/hanu/data/sf1/parquet/web_sales, numFiles=1,
numRowGroups=1, usedMetadataFile=false, columns=[`**`, `ws_ship_date_sk`,
`ws_order_number`]]])
DrillScanRel(table=[[dfs, /home/hanu/data/sf1/parquet/date_dim]],
groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=file:/home/hanu/data/sf1/parquet/date_dim]],
selectionRoot=file:/home/hanu/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/hanu/data/sf1/parquet/web_sales` ws1,
dfs.`/home/hanu/data/sf1/parquet/web_sales` ws2 where ws1.ws_ship_date_sk in
(select dd.d_date_sk from dfs.`/home/hanu/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/hanu/data/sf1/parquet/web_sales]],
groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=file:/home/hanu/data/sf1/parquet/web_sales]],
selectionRoot=file:/home/hanu/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/hanu/data/sf1/parquet/date_dim]],
groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=file:/home/hanu/data/sf1/parquet/date_dim]],
selectionRoot=file:/home/hanu/data/sf1/parquet/date_dim, numFiles=1,
numRowGroups=1, usedMetadataFile=false, columns=[`d_date_sk`]]])
DrillScanRel(table=[[dfs, /home/hanu/data/sf1/parquet/web_sales]],
groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=file:/home/hanu/data/sf1/parquet/web_sales]],
selectionRoot=file:/home/hanu/data/sf1/parquet/web_sales, numFiles=1,
numRowGroups=1, usedMetadataFile=false, columns=[`**`, `ws_ship_date_sk`,
`ws_order_number`]]])
{code}
was:
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.
{code}
: 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`]]])
{code}
> Semijoins are not being considered in LoptOptimizeJoinRule during join
> ordering
> -------------------------------------------------------------------------------
>
> 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
> Priority: Major
>
> 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.
> {code}
> : 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/hanu/data/sf1/parquet/web_sales` ws1,
> dfs.`/home/hanu/data/sf1/parquet/web_sales` ws2 where ws1.ws_ship_date_sk in
> (select dd.d_date_sk from dfs.`/home/hanu/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/hanu/data/sf1/parquet/web_sales]],
> groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
> [path=file:/home/hanu/data/sf1/parquet/web_sales]],
> selectionRoot=file:/home/hanu/data/sf1/parquet/web_sales, numFiles=1,
> numRowGroups=1, usedMetadataFile=false, columns=[`**`, `ws_ship_date_sk`,
> `ws_order_number`]]])
> DrillScanRel(table=[[dfs, /home/hanu/data/sf1/parquet/web_sales]],
> groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
> [path=file:/home/hanu/data/sf1/parquet/web_sales]],
> selectionRoot=file:/home/hanu/data/sf1/parquet/web_sales, numFiles=1,
> numRowGroups=1, usedMetadataFile=false, columns=[`**`, `ws_ship_date_sk`,
> `ws_order_number`]]])
> DrillScanRel(table=[[dfs, /home/hanu/data/sf1/parquet/date_dim]],
> groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
> [path=file:/home/hanu/data/sf1/parquet/date_dim]],
> selectionRoot=file:/home/hanu/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/hanu/data/sf1/parquet/web_sales` ws1,
> dfs.`/home/hanu/data/sf1/parquet/web_sales` ws2 where ws1.ws_ship_date_sk in
> (select dd.d_date_sk from dfs.`/home/hanu/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/hanu/data/sf1/parquet/web_sales]],
> groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
> [path=file:/home/hanu/data/sf1/parquet/web_sales]],
> selectionRoot=file:/home/hanu/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/hanu/data/sf1/parquet/date_dim]],
> groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
> [path=file:/home/hanu/data/sf1/parquet/date_dim]],
> selectionRoot=file:/home/hanu/data/sf1/parquet/date_dim, numFiles=1,
> numRowGroups=1, usedMetadataFile=false, columns=[`d_date_sk`]]])
> DrillScanRel(table=[[dfs, /home/hanu/data/sf1/parquet/web_sales]],
> groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
> [path=file:/home/hanu/data/sf1/parquet/web_sales]],
> selectionRoot=file:/home/hanu/data/sf1/parquet/web_sales, numFiles=1,
> numRowGroups=1, usedMetadataFile=false, columns=[`**`, `ws_ship_date_sk`,
> `ws_order_number`]]])
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
