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

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 3.0
• Component/s: sql
• Labels: ignite-3
• Assignee: Andrey Mashenkov
• Reporter: Konstantin Orlov
• Created: 21/Feb/24 11:57
• Updated: 04/Sep/24 16:08
• Resolved: 08/Apr/24 10:44

Description

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

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

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:

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

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.

Issue Links

• fixes: IGNITE-20083 Sql. Investigate cost calculation for MAP/REDUCE aggregate.
• fixes: IGNITE-20095 Sql. Map part of MAP/REDUCE aggregate sometimes can not be moved past Exchange.
• is related to: IGNITE-21948 Rethink distributed aggregates converter rules.
• is related to: IGNITE-21954 Reuse Calcite map-reduce aggregates code.
• relates to: IGNITE-21579 Sql. Investigate performance bottlenecks in sql engine
• split to: IGNITE-21897 Sql. Invalid collation passthrough for sorted aggregates.
• links to: GitHub Pull Request #3552