Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1494

Inefficient plan for correlated sub-queries

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.12.0
    • Component/s: core
    • Labels:

      Description

      For co-related queries such as

       select sal from emp where empno IN (select deptno from dept where emp.job = dept.name) 

      Calcite generates following plan (SubqueryRemove Rule + Decorrelation)

      LogicalProject(SAL=[$5])
        LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
          LogicalJoin(condition=[AND(=($2, $10), =($0, $9))], joinType=[inner])
            LogicalTableScan(table=[[CATALOG, SALES, EMP]])
            LogicalAggregate(group=[{0, 1}])
              LogicalProject(DEPTNO=[$0], JOB=[$1])
                LogicalProject(DEPTNO=[$0], JOB=[$2])
                  LogicalJoin(condition=[=($2, $1)], joinType=[inner])
                    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
                    LogicalAggregate(group=[{0}])
                      LogicalProject(JOB=[$2])
                        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      

      As you can notice there is a outer table scan (EMP in this case) to retrieve all distinct values for co-related column (EMP.JOB here), which is then joined with inner table (DEPT).
      I am not sure why is this step required. After this join Calcite is anyway doing group by to generate all distinct values for correlated and result column (DEPTNO, JOB) which is then joined with outer table.
      I think the scan + join of outer table with inner table to generate co-rrelated values is un-necessary and is not required.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                vgarg Vineet Garg
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: