You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "A B (JIRA)" <de...@db.apache.org> on 2006/04/27 07:54:03 UTC
[jira] Created: (DERBY-1260) Investigate impact of infinite cost
estimates on arithmetic operations/comparisons in the optimizer.
Investigate impact of infinite cost estimates on arithmetic operations/comparisons in the optimizer.
----------------------------------------------------------------------------------------------------
Key: DERBY-1260
URL: http://issues.apache.org/jira/browse/DERBY-1260
Project: Derby
Type: Task
Components: Performance
Versions: 10.2.0.0
Reporter: A B
Priority: Minor
For large, deeply nested queries and/or queries with a high number of FROM tables/expressions with high row count estimates, the the resultant cost estimates can be multiplied--sometimes many times over--throughout the optimization process, which means that the overall query estimate can climb to a very large number very quickly. If the query is big enough, this can actually cause the optimizer to reach an estimated cost of INFINITY.
That said, the current optimizer logic for costing and comparing access paths does not expect--and therefore does not account for--infinite cost estimates. As a result the optimizer does comparisons of, and basic arithmetic with, cost estimates and row counts that, when applied to estimtes of Infinity, can give unexpected results. One specific example of this is DERBY-1259.
I'm filing this issue for the task of investigating the optimizer code more closely to see what other issues might exist when infinite cost estimates come into play.
--
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
[jira] Commented: (DERBY-1260) Investigate impact of infinite cost
estimates on arithmetic operations/comparisons in the optimizer.
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1260?page=comments#action_12453442 ]
Bryan Pendleton commented on DERBY-1260:
----------------------------------------
Another example of the problems of handling infinite cost estimates arises during
pushing and pulling of intermediate cost estimates as part of pushing and pulling
predicates. Specifically, there is the following code in OptimizerImpl:
double newCost = currentCost.getEstimatedCost();
double pullCost = 0.0;
CostEstimate pullCostEstimate =
pullMe.getBestAccessPath().getCostEstimate();
if (pullCostEstimate != null)
{
pullCost = pullCostEstimate.getEstimatedCost();
newCost -= pullCost;
/*
** It's possible for newCost to go negative here due to
** loss of precision.
*/
if (newCost < 0.0)
newCost = 0.0;
}
Here are at least some of the interesting situations that can arise with this code:
- If newCost is infinite, and pullCost is also infinite, then newCost will become NaN.
- If newCost is infinite, but pullCost is finite, then newCost will remain infinite.
For more discussion about some of the possible behaviors of this
code, see
http://www.nabble.com/Optimizer%3A-question-about-predicate-pushdown-and-handling-of-intermediate-costs-tf2689629.html#a7500315
> Investigate impact of infinite cost estimates on arithmetic operations/comparisons in the optimizer.
> ----------------------------------------------------------------------------------------------------
>
> Key: DERBY-1260
> URL: http://issues.apache.org/jira/browse/DERBY-1260
> Project: Derby
> Issue Type: Task
> Components: Performance
> Affects Versions: 10.2.1.6
> Reporter: A B
> Priority: Minor
>
> For large, deeply nested queries and/or queries with a high number of FROM tables/expressions with high row count estimates, the the resultant cost estimates can be multiplied--sometimes many times over--throughout the optimization process, which means that the overall query estimate can climb to a very large number very quickly. If the query is big enough, this can actually cause the optimizer to reach an estimated cost of INFINITY.
> That said, the current optimizer logic for costing and comparing access paths does not expect--and therefore does not account for--infinite cost estimates. As a result the optimizer does comparisons of, and basic arithmetic with, cost estimates and row counts that, when applied to estimtes of Infinity, can give unexpected results. One specific example of this is DERBY-1259.
> I'm filing this issue for the task of investigating the optimizer code more closely to see what other issues might exist when infinite cost estimates come into play.
--
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
[jira] Updated: (DERBY-1260) Investigate impact of infinite cost
estimates on arithmetic operations/comparisons in the optimizer.
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1260?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-1260:
---------------------------------
Component/s: SQL
> Investigate impact of infinite cost estimates on arithmetic operations/comparisons in the optimizer.
> ----------------------------------------------------------------------------------------------------
>
> Key: DERBY-1260
> URL: https://issues.apache.org/jira/browse/DERBY-1260
> Project: Derby
> Issue Type: Task
> Components: SQL
> Affects Versions: 10.2.1.6
> Reporter: A B
> Priority: Minor
>
> For large, deeply nested queries and/or queries with a high number of FROM tables/expressions with high row count estimates, the the resultant cost estimates can be multiplied--sometimes many times over--throughout the optimization process, which means that the overall query estimate can climb to a very large number very quickly. If the query is big enough, this can actually cause the optimizer to reach an estimated cost of INFINITY.
> That said, the current optimizer logic for costing and comparing access paths does not expect--and therefore does not account for--infinite cost estimates. As a result the optimizer does comparisons of, and basic arithmetic with, cost estimates and row counts that, when applied to estimtes of Infinity, can give unexpected results. One specific example of this is DERBY-1259.
> I'm filing this issue for the task of investigating the optimizer code more closely to see what other issues might exist when infinite cost estimates come into play.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-1260) Investigate impact of infinite cost
estimates on arithmetic operations/comparisons in the optimizer.
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1260?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-1260:
---------------------------------
Derby Categories: [Performance]
> Investigate impact of infinite cost estimates on arithmetic operations/comparisons in the optimizer.
> ----------------------------------------------------------------------------------------------------
>
> Key: DERBY-1260
> URL: https://issues.apache.org/jira/browse/DERBY-1260
> Project: Derby
> Issue Type: Task
> Affects Versions: 10.2.1.6
> Reporter: A B
> Priority: Minor
>
> For large, deeply nested queries and/or queries with a high number of FROM tables/expressions with high row count estimates, the the resultant cost estimates can be multiplied--sometimes many times over--throughout the optimization process, which means that the overall query estimate can climb to a very large number very quickly. If the query is big enough, this can actually cause the optimizer to reach an estimated cost of INFINITY.
> That said, the current optimizer logic for costing and comparing access paths does not expect--and therefore does not account for--infinite cost estimates. As a result the optimizer does comparisons of, and basic arithmetic with, cost estimates and row counts that, when applied to estimtes of Infinity, can give unexpected results. One specific example of this is DERBY-1259.
> I'm filing this issue for the task of investigating the optimizer code more closely to see what other issues might exist when infinite cost estimates come into play.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.