Outdated mininum and maximum column statistics lead to bad join orders
Details
| Detail name | Value |
|---|---|
| Changelog Number | 5222 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | EXASOL 6.0.0, EXASolution 5.0.0 |
| Fix Versions | EXASOL 6.0.5 |
| Resolution Date | 2017-10-26 |
Situation
Column statistics (distinct estimates, minimum & maximum values) are used by the join order optimizer to minimize pipeline costs. The minimum and maximum values are used to estimate filtering effects of joins based on partially or even disjoint join attribute overlapping.
Column statistics are maintained as part of DML statements. They are only recomputed if a significant amount of data has been changed since the last computation. Therefore, the minimum and maximum values may be out-of-date.
Problem
The use of outdated join attribute ranges in join order optimization can lead to suboptimal join orders. Hence, when join attribute ranges are considered disjoint, the error may result in bad join orders. Disjoint join attribute ranges are sometimes produced by the materialization of strong filters using ORDER BY FALSE, aggregations or certain outer joins.
Improvement
The join filtering estimation based on minimum and maximum values has been disabled by default.
Workarounds
There are a number of workarounds:
- avoid the materialization of strong filters using ORDER BY FALSE
- run ANALYZE TABLE <table> REFRESH STATISTICS on affected tables
- disable minimum & maximum based join order optimization by setting the parameter
-disableMinMaxSelectivityEstimation=1
When you find your query performance relying on those estimations, you may re-activate the join filter estimation by setting the following parameter
-enableJoinSelectivityEstimation=1
