Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r1522084 - in /db/derby/code/branches/10.8: ./
 java/engine/org/apache/derby/impl/store/access/btree/
 java/testing/org/apache/derbyTesting/functionTests/tests/largedata/
Date: Wed, 11 Sep 2013 22:46:46 -0000
To: derby-commits@db.apache.org
From: mikem@apache.org
Message-Id: <20130911224646.CAFCD238889B@eris.apache.org>

Author: mikem
Date: Wed Sep 11 22:46:46 2013
New Revision: 1522084

URL: http://svn.apache.org/r1522084
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.8 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.8/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.8/   (props changed)
    db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/store/access/btree/BTreeCostController.java
    db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/_Suite.java

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

Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/store/access/btree/BTreeCostController.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/store/access/btree/BTreeCostController.java?rev=1522084&r1=1522083&r2=1522084&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/store/access/btree/BTreeCostController.java (original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/store/access/btree/BTreeCostController.java Wed Sep 11 22:46:46 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.8/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/_Suite.java?rev=1522084&r1=1522083&r2=1522084&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/_Suite.java (original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/largedata/_Suite.java Wed Sep 11 22:46:46 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.