Uploaded image for project: 'Apache Trafodion (Retired)'
  1. Apache Trafodion (Retired)
  2. TRAFODION-3031

Query with nested subqueries chooses bad plan



    • Improvement
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 2.3
    • 2.3
    • sql-cmp
    • None


      The attached files demonstrate the problem. The file jira.sql is a script that reproduces the problem, while jira.log is a sqlci showing the results.

      The query in question does an IN-subquery from T1 to T2, then T2 has an =-subquery back to T1. T2 contains two indexes, one each on the join columns.

      The default plan uses a hybrid hash join of T1 to T2 and is very slow. It does a full scan of both T1 and T2.

      If we set CQD SEMIJOIN_TO_INNERJOIN_TRANSFORMATION 'ON', the plan is a little bit better. We get a nested join of T1 to T2. But it is inefficient; we still do a full scan of T2.

      If we rename the index T2A to T2Y, and we still have the CQD set, we get a good nested join plan that uses the index T2Y and reads just one row at each level. This is very fast.

      So, there are two issues here.

      1. We could do a better job of deciding when to do the semi-join to join transformation. When the inner table is small, it is profitable to do this.
      2. The index elimination logic is mistakenly eliminating index T2A so the Optimizer misses a chance to use it and so does not find the efficient nested join plan.jira.log


        1. jira.sql
          2 kB
          Dave Birdsall
        2. jira.log
          8 kB
          Dave Birdsall

        Issue Links



              dbirdsall Dave Birdsall
              dbirdsall Dave Birdsall
              0 Vote for this issue
              2 Start watching this issue