db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject svn commit: r1521517 - in /db/derby/code/branches/10.10: ./ java/engine/org/apache/derby/impl/store/access/btree/ java/testing/org/apache/derbyTesting/functionTests/tests/largedata/
Date Tue, 10 Sep 2013 15:03:53 GMT
Author: mikem
Date: Tue Sep 10 15:03:53 2013
New Revision: 1521517

URL: http://svn.apache.org/r1521517
Log:
DERBY-6317 Optmizer can choose the wrong path when BTreeCostController.java returns an estimate
cost and row count of 0.0

backported change #1521310 from trunk to 10.10 branch.

The Optimizer estimates do not handle well 0 row counts coming out of store.
These estimates end up getting multiplied by other estimates and the resulting
0 costs caused the incorrect plan to be picked in the repro included with
this fix.  This fix changes store cost to always return at least a minimum
of one row when asked to estimate the number of rows in an exact range of
keys from an index.  This minimum is consistent with the optimizer assumption
that an exact key match on a unique index will also return 1 row.

Thanks to Brett Bergquist for debugging and suggesting a fix and to mamta
satoor for providing a reproducible test case for the bug.


Added:
    db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/Derby6317Test.java
      - copied unchanged from r1521310, db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/Derby6317Test.java
Modified:
    db/derby/code/branches/10.10/   (props changed)
    db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/store/access/btree/BTreeCostController.java
    db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/_Suite.java

Propchange: db/derby/code/branches/10.10/
------------------------------------------------------------------------------
  Merged /db/derby/code/trunk:r1521310

Modified: db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/store/access/btree/BTreeCostController.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/store/access/btree/BTreeCostController.java?rev=1521517&r1=1521516&r2=1521517&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/store/access/btree/BTreeCostController.java
(original)
+++ db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/store/access/btree/BTreeCostController.java
Tue Sep 10 15:03:53 2013
@@ -593,6 +593,21 @@ public class BTreeCostController extends
 
             float estimated_row_count = input_row_count * ret_fraction;
 
+            // DERBY-6317
+            // In the case of unique indexes we always estimate a return of
+            // 1 row for a equality term. To be consistent always insure
+            // that this estimate at least returns at least 1 row, even
+            // though the reality may be that the row does not exist.  In the
+            // case of DERBY-6317 a table with 43 million rows, the existing
+            // calculation for the search which in reality was exactly 1 row 
+            // was rounding down to 0 rows.  This in turn led calling 
+            // optimizer to pick a full scan plan on the 43 million row which
+            // was discounted by multiplying the 0 row count, rather than 
+            // pick an obviously useful index.
+            if (estimated_row_count < 1)
+                estimated_row_count = 1;
+
+
             // first the base cost of positioning on the first row in the scan.
             double cost = 
                 getFetchFromFullKeyCost(scanColumnList, access_type);
@@ -642,7 +657,6 @@ public class BTreeCostController extends
             // return the cost
             cost_result.setEstimatedCost(cost);
 
-            // RESOLVE - should we make sure this number is > 0?
             cost_result.setEstimatedRowCount(Math.round(estimated_row_count));
         }
         finally

Modified: db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/_Suite.java?rev=1521517&r1=1521516&r2=1521517&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/_Suite.java
(original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/_Suite.java
Tue Sep 10 15:03:53 2013
@@ -41,6 +41,7 @@ public class _Suite extends BaseJDBCTest
     public static Test suite() {
         TestSuite suite = new TestSuite("largedata suite");
 
+        suite.addTest(Derby6317Test.suite());
         // DERBY-5624, currently this runs out of file descriptors on unix
         // systems with 1024 limit per user.  Setting to run only on windows
         // until solution for unix is found.



Mime
View raw message