Massive slowdown of aggregation query with many aggregation columns
Details
| Detail name | Value |
|---|---|
| Changelog Number | 9121 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 6.2.0 |
| Fix Versions | Exasol 7.0.0, Exasol 6.2.3 |
| Resolution Date | 2019-12-11 |
Background
Exasol aggregation algorithms use fast hash-based algorithms in heap memory and scalable sort-based algorithms in TEMP. Heap structures (so called group buffers) store fixed and variable aggregation data and are flushed into TEMP storage when their maximum size of a few MB is reached.
Problem
For queries with over a hundred fixed and variable group key and aggregation columns the performance of GROUP BY can break down a factor of 100 and more. This slowdown is caused by group buffers being flushed too often (bug).
Workaround
The following database parameter increases the group buffer size to 16 MB (default is 8 MB)
-group_buffer_size=16000
Although there is no guarantee that this will solve the performance issue there is still a good chance for it.
