exa-GeorgD
Team Exasol
Team Exasol

Starting with 7.0, Exasol offers a new Join Order optimizer. This short article provides details about the new join order optimizer and answers some common questions.


Background

Determining the best join order is a complex task and an ongoing research topic. Parts of the problem are NP-Hard (see https://doi.org/10.1145/1270.1498). In general, finding the best join order consists of three parts, namely Cardinality Estimation, Cost Model, and Plan Enumeration. Cardinality Estimation creates assumptions about database properties like data distribution in columns. The Cost Model uses these assumptions and a fixed execution plan to approximate the execution time of a join order. The Plan Enumeration searches for the execution plan with minimal costs.

For more details, the following links provide a good starting point:
* https://doi.org/10.1007/s41019-020-00149-7
* https://doi.org/10.14778/2850583.2850594
* https://doi.org/10.1007/s00778-017-0480-7

 

New Optimizer in Exasol

We built a new plan enumeration algorithm from scratch. It is faster as the previous one, inspects many more join orders, and is easier to extend in the future. It also has cleaner interfaces to the cost model and the cardinality estimation. As far as possible we kept the current behavior (Cardinality Estimation, Cost Model, join method selection, replication handling). However, it was necessary to make some slight changes due to the new interfaces.

The new join order algorithm is deactivated by default in 7.0.x and 7.1.x. 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!

 

The new optimizer is called preview and EXPERIMENTAL. Is it production ready?

This is ... complicated 😉.
Let's look at what could possibly go wrong:

  1. Long execution time of the new optimizer.
    Please create a support ticket for these cases. We will try to find a workaround and we also handle this like any other bug.
    Of course, switching to the old optimizer is also valid. If such a problem occurs, there is just one difference from other bugs:
    As we in R&D want you to use the new optimizer, we will fix them as fast as possible 😉.
  2. Non-optimal join queue with high execution time due to a bug in the new optimizer.
    Everything in issue 1 applies here. Create a Support ticket in the usual way.
  3. Non-optimal join queue with high execution time due to a valid decision in the new optimizer.
    This is the complicated case. We still want you to create a support ticket with all the necessary information. We want to collect all these cases to determine the best way to extend our optimizer in 8.0. However, we will only address these issues in Exasol 8.0.x. In older versions like 7.0.x and 7.1.x the only workaround will be to switch to the old optimizer.

At the time of writing this article, there are no known bugs that would prevent the use of the new optimizer in a production environment with Exasol 7.0.10 and above.

 

When do we have to switch to the new optimizer?
The new optimizer will be the default configuration in 8.0. It will still be possible to use the old optimizer in 8.0 and we will also provide support for it. We have not yet determined the end of support for the old optimizer. We will communicate this in advance with sufficient time to prepare.

 

But why did you call the new optimizer EXPERIMENTAL?

This happens if you allow R&D to choose the keyword and not marketing. But we will improve that, and we will switch to a better name in 8.0. Probably we will choose something like WORLDS_SHINIEST_OPTIMIZER 😁.

 

So, we in R&D want to encourage you to use the new optimizer and we are looking forward to your feedback!

8 Comments
PeterK
Xpert

Hi Georg - 

Thanks for a great summary.

I know Exasol prides itself (and rightly so) on being an self-tuning system. However, as you pointed out, some aspects of optimization are NP-hard and it's practically impossible for an optimizer to make all queries as fast as they could be.

What are your thoughts on allowing per-query optimizer hints (e.g. JOIN order or index hints) to allow users to manually address the edge cases? 

Cheers,

Peter

mwellbro
Xpert

Hi Georg,

thanks also from my side for this article - on the note of "allowing per-query optimizer" that Peter brought up: please don´t 🙂
My reasoning: Exasol did stake a claim to be tuning free(-ish) so this would run counter to that "there´s only one switch, "ON/OFF" idea.

Of course optimization is no small feat, but might I suggest ( and I´m sure your marketing colleagues could have a field day with this ) to solve this in a manner more along the lines of AI/ML ( aut idem  😉  ) ? I´d think that many of the data models that are being implemented on Exasol do vary and that a general solution to optimization without certain knowledge about "sensible join paths" in a given model is bound to be suboptimal - however I also believe that the number of "sensible join paths" would probably be somewhat finite and "learnable" in an "ML-kind-of-way" ?

Just my 2 cents.

Cheers,
Malte

exa-GeorgD
Team Exasol
Team Exasol

Hi everybody,

first of all, currently R&D does not plan to add per-query optimizer hints ;).
Still, it would be interesting for R&D to know who often you encountered queries for which the hints were absolutely necessary. We in R&D see only queries that cause problems and thus have no data about how often this really happens.

@PeterK:
How often did you encounter the need to use hints? Daily? Monthly?

@mwellbro:
Learning from previous query executions is definitely an exciting option and we will spend some time in the next couple of years to look into it ;).

DISCLAIMER: Everything below is now just my personal opinion. There are several different opinions about this topic in R&D alone 😉. If you talk to Product Management you will get even more opinions.

Personally, I think per-query hints are not the way to go. It is the task of the compiler/optimizer to figure out the best execution plan. If there are extreme problems with a query, it is invaluable for R&D to get a ticket about the issue. Only with such tickets, we can improve the compiler/optimizer. If everyone just relies on hints, the issues will remain. Additionally, providing hints is time-consuming, hard to maintain, and does not scale with a growing number of queries.

The general join ordering problem is NP-complete, but not all queries fall into this category. An insufficient cardinality estimation can also cause a non-optimal join order. In such a case, it makes sense to improve the estimation with better statistics. An incomplete cost model can also cause a non-optimal join order. For example, the use of LATERAL JOINS in combination with EXISTS is not yet part of the Exasol cost model (I am making this one up 😉). Here it is more useful to extend the cost model instead of using a query hint. R&D can cover such solvable issues, if we are aware of them. So the time is better spent on creating tickets for R&D instead of trying to manual optimize the join order 😉.

As mentioned above, I also think hints are hard to maintain. I give you a couple of examples:

  • Let us assume for a moment it is possible optimized a query with a hint. A couple of month later your data did change considerably and the hint does no longer provide you with the best execution time. Spotting the now invalid hint is hard, if the execution time does not change much despite the new data.
  • Exasol adds a execution engine feature in a new version. As a consequence, it is possible that the execution time with the hint is worse than without it as soon due to the new feature. This is also nearly impossible to detect.
  • Someone is copying your SQL script (including the hint!) for a different task. Due to the new purpose, it becomes necessary to change the query slightly, but the hint is kept in the query. In this scenario, the hint could prevent a better execution time for the new query. It is just an left over artifact of the initial copy.

In general this is an interesting discussion topic and I hope that we get additional opinions from the community and from others in R&D.

Best wishes
Georg

PeterK
Xpert

Hi @exa-GeorgD ,

You make good points there. I'm honestly on the fence as to whether it's better or not.  I usually favor the no-hints philosophy until I'm faced with a client whose report is running too slowly and who wants it addressed asap and where the root cause is a sub-optimal query plan...then I temporarily switch to the other side of the fence 🙂

For me that only happens once a month or so.

I would argue that we already /do/ have optimizer hints...they're just not official. Any time we have to (e.g.):

  • add an ORDER-BY-FALSE to force materialization,
  • or rewrite a INNER JOIN as an LEFT JOIN WHERE NOT-NULL to influence join order,
  • or rewrite criteria in such a way as to discourage/encourage use of a particular index

we are essentially adding optimizer hints and those all have the same drawbacks as you listed. In fact I would say they are worse then official hints because it's harder to find/remove them later on. At least with an official hint syntax we could search/remove/disable-via-config later on.

Here's a thought: In order to help you improve the optimizer it would be very helpful if there was an easy way to submit to Exasol the information you need to reproduce the sub-optimal plan. Currently I find it very time consuming and difficult to reproduce the data/schema necessary to reproduce the sub-optimal plan in order to submit a ticket for it.

If there was a more automated way (from ExaOp perhaps) where we could download all the necessary (and sanitized) info need for you to reproduce the plan for a given session+stmtid I think you would end up getting a lot more real-world optimizer feedback. 

And if there was an official hint syntax maybe we could just download/submit a report of all queries that used hints.

If we want to get really crazy you could have the cluster automatically rerun any query containing hints during cluster idle periods (late at night). It could run them with and without the hints and generate a report of all queries where hints appear to be slowing it down. That way we can remove the hints once the optimizer catches up.

Cheers,

Peter

mwellbro
Xpert

Hi all ,

"For me that only happens once a month or so." => ohhh how I envy you, @PeterK  😉

"
Here's a thought: In order to help you improve the optimizer it would be very helpful if there was an easy way to submit to Exasol the information you need to reproduce the sub-optimal plan. Currently I find it very time consuming and difficult to reproduce the data/schema necessary to reproduce the sub-optimal plan in order to submit a ticket for it.
" => THIS !

Cheers,
Malte

exa-MathiasHo
Community Manager
Community Manager

How often it happens to you @mwellbro? But it sounds like we have another idea for our ideation. @PeterK or @mwellbro Could you please create an idea and add specifics to it?

For example: What exactly do you expect? How would you like to submit the information? Would that require a certain support level (we have new support levels display here)? How often you would use that? What time/effort savings that'd be for you/your company?

Best regards,
Mathias

mwellbro
Xpert

I think I´ll leave it at "significantly more often than once a month" 😉

Will jot down an idea, thinking along the lines of "additional checkbox in ExaOp that when session_id is provided dumps everything support and development would need to provide a solution ( third level stats , optimizer trace, card´s , etc.,etc. ), all wrapped into a single .gz file we can upload into support/jira , no questions asked".

In terms of support levels I´d strongly see this in the silver level -> Exa running a query indefinitely is just wrong and making it easier for your customers to provide you with the necessary data as to why should be a given.

Cheers,
Malte

edit: now here´s an idea: https://community.exasol.com/t5/ideas/improve-supportupload-for-performance-related-tickets/idi-p/60...

 

PeterK
Xpert

I think it would be valuable for Exasol to accumulate a large number of these real-world slow-query scenarios in order to build testing scenarios to benchmark future optimizer improvements. So I think it would be in your (Exasol's) best interest to allow /anybody/ to upload the slow-query scenarios...even community users.  Whether or not you provide immediate response/workaround/support could then depend on the customer's support level.

Overall you can still use these test cases to improve your optimizer's test suite even if you don't provide individual support for each submitted test case. Data Driven Development 😉