[ 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/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} 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. : 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`]]]) > 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 > 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/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} -- This message was sent by Atlassian JIRA (v7.6.3#76005)