Join Order from explain plan

[#1]

Hive is having an improvised explain plan with the hive.explain.user set to true. Now the indentation and arrows clearly depict the execution flow. Retrieving the join order needed a lot of back and forth references in the old style which was made as easy as following the arrows now, even with the details of mappers and reducers.

Let us take a look at one sample explain plan and retrieve the join order

Plan optimized by CBO.
Vertex dependency in root stage
Map 2 <- Map 1 (BROADCAST_EDGE), Map 4 (BROADCAST_EDGE), Map 5 (BROADCAST_EDGE), Map 6 (BROADCAST_EDGE), Map 7 (BROADCAST_EDGE), Map 8 (BROADCAST_EDGE), Map 9 (BROADCAST_EDGE)
Reducer 3 <- Map 2 (SIMPLE_EDGE)
Stage-0
  Fetch Operator
    limit:-1
    Stage-1
      Reducer 3 vectorized, llap
      File Output Operator [FS_101]
        Group By Operator [GBY_100] (rows=1 width=736)
          Output:["_col0","_col1","_col2","_col3"],aggregations:["min(VALUE._col0)","min(VALUE._col1)","min(VALUE._col2)","min(VALUE._col3)"]
        <-Map 2 [SIMPLE_EDGE] llap
          SHUFFLE [RS_45]
            Group By Operator [GBY_44] (rows=1 width=736)
              Output:["_col0","_col1","_col2","_col3"],aggregations:["min(_col1)","min(_col3)","min(_col14)","min(_col19)"]
              Map Join Operator [MAPJOIN_92] (rows=80573328660391072 width=114)
                Conds:MAPJOIN_91._col11, _col7=RS_41._col0, _col0(Inner),Output:["_col1","_col3","_col14","_col19"]
              <-Map 9 [BROADCAST_EDGE] llap
                BROADCAST [RS_41]
                  PartitionCols:_col0, _col0
                  Select Operator [SEL_23] (rows=1960358 width=104)
                    Output:["_col0","_col1"]
                    Filter Operator [FIL_86] (rows=1960358 width=104)
                      predicate:id is not null
                      TableScan [TS_21] (rows=1960358 width=104)
                        default@title,t,Tbl:COMPLETE,Col:NONE,Output:["id","title"]
              <-Map Join Operator [MAPJOIN_91] (rows=73248479012737488 width=125)
                  Conds:MAPJOIN_90._col10=RS_38._col0(Inner),Output:["_col1","_col3","_col7","_col11","_col14"]
                <-Map 8 [BROADCAST_EDGE] llap
                  BROADCAST [RS_38]
                    PartitionCols:_col0
                    Select Operator [SEL_20] (rows=2 width=40)
                      Output:["_col0"]
                      Filter Operator [FIL_85] (rows=2 width=40)
                        predicate:((UDFToString(role) = 'actress') and id is not null)
                        TableScan [TS_18] (rows=4 width=40)
                          default@role_type,rt,Tbl:COMPLETE,Col:NONE,Output:["id","role"]
                <-Map Join Operator [MAPJOIN_90] (rows=66589524931926816 width=138)
                    Conds:MAPJOIN_89._col0, _col6=RS_35._col0, _col0(Inner),Output:["_col1","_col3","_col7","_col10","_col11","_col14"]
                  <-Map 7 [BROADCAST_EDGE] llap
                    BROADCAST [RS_35]
                      PartitionCols:_col0, _col0
                      Select Operator [SEL_17] (rows=764741 width=105)
                        Output:["_col0","_col1"]
                        Filter Operator [FIL_84] (rows=764741 width=105)
                          predicate:((UDFToString(gender) = 'f') and (name like '%An%') and id is not null)
                          TableScan [TS_15] (rows=3058966 width=105)
                            default@name,n,Tbl:COMPLETE,Col:NONE,Output:["id","name","gender"]
                  <-Map Join Operator [MAPJOIN_89] (rows=60535930444216152 width=152)
                      Conds:MAPJOIN_88._col7, _col4=RS_32._col0, _col1(Inner),Output:["_col0","_col1","_col3","_col6","_col7","_col10","_col11"]
                    <-Map 6 [BROADCAST_EDGE] llap
                      BROADCAST [RS_32]
                        PartitionCols:_col0, _col1
                        Select Operator [SEL_14] (rows=11636372 width=8)
                          Output:["_col0","_col1"]
                          Filter Operator [FIL_83] (rows=11636372 width=8)
                            predicate:(movie_id is not null and company_id is not null)
                            TableScan [TS_12] (rows=11636372 width=8)
                              default@movie_companies,mc,Tbl:COMPLETE,Col:NONE,Output:["movie_id","company_id"]
                    <-Map Join Operator [MAPJOIN_88] (rows=55032662847395656 width=167)
                        Conds:MAPJOIN_87._col0, _col2=RS_29._col0, _col2(Inner),Output:["_col0","_col1","_col3","_col4","_col6","_col7","_col10"]
                      <-Map 4 [BROADCAST_EDGE] llap
                        BROADCAST [RS_29]
                          PartitionCols:_col0, _col2
                          Select Operator [SEL_8] (rows=6110483 width=116)
                            Output:["_col0","_col1","_col2","_col4"]
                            Filter Operator [FIL_81] (rows=6110483 width=116)
                              predicate:((note) IN ('(voice)', '(voice: Japanese version)', '(voice) (uncredited)', '(voice: English version)') and person_id is not null and person_role_id is not null and movie_id is not null and role_id is not null)
                              TableScan [TS_6] (rows=12220966 width=116)
                                default@cast_info,ci,Tbl:COMPLETE,Col:NONE,Output:["person_id","movie_id","person_role_id","note","role_id"]
                      <-Map Join Operator [MAPJOIN_87] (rows=50029692413268030 width=184)
                          Conds:(Inner),(Inner),Output:["_col0","_col1","_col2","_col3","_col4"]
                        <-Map 1 [BROADCAST_EDGE] llap
                          BROADCAST [RS_24]
                            Select Operator [SEL_2] (rows=701938 width=104)
                              Output:["_col0","_col1"]
                              Filter Operator [FIL_79] (rows=701938 width=104)
                                predicate:person_id is not null
                                TableScan [TS_0] (rows=701938 width=104)
                                  default@aka_name,an,Tbl:COMPLETE,Col:NONE,Output:["person_id","name"]
                        <-Map 5 [BROADCAST_EDGE] llap
                          BROADCAST [RS_26]
                            Select Operator [SEL_11] (rows=34365 width=259)
                              Output:["_col0"]
                              Filter Operator [FIL_82] (rows=34365 width=259)
                                predicate:((UDFToString(country_code) = '[us]') and id is not null)
                                TableScan [TS_9] (rows=68731 width=259)
                                  default@company_name,cn,Tbl:COMPLETE,Col:NONE,Output:["id","country_code"]
                        <-Select Operator [SEL_5] (rows=2074019 width=104)
                            Output:["_col0","_col1"]
                            Filter Operator [FIL_80] (rows=2074019 width=104)
                              predicate:id is not null
                              TableScan [TS_3] (rows=2074019 width=104)
                                default@char_name,chn,Tbl:COMPLETE,Col:NONE,Output:["id","name"]

1. Start reading from the bottom

2. Look for the keyword 'TableScan', the row below it will have the details of the tables being joined

3. Look for the keyword 'Join Operator', the indent of this line will tell us the tables that are part of this join

4. This joined table group will form candidate for another join if the line starts with '<-'

5. Similarly keep going to top to get the complete join order

The join order in the above example boils down to (title(t),(((movie_companies(mc),(cast_info(ci),(company_name(cn),aka_name(an),char_name(chn)))),name(n)),role_type(rt)))