[jira] [Created] (IGNITE-21580) Sql. Unable to optimise query using only two phase aggregates

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

Konstantin Orlov created IGNITE-21580:
-----------------------------------------

             Summary: Sql. Unable to optimise query using only two phase aggregates
                 Key: IGNITE-21580
                 URL: https://issues.apache.org/jira/browse/IGNITE-21580
             Project: Ignite
          Issue Type: Bug
          Components: sql
            Reporter: Konstantin Orlov


As for now, query planner returns following plan for q1 from TPC H suite:

{code:java}
IgniteColocatedSortAggregate
  IgniteExchange(distribution=[single])
    IgniteSort
      IgniteIndexScan(index=[L_SD], searchBounds=[[RangeBounds [lowerBound=null, upperBound=-(1998-12-01, 7776000000:INTERVAL DAY), lowerInclude=true, upperInclude=true]]])
{code}

The first problem is it's not even optimal variant from possible ones. By simply excluding {{SortAggregateConverterRule.COLOCATED}} from planning phase, we will get plan as follow:

{code:java}
IgniteSort
  IgniteColocatedHashAggregate
    IgniteExchange(distribution=[single])
      IgniteIndexScan(index=[L_SD], searchBounds=[[RangeBounds [lowerBound=null, upperBound=-(1998-12-01, 7776000000:INTERVAL DAY), lowerInclude=true, upperInclude=true]]])
{code}

Latter plan is executed ~40% faster than the first one.

Seems, it's possible to reduce time even further by taking an advantage of two-phase aggregates, but disabling both version of colocated aggregates results in an exception during planning phase.

Within this ticket, let's address the issue preventing optimiser from usage of two-phase aggregates, and also tweak cost function to make optimiser choose better plan.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)