Optimized GROUP BY Key Reduction

Detail name	Value
Changelog Number	14657
Type	Improvement
Status	Resolved
Fix Versions	Exasol 8.0.3
Resolution Date	2022-06-10

Background

Previously, only exact duplicates in the GROUP BY clause were removed from grouping keys in a SQL statement to improve performance. For example: 

-- the second item_name is removed
select
  item_name,
  'total size' as measure_name,
  sum(item_size) as measure_value
from test_item
group by
  item_name, 
  item_name

Improvement

Increased performance of GROUP BY operations by detecting and reducing the set of columns in the GROUP BY clause.  This improvement can also be beneficial for queries containing DISTINCT.

The optimizations include:

1 - Remove Constant Expressions

Constant expressions obviously do not attribute to the grouping information and can summarily be removed.
Background: Often, such keys are added by front-ends or query generators in an effort to be as compatible as possible across different systems and dialects.

Example

-- eliminates constant from group by
select
  item_name,
  'total size' as measure_name,
  sum(item_size) as measure_value
from test_item
group by
  item_name, 
  'total size'

in this case, the 'total size' expression can be removed from group by.

The same of course applies to more complex constant expressions and scalar subselects within the group-by clause.

2 - Functional Dependencies

A slightly more complex case is when expressions build on top of each other, also not providing additional key diversity.

Examples

-- eliminates year-expression
group by
  day_date,
  year(day_date) 

-- eliminates case/when
group by
    result_score,
    case when result_score < 100 then 'failed' else 'passed' end

Again, the expressions can be more complex and even span multiple contributing expressions and tables.

Please note that this detection is limited to information present in the SQL syntax/semantic; implicit dependencies in data (eg. zip-code -> state -> country) can not be detected and leveraged at this point.

3 - Column Uniqueness

When a column or a set of columns is unique within its source table or subselect, all other columns and deterministic expressions based on the same table can be considered 'functionally dependent'.

Examples

-- Removes customer_name and customer_address because customer_id is unique
select ...
from customer
-- remove name and address if ID is known to be unique
group by customer_id, customer_name, customer_address;


with k1 as (
    select item_name, sum(item_size) as total_size
    from test_item
    group by item_name
)
select item_name, total_size
from k1
-- removes total_size because item_name is unique within k1
group by item_name, total_size;

In case of views and subselects, the uniqueness information is provided by the contained GROUP BY key (if any).
In case of actual tables, uniqueness is inferred from existing indices. Suitable indices are for example automatically built for any enabled primary key constraints.

 

 The following situations prevent all of the above optimizations:

• CONNECT BY
• GROUPING SETS (incl. CUBE and ROLLUP)
• EMIT scripts
• correlated subselects in the select list