[ http://issues.apache.org/jira/browse/DERBY-1357?page=all ]
A B updated DERBY-1357:
-----------------------
Derby Info: [Patch Available, Release Note Needed] (was: [Patch Available])
Possible RELEASE NOTE for this fix is as follows, based on suggestions from
Bryan in the following thread:
http://article.gmane.org/gmane.comp.apache.db.derby.devel/23875
<begin_release_note>
DERBY-1357: Short-circuit logic in optimizer appears to be incorrect.
Changes have been made to prevent the optimizer from spending time
optimizing/evaluating join orders that it already knows are bad.
WHAT CHANGED
The optimizer will now abandon sub-optimal join orders as soon as it realizes
that they cost more than the best join order so far.
This fix also ensures that, in the case of short-circuited join orders, Derby
will still generate (and execute) an overall plan that matches the "best path"
decisions made by the optimizer--which was not always the case prior to these
changes.
SYMPTOM
Execution performance of large queries (esp. those with nested subqueries
and/or with large FROM clauses) may change. The expectation is that the new
(10.2) query plans will show improved performance over the old ones.
CAUSE
Since the optimizer is now spending less time evaluating sub-optimal join
orders, it is possible that it will be able to try out more join orders before
optimizer "timeout" occurs. As a result the optimizer can sometimes find
better plans than it did in earlier versions of Derby.
SOLUTION
This was an intentional change to fix behavior that was not working correctly
in earlier versions of Derby. The expectation is that the new behavior--and the
subsequent query plans--will lead to improved performance over the old ones, so
no further solution is required.
WORKAROUND
There is no way to disable/workaround this new behavior since the symptom as
described above is a good one for Derby.
That said, any user who notices a negative performance change after moving to
Derby 10.2, and who believes that the difference in performance is related to
this optimizer change, is encouraged to visit the following "performance
diagnosis" page and to follow up with his/her findings on the Derby mailing
lists:
http://wiki.apache.org/db-derby/PerformanceDiagnosisTips
<end_release_note>
> Short-circuit logic in optimizer appears to be incorrect...
> -----------------------------------------------------------
>
> Key: DERBY-1357
> URL: http://issues.apache.org/jira/browse/DERBY-1357
> Project: Derby
> Issue Type: Bug
> Components: Performance
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.0.0, 10.1.2.0,
> 10.1.1.1, 10.1.1.2, 10.1.2.1, 10.1.2.2, 10.1.2.3, 10.1.2.4
> Reporter: A B
> Assigned To: A B
> Priority: Minor
> Fix For: 10.2.0.0
>
> Attachments: d1357_v1.patch, d1357_v1.stat
>
>
> When considering different join orders for the FROM tables in a query, the
> optimizer will decide to give up on a join order midway through if the cost
> of that (partial) join order is already higher than the cost of some other
> *complete* join order that the optimizer previously found. This
> "short-circuiting" of a join order can save compilation time.
> That said, the logic to perform this "short-circuit" of a join order is
> currently as follows (from OptimizerImpl.java):
> /*
> ** Pick the next table in the join order, if there is an unused position
> ** in the join order, and the current plan is less expensive than
> ** the best plan so far, and the amount of time spent optimizing is
> ** still less than the cost of the best plan so far, and a best
> ** cost has been found in the current join position. Otherwise,
> ** just pick the next table in the current position.
> */
> boolean joinPosAdvanced = false;
> if ((joinPosition < (numOptimizables - 1)) &&
> ((currentCost.compare(bestCost) < 0) ||
> (currentSortAvoidanceCost.compare(bestCost) < 0)) &&
> ( ! timeExceeded )
> )
> {
> ...
> }
> There are two "current costs" in this statement: one for the cost if the
> optimizer is calculating a "sort avoidance" plan (which it does if there is a
> required row ordering on the results) and one if it is calculating a plan for
> which row order is not important.
> I admit that I'm not all that familiar with what goes on with the costing of
> a sort-avoidance plan, but inspection of the code shows that, when there is
> no required row ordering--i.e. when we aren't looking for a sort-avoidance
> plan--the cost field of currentSortAvoidanceCost will always be 0.0d. That in
> turn means that in the above "if" statement, the check for
> ((currentCost.compare(bestCost) < 0) ||
> (currentSortAvoidanceCost.compare(bestCost) < 0))
> will always return true (because bestCost should--in theory--always be
> greater than 0.0d). Thus, in the case where we don't have a required row
> ordering, the short-circuit logic will fail even if currentCost is actually
> greater than bestCost.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
