Underestimation of join cardinalities in case of multiple join attributes
Details
| Detail name | Value |
|---|---|
| Changelog Number | 5266 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | EXASOL 6.0.0, EXASolution 5.0.0 |
| Fix Versions | EXASOL 6.0.7 |
| Resolution Date | 2018-03-02 |
The join order optimizer determines the order of joins in a pipeline. Typically, strong local filters and filtering joins are pushed to the front to reduce overall rows in the pipeline. To determine the cardinality of a join the number of distinct value combinations of all join attributes are estimated.
Problem
In case of multiple join attributes, the distinct value estimation assumed uncorrelated values resulting in very high distinct estimations.
As a consequence, joins were treated as filtering joins, ending up in front of the pipeline. The resulting query plans were often much more expensive than needed.
Improvement
The distinct values estimation of multiple join attributes is changed. The default attribute correlation will be the geometric mean between a total correlation and a total uncorrelation of join attributes.
The correlation type can be changed by database parameter.
Workaround
There is no workaround.
