Common subexpression elimination on propagated filters leads to bad join orders
Details
| Detail name | Value |
|---|---|
| Changelog Number | 5503 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | EXASOL 6.0.0, EXASolution 5.0.0 |
| Fix Versions | EXASOL 6.0.7 |
| Resolution Date | 2018-01-30 |
The propagation of filters over equi join conditions generates additional local filters that help to reduce computation costs of subqueries.
Example
The following example query has a local filter on table L that can be propagated to R
select * from L join R on L.id = R.id where L.id = 7;
After the propagation the query becomes
select * from L join R on L.id = R.id where L.id = 7 and R.id = 7;
The enhanced query often results in better join orders and hence faster queries.
Problem
The propagation of filters can lead to duplicated common subexpressions on the same table. In that case, the join order optimizer failed to recognise and eliminate those propagated common subexpressions.
As a consequence, the filtering effect of local filters on a table could have been significantly overestimated by the join order optimizer. Resulting join orders were often more expensive than needed.
The number of propagated filters was boosted in version 6.0 increasing the negative impact of this bug. BI tools and hierarchies of views often incorporate filter propagation.
Workaround
There is no workaround.
Configuring the Fix
With the fix of this issue we also add the option to re-enable the old behavior with commandline-parameter -eliminateDuplicateFilters=0 (set in EXAOperation). This is there for the case that more precise filter estimation might cause a worse join queue in rare cases.
Changed behavior
In some cases the fix might lead to slower queries because it causes the cost based optimizer (CBO) to prefer a different join queue which is better according to the internal model but might still be slower in reality. However, the opposite effect (better join queue) should be the more common effect. For most queries this will probably change nothing at all.
