[jira] [Created] (CALCITE-4542) Suboptimal plan is chosen when TopDownRuleDriver is enabled

["Konstantin Orlov (Jira)" <ji...@apache.org>]

Konstantin Orlov created CALCITE-4542:
-----------------------------------------

             Summary: Suboptimal plan is chosen when TopDownRuleDriver is enabled 
                 Key: CALCITE-4542
                 URL: https://issues.apache.org/jira/browse/CALCITE-4542
             Project: Calcite
          Issue Type: Bug
    Affects Versions: 1.26.0
            Reporter: Konstantin Orlov
         Attachments: dump.txt

When TopDownRuleDriver is enabled, suboptimal plan is chosen for query with join.

We have our own convention and implementation of all necessary relations. A distributed join is considered less expensive by our cost system than a single-distributed join, and the merge join is considered less expensive than nested loop if index over join condition is present. Nevertheless the merge join with a single distribution is chosen by optimizer.

The query is:

{code:java}
select e1."empid", e1."deptno" from "emps" e1 join "emps" e2 on e1."empid" = e2."empid"
{code}

Actual plan:

{code:java}
MyProject(subset=[rel#16:RelSubset#2.MY.single.[]], empid=[$0], deptno=[$1]): rowcount = 1500.0, cumulative cost = {1500.0 rows, 3000.0 cpu, 0.0 io}, id = 21
  MyMergeJoin(subset=[rel#20:RelSubset#1.MY.single.[]], condition=[=($0, $5)], joinType=[inner]): rowcount = 1500.0, cumulative cost = {150.0 rows, 0.0 cpu, 0.0 io}, id = 50
    MyExchange(subset=[rel#25:RelSubset#0.MY.single.[]], distribution=[single]): rowcount = 100.0, cumulative cost = {9210.340371976183 rows, 100.0 cpu, 0.0 io}, id = 30
      MyTableScan(subset=[rel#29:RelSubset#0.MY.any.[0]], table=[[hr, emps]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 27
    MyExchange(subset=[rel#25:RelSubset#0.MY.single.[]], distribution=[single]): rowcount = 100.0, cumulative cost = {9210.340371976183 rows, 100.0 cpu, 0.0 io}, id = 30
      MyTableScan(subset=[rel#29:RelSubset#0.MY.any.[0]], table=[[hr, emps]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 27
{code}

Expected plan is

{code:java}
MyProject(subset=[rel#16:RelSubset#2.MY.single.[]], empid=[$0], deptno=[$1]): rowcount = 1500.0, cumulative cost = {1500.0 rows, 3000.0 cpu, 0.0 io}, id = 21
  MyExchange(subset=[rel#20:RelSubset#1.MY.single.[]], distribution=[single]): rowcount = 1500.0, cumulative cost = {18420.680743952365 rows, 100.0 cpu, 0.0 io}, id = 24
    MyMergeJoin(subset=[rel#23:RelSubset#1.MY.any.[]], condition=[=($0, $5)], joinType=[inner]): rowcount = 1500.0, cumulative cost = {0.15 rows, 0.0 cpu, 0.0 io}, id = 50
      MyTableScan(subset=[rel#26:RelSubset#0.MY.hash[0].[0]], table=[[hr, emps]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 25
      MyTableScan(subset=[rel#26:RelSubset#0.MY.hash[0].[0]], table=[[hr, emps]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 25
{code}

Planner dump doesn't contain the join with proper distribution.

Reproducer could be found [here|https://github.com/korlov42/calcite/tree/derive-not-being-called-repoducer]. Please run {{org.apache.calcite.tools.PlannerTest#test}}






--
This message was sent by Atlassian Jira
(v8.3.4#803005)