New Join Order Optimization Algorithm

Detail name	Value
Changelog Number	6404
Type	New Feature
Status	Resolved
Fix Versions	Exasol 7.0.0
Resolution Date	2020-09-11

Background

Join order optimization is a complex problem and an active area of research. While most Exasol users do not experience any problems with join orderings in their productive workloads, we evaluate alternative approaches and make the most promising ones available to all Exasol users.

Feature Description

We implemented a new join order optimization algorithm while keeping the semantic join order components mainly the same (cost model, join method selection, replication handling). The new algorithm is faster and inspects much more join orders to find the best for the current query. Furthermore, it is more easy to extend and test so it will be a good basis for future improvements of semantic join order components.

The new join order algorithm is deactivated by default. It can be enabled per session:

CONTROL SET JOIN OPTIMIZER EXPERIMENTAL;

Alternatively, it can be enabled database-wide using the command-line parameter -joinOrderMethod=1. In that case individual sessions can switch back to the old default optimizer:

CONTROL SET JOIN OPTIMIZER DEFAULT;

To clear the session setting and rely on database-wide setting, use:

CONTROL CLEAR JOIN OPTIMIZER;

Changing the session setting doesn't affect queries already stored in the query cache!

Benefits

Faster join order optimization (i.e. faster compile time) for complex queries and occasionally faster query execution through improved join queues.

Restrictions

We can't guarantee that every single query benefits from the new optimizer. Users might encounter individual queries which work slower than with the old optimizer.

Notes

The new optimizer will become the default in Exasol 8.0. By releasing the new one optionally for version 7.0. we aim to get early  feedback from our users. Please feel free to test the new optimizer and provide feedback via Exasol support