Join order optimizer improvements
Details
| Detail name | Value |
|---|---|
| Changelog Number | 6463 |
| Type | Improvement |
| Status | Resolved |
| Fix Versions | Exasol 6.1.3 |
| Resolution Date | 2019-05-13 |
Background
The join order optimizer determines the types of joins as well as the order of joins in a pipeline. The optimizer uses cardinality estimates on basis of column statistics and a physical cost model to determine the costs of join orders.
In case of many joins, only a subset of the search space will be processed. The best join order can be missed in such cases.
Improvement
The join order optimizer has been improved in several ways
- more precise selection of caching global joins over non-caching ones
- better handling of computation imbalances in the pipeline
- faster finishing of join order search in case of very many tables joined
- improved multiple join attribute cardinality estimation
Although the above changes are supposed to generally improve query performance, it is possible that some queries take longer than before. This should be accepted with an improved average query duration in mind.
Workaround
The former optimizer behaviour can be reactivated using a database parameter.
