You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "hujiahua (Jira)" <ji...@apache.org> on 2022/01/14 03:35:00 UTC
[jira] [Updated] (CALCITE-4958) bad performance execute plan when use dynamic parameters in a VALUES clause

     [ https://issues.apache.org/jira/browse/CALCITE-4958?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

hujiahua updated CALCITE-4958:
------------------------------
    Description: 
 

First I set SqlToRelConverter.DEFAULT_IN_SUB_QUERY_THRESHOLD = 2
And When I use dynamic parameters in query like this:
{noformat}
select * from DEPTS where NAME in ( ?, ?, ?)
{noformat}
The IN-list will convert to union three project.
{noformat}
LogicalProject(DEPTNO=[$0], NAME=[$1])
  LogicalJoin(condition=[=($1, $2)], joinType=[inner])
    LogicalTableScan(table=[[TEST, DEPTS]])
    LogicalAggregate(group=[{0}])
      LogicalUnion(all=[true])
        LogicalProject(EXPR$0=[?0])
          LogicalValues(tuples=[[{ 0 }]])
        LogicalProject(EXPR$0=[?1])
          LogicalValues(tuples=[[{ 0 }]])
        LogicalProject(EXPR$0=[?2])
          LogicalValues(tuples=[[{ 0 }]])
{noformat}
But if I not use dynamic parameters in query like this:
{noformat}
select * from DEPTS where NAME in ( 'a', 'b', 'c') 
{noformat}
The IN-list will a LogicalValues, this is what I wanted.
{noformat}
LogicalProject(DEPTNO=[$0], NAME=[$1])
  LogicalJoin(condition=[=($1, $2)], joinType=[inner])
    LogicalTableScan(table=[[TEST, DEPTS]])
    LogicalAggregate(group=[{0}])
      LogicalValues(tuples=[[{ 'a' }, { 'b' }, { 'c' }]])
{noformat}
Here is my test: // I set DEFAULT_IN_SUB_QUERY_THRESHOLD = 2
{code:java}
      final String sql = "select * from \"TEST\".\"DEPTS\" where \"NAME\" in ( ?, ?, ?)";
      final PreparedStatement statement2 =
              calciteConnection.prepareStatement(sql);

      statement2.setString(1, "Sales");
      statement2.setString(2, "Sales2");
      statement2.setString(3, "Sales3");
      final ResultSet resultSet1 = statement2.executeQuery();
{code}

  was:
First I set SqlToRelConverter.DEFAULT_IN_SUB_QUERY_THRESHOLD = 2
And When I use dynamic parameters in query like this:
{noformat}
select * from DEPTS where NAME in ( ?, ?, ?)
{noformat}
The IN-list will convert to union three project.
{noformat}
LogicalProject(DEPTNO=[$0], NAME=[$1])
  LogicalJoin(condition=[=($1, $2)], joinType=[inner])
    LogicalTableScan(table=[[TEST, DEPTS]])
    LogicalAggregate(group=[{0}])
      LogicalUnion(all=[true])
        LogicalProject(EXPR$0=[?0])
          LogicalValues(tuples=[[{ 0 }]])
        LogicalProject(EXPR$0=[?1])
          LogicalValues(tuples=[[{ 0 }]])
        LogicalProject(EXPR$0=[?2])
          LogicalValues(tuples=[[{ 0 }]])
{noformat}

But if I not use dynamic parameters in query like this:
{noformat}
select * from DEPTS where NAME in ( 'a', 'b', 'c') 
{noformat}
The IN-list will a LogicalValues, this is what I wanted.
{noformat}
LogicalProject(DEPTNO=[$0], NAME=[$1])
  LogicalJoin(condition=[=($1, $2)], joinType=[inner])
    LogicalTableScan(table=[[TEST, DEPTS]])
    LogicalAggregate(group=[{0}])
      LogicalValues(tuples=[[{ 'a' }, { 'b' }, { 'c' }]])
{noformat}

Here is my test:   // I set DEFAULT_IN_SUB_QUERY_THRESHOLD = 2
{code:java}
      final String sql = "select * from \"TEST\".\"DEPTS\" where \"NAME\" in ( ?, ?, ?)";
      final PreparedStatement statement2 =
              calciteConnection.prepareStatement(sql);

      statement2.setString(1, "Sales");
      statement2.setString(2, "Sales2");
      statement2.setString(3, "Sales3");
      final ResultSet resultSet1 = statement2.executeQuery();
{code}




> bad performance execute plan when use dynamic parameters in a VALUES clause
> ---------------------------------------------------------------------------
>
>                 Key: CALCITE-4958
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4958
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: hujiahua
>            Priority: Minor
>
>  
> First I set SqlToRelConverter.DEFAULT_IN_SUB_QUERY_THRESHOLD = 2
> And When I use dynamic parameters in query like this:
> {noformat}
> select * from DEPTS where NAME in ( ?, ?, ?)
> {noformat}
> The IN-list will convert to union three project.
> {noformat}
> LogicalProject(DEPTNO=[$0], NAME=[$1])
>   LogicalJoin(condition=[=($1, $2)], joinType=[inner])
>     LogicalTableScan(table=[[TEST, DEPTS]])
>     LogicalAggregate(group=[{0}])
>       LogicalUnion(all=[true])
>         LogicalProject(EXPR$0=[?0])
>           LogicalValues(tuples=[[{ 0 }]])
>         LogicalProject(EXPR$0=[?1])
>           LogicalValues(tuples=[[{ 0 }]])
>         LogicalProject(EXPR$0=[?2])
>           LogicalValues(tuples=[[{ 0 }]])
> {noformat}
> But if I not use dynamic parameters in query like this:
> {noformat}
> select * from DEPTS where NAME in ( 'a', 'b', 'c') 
> {noformat}
> The IN-list will a LogicalValues, this is what I wanted.
> {noformat}
> LogicalProject(DEPTNO=[$0], NAME=[$1])
>   LogicalJoin(condition=[=($1, $2)], joinType=[inner])
>     LogicalTableScan(table=[[TEST, DEPTS]])
>     LogicalAggregate(group=[{0}])
>       LogicalValues(tuples=[[{ 'a' }, { 'b' }, { 'c' }]])
> {noformat}
> Here is my test: // I set DEFAULT_IN_SUB_QUERY_THRESHOLD = 2
> {code:java}
>       final String sql = "select * from \"TEST\".\"DEPTS\" where \"NAME\" in ( ?, ?, ?)";
>       final PreparedStatement statement2 =
>               calciteConnection.prepareStatement(sql);
>       statement2.setString(1, "Sales");
>       statement2.setString(2, "Sales2");
>       statement2.setString(3, "Sales3");
>       final ResultSet resultSet1 = statement2.executeQuery();
> {code}



--
This message was sent by Atlassian Jira
(v8.20.1#820001)