Uploaded image for project: 'Tajo'
  1. Tajo
  2. TAJO-903

Some left outer join cases are not optimized as the broadcast join.

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.9.0
    • Component/s: None
    • Labels:
      None

      Description

      The next query has three small tables and expected broadcast join, but not.

      select count(*) from large1 
      left outer join large2 on large1_id = large2_id
      left outer join small1 on large1_id = small1_id
      left outer join small2 on large1_id = small2_id
      left outer join small3 on large1_id = small3_id
      

      The next is the upper query's plan.

      |-eb_1404411535695_0000_000011
         |-eb_1404411535695_0000_000010
            |-eb_1404411535695_0000_000009 (join)
               |-eb_1404411535695_0000_000008 (small)
               |-eb_1404411535695_0000_000007 (join)
                  |-eb_1404411535695_0000_000006 (small)
                  |-eb_1404411535695_0000_000005 (join)
                     |-eb_1404411535695_0000_000004 (small)
                     |-eb_1404411535695_0000_000003 (join)
                        |-eb_1404411535695_0000_000002 (large)
                        |-eb_1404411535695_0000_000001 (large)
      

      Optimized plan should be the next.

      |-eb_1404411906426_0000_000005
         |-eb_1404411906426_0000_000004
            |-eb_1404411906426_0000_000003 (broadcast small1, small2, small3)
               |-eb_1404411906426_0000_000002 (large)
               |-eb_1404411906426_0000_000001 (large)
      

        Activity

        Hide
        hudson Hudson added a comment -

        SUCCESS: Integrated in Tajo-master-build #281 (See https://builds.apache.org/job/Tajo-master-build/281/)
        TAJO-903: Some left outer join cases are not optimized as the broadcast join. (Hyoungjun Kim via hyunsik) (hyunsik: rev a5d14f8082fed000835520df6c9f12476039aea1)

        • tajo-core/src/test/java/org/apache/tajo/master/querymaster/TestQueryUnitStatusUpdate.java
        • tajo-core/src/main/java/org/apache/tajo/worker/Task.java
        • CHANGES
        • tajo-core/src/test/resources/queries/TestJoinBroadcast/testBroadcastSubquery.sql
        • tajo-core/src/main/java/org/apache/tajo/engine/planner/logical/JoinNode.java
        • tajo-core/src/test/resources/results/TestJoinBroadcast/testLeftOuterJoin2.result
        • tajo-core/src/test/java/org/apache/tajo/engine/query/TestJoinBroadcast.java
        • tajo-core/src/test/resources/queries/TestJoinBroadcast/testLeftOuterJoin3.sql
        • tajo-core/src/main/java/org/apache/tajo/master/DefaultTaskScheduler.java
        • tajo-core/src/test/resources/queries/TestJoinOnPartitionedTables/testFilterPushDownPartitionColumnCaseWhen.sql
        • tajo-core/src/main/java/org/apache/tajo/worker/TaskAttemptContext.java
        • tajo-core/src/main/java/org/apache/tajo/engine/planner/logical/ScanNode.java
        • tajo-core/src/main/java/org/apache/tajo/engine/planner/BroadcastJoinPlanVisitor.java
        • tajo-core/src/main/java/org/apache/tajo/master/querymaster/Repartitioner.java
        • tajo-core/src/main/java/org/apache/tajo/engine/planner/BroadcastJoinMarkCandidateVisitor.java
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/global/TestBroadcastJoinPlan.java
        • tajo-core/src/test/resources/queries/TestJoinBroadcast/create_orders_large_ddl.sql
        • tajo-core/src/main/java/org/apache/tajo/engine/planner/PhysicalPlannerImpl.java
        • tajo-core/src/test/resources/results/TestJoinBroadcast/testLeftOuterJoin3.result
        • tajo-core/src/main/java/org/apache/tajo/engine/planner/global/GlobalPlanner.java
        • tajo-core/src/test/java/org/apache/tajo/master/TestExecutionBlockCursor.java
        • tajo-core/src/test/resources/queries/TestJoinBroadcast/testLeftOuterJoin2.sql
        • tajo-core/src/test/resources/results/TestJoinBroadcast/testBroadcastSubquery.result
        • tajo-core/src/test/resources/dataset/TestJoinBroadcast/orders_large/orders.tbl
        • tajo-core/src/main/java/org/apache/tajo/master/querymaster/SubQuery.java
        Show
        hudson Hudson added a comment - SUCCESS: Integrated in Tajo-master-build #281 (See https://builds.apache.org/job/Tajo-master-build/281/ ) TAJO-903 : Some left outer join cases are not optimized as the broadcast join. (Hyoungjun Kim via hyunsik) (hyunsik: rev a5d14f8082fed000835520df6c9f12476039aea1) tajo-core/src/test/java/org/apache/tajo/master/querymaster/TestQueryUnitStatusUpdate.java tajo-core/src/main/java/org/apache/tajo/worker/Task.java CHANGES tajo-core/src/test/resources/queries/TestJoinBroadcast/testBroadcastSubquery.sql tajo-core/src/main/java/org/apache/tajo/engine/planner/logical/JoinNode.java tajo-core/src/test/resources/results/TestJoinBroadcast/testLeftOuterJoin2.result tajo-core/src/test/java/org/apache/tajo/engine/query/TestJoinBroadcast.java tajo-core/src/test/resources/queries/TestJoinBroadcast/testLeftOuterJoin3.sql tajo-core/src/main/java/org/apache/tajo/master/DefaultTaskScheduler.java tajo-core/src/test/resources/queries/TestJoinOnPartitionedTables/testFilterPushDownPartitionColumnCaseWhen.sql tajo-core/src/main/java/org/apache/tajo/worker/TaskAttemptContext.java tajo-core/src/main/java/org/apache/tajo/engine/planner/logical/ScanNode.java tajo-core/src/main/java/org/apache/tajo/engine/planner/BroadcastJoinPlanVisitor.java tajo-core/src/main/java/org/apache/tajo/master/querymaster/Repartitioner.java tajo-core/src/main/java/org/apache/tajo/engine/planner/BroadcastJoinMarkCandidateVisitor.java tajo-core/src/test/java/org/apache/tajo/engine/planner/global/TestBroadcastJoinPlan.java tajo-core/src/test/resources/queries/TestJoinBroadcast/create_orders_large_ddl.sql tajo-core/src/main/java/org/apache/tajo/engine/planner/PhysicalPlannerImpl.java tajo-core/src/test/resources/results/TestJoinBroadcast/testLeftOuterJoin3.result tajo-core/src/main/java/org/apache/tajo/engine/planner/global/GlobalPlanner.java tajo-core/src/test/java/org/apache/tajo/master/TestExecutionBlockCursor.java tajo-core/src/test/resources/queries/TestJoinBroadcast/testLeftOuterJoin2.sql tajo-core/src/test/resources/results/TestJoinBroadcast/testBroadcastSubquery.result tajo-core/src/test/resources/dataset/TestJoinBroadcast/orders_large/orders.tbl tajo-core/src/main/java/org/apache/tajo/master/querymaster/SubQuery.java
        Hide
        hyunsik Hyunsik Choi added a comment -

        committed it to master branch. Thank you for your contribution!

        Show
        hyunsik Hyunsik Choi added a comment - committed it to master branch. Thank you for your contribution!
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user asfgit closed the pull request at:

        https://github.com/apache/tajo/pull/56

        Show
        githubbot ASF GitHub Bot added a comment - Github user asfgit closed the pull request at: https://github.com/apache/tajo/pull/56
        Hide
        hyunsik Hyunsik Choi added a comment - - edited

        I've changed the issue title for more exact meaning.

        Show
        hyunsik Hyunsik Choi added a comment - - edited I've changed the issue title for more exact meaning.
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user hyunsik commented on the pull request:

        https://github.com/apache/tajo/pull/56#issuecomment-48448719

        +1

        The patch looks good to me. It includes enough unit tests to verify added features. In addition, I added some of explanation to the source code. I'll commit it shortly.

        Show
        githubbot ASF GitHub Bot added a comment - Github user hyunsik commented on the pull request: https://github.com/apache/tajo/pull/56#issuecomment-48448719 +1 The patch looks good to me. It includes enough unit tests to verify added features. In addition, I added some of explanation to the source code. I'll commit it shortly.
        Hide
        githubbot ASF GitHub Bot added a comment -

        GitHub user babokim opened a pull request:

        https://github.com/apache/tajo/pull/56

        TAJO-903: Left outer join should be optimized by the broadcast join.

        You can merge this pull request into a Git repository by running:

        $ git pull https://github.com/babokim/tajo TAJO-903

        Alternatively you can review and apply these changes as the patch at:

        https://github.com/apache/tajo/pull/56.patch

        To close this pull request, make a commit to your master/trunk branch
        with (at least) the following in the commit message:

        This closes #56


        commit dc1770aacb83ecaebc0679bd7a4b7b8dbf322f95
        Author: 김형준 <babokim@babokim-macbook-pro.local>
        Date: 2014-06-11T01:22:29Z

        enhancement broadcast join in the case of left outer join

        commit e892f6ddb06a047aa6e7c6e9b30cb1ee0535d612
        Author: 김형준 <babokim@babokim-macbook-pro.local>
        Date: 2014-06-11T15:48:39Z

        Add more testcase for broadcast join

        commit 82297c2af55985a8a79b4e9749df5ea50a3e50e0
        Author: 김형준 <babokim@babokim-mbp.server.gruter.com>
        Date: 2014-06-30T12:16:37Z

        Add node.setCandidateBroadcast(true) in BroadcastJoinPlanVisitor

        commit 840f78a54cddc155cd0c06575903378b17834ae9
        Author: 김형준 <babokim@babokim-macbook-pro.local>
        Date: 2014-06-30T16:31:15Z

        Merge branch 'master' of https://git-wip-us.apache.org/repos/asf/tajo

        Conflicts:
        tajo-core/src/main/java/org/apache/tajo/engine/planner/global/GlobalPlanner.java
        tajo-core/src/main/java/org/apache/tajo/master/querymaster/Repartitioner.java
        tajo-core/src/test/java/org/apache/tajo/engine/query/TestJoinBroadcast.java

        commit 8a6d0e7acd9c35d029d5ad31ea213e3d30f3441b
        Author: 김형준 <babokim@babokim-macbook-pro.local>
        Date: 2014-07-03T18:35:14Z

        TAJO-903: Left outer join should be optimized by the broadcast join.

        commit 7a93d846a282e5b643e41822fe5deac535b84eb5
        Author: 김형준 <babokim@babokim-macbook-pro.local>
        Date: 2014-07-03T18:35:36Z

        Merge branch 'master' of https://git-wip-us.apache.org/repos/asf/tajo

        commit e5e3877e62022aa666c2c43abefe714db22447dd
        Author: 김형준 <babokim@babokim-macbook-pro.local>
        Date: 2014-07-03T19:08:22Z

        TAJO-903: Left outer join should be optimized by the broadcast join.


        Show
        githubbot ASF GitHub Bot added a comment - GitHub user babokim opened a pull request: https://github.com/apache/tajo/pull/56 TAJO-903 : Left outer join should be optimized by the broadcast join. You can merge this pull request into a Git repository by running: $ git pull https://github.com/babokim/tajo TAJO-903 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/tajo/pull/56.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #56 commit dc1770aacb83ecaebc0679bd7a4b7b8dbf322f95 Author: 김형준 <babokim@babokim-macbook-pro.local> Date: 2014-06-11T01:22:29Z enhancement broadcast join in the case of left outer join commit e892f6ddb06a047aa6e7c6e9b30cb1ee0535d612 Author: 김형준 <babokim@babokim-macbook-pro.local> Date: 2014-06-11T15:48:39Z Add more testcase for broadcast join commit 82297c2af55985a8a79b4e9749df5ea50a3e50e0 Author: 김형준 <babokim@babokim-mbp.server.gruter.com> Date: 2014-06-30T12:16:37Z Add node.setCandidateBroadcast(true) in BroadcastJoinPlanVisitor commit 840f78a54cddc155cd0c06575903378b17834ae9 Author: 김형준 <babokim@babokim-macbook-pro.local> Date: 2014-06-30T16:31:15Z Merge branch 'master' of https://git-wip-us.apache.org/repos/asf/tajo Conflicts: tajo-core/src/main/java/org/apache/tajo/engine/planner/global/GlobalPlanner.java tajo-core/src/main/java/org/apache/tajo/master/querymaster/Repartitioner.java tajo-core/src/test/java/org/apache/tajo/engine/query/TestJoinBroadcast.java commit 8a6d0e7acd9c35d029d5ad31ea213e3d30f3441b Author: 김형준 <babokim@babokim-macbook-pro.local> Date: 2014-07-03T18:35:14Z TAJO-903 : Left outer join should be optimized by the broadcast join. commit 7a93d846a282e5b643e41822fe5deac535b84eb5 Author: 김형준 <babokim@babokim-macbook-pro.local> Date: 2014-07-03T18:35:36Z Merge branch 'master' of https://git-wip-us.apache.org/repos/asf/tajo commit e5e3877e62022aa666c2c43abefe714db22447dd Author: 김형준 <babokim@babokim-macbook-pro.local> Date: 2014-07-03T19:08:22Z TAJO-903 : Left outer join should be optimized by the broadcast join.

          People

          • Assignee:
            hjkim Hyoungjun Kim
            Reporter:
            hjkim Hyoungjun Kim
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development