[
https://issues.apache.org/jira/browse/DERBY-6011?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13553053#comment-13553053
]
Knut Anders Hatlen commented on DERBY-6011:
-------------------------------------------
Most likely using 1 as floor would be sufficient to make the optimizer pick the
unique index. The optimizer trace above suggests that the lookup in the
non-unique index will have a cost of 20.04, whereas the lookup in the unique
index has cost 1.575. So if the two had the exact same estimated cost for the
additional base table fetch, the unique index plan would be preferred.
Presumably...
FromBaseTable.estimateCost() already has code to handle something that sounds
like a very similar case:
/* oneRowResultSetForSomeConglom means there's
a unique index, but certainly
* not this one since we are here. If store
knows this non-unique index
* won't return any row or just returns one row
(eg., the predicate is a
* comparison with constant or almost empty
table), we do minor adjustment
* on cost (affecting decision for covering
index) and rc (decision for
* non-covering). The purpose is favoring
unique index. beetle 5006.
*/
if (oneRowResultSetForSomeConglom &&
costEstimate.rowCount() <= 1)
{
costEstimate.setCost(costEstimate.getEstimatedCost() * 2,
costEstimate.rowCount() + 2,
costEstimate.singleScanRowCount() + 2);
}
Maybe we could change this code to add even more bias towards the unique index.
Another thing to consider, is whether the cost estimates give a realistic
picture of the actual cost. I find it puzzling that the estimates for looking
up the key in an index is somewhere between 1.5 and 20, whereas looking up the
base row with the row location fetched from the index, adds more than 100 to
the cost. I'd expect those two costs to be about the same size. And if they
were, the difference in index lookup cost (1.5 vs 20) would have dominated the
difference in base row lookups (1 vs 0.6). But with the current estimates, the
base row lookup has more weight than the index lookup.
> Derby performs very badly (seems to deadlock and timeout) in very simple
> multi-threaded tests
> ---------------------------------------------------------------------------------------------
>
> Key: DERBY-6011
> URL: https://issues.apache.org/jira/browse/DERBY-6011
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.7.1.1, 10.8.2.2, 10.9.1.0
> Environment: Lenovo laptop with SSD's, Windows 7, 64-bit, Sun JDK
> 1.6.xx
> Reporter: Karl Wright
> Attachments: derby.log, force-specific-index.diff, manifoldcf.log
>
>
> The Apache ManifoldCF project supports Derby as one of its underlying
> databases. Simple tests, however, demonstrate that Derby is apparently
> deadlocking and timing out repeatedly under multi-thread conditions. This
> problem is long-standing, and is not exhibited by any other database
> ManifoldCF supports, and makes a simple test take between 6x and 12x as long.
> There is a trivial test with demonstrates the problem vs. other databases.
> Please do the following (once you have java 1.6+, svn 1.7+, and ant 1.7+
> available):
> (1) Check out https://svn.apache.org/repos/asf/manifoldcf/trunk
> (2) Run the following ant target to download the dependencies: "ant
> make-core-deps"
> (3) Run the Derby test: "ant run-rss-tests-derby" . Note the time required -
> at least 180 seconds, can be up to 360 seconds.
> (4) Run the equivalent HSQLDB test: "ant run-rss-tests-HSQLDB". This test
> takes about 31 seconds to run.
> The output of the Derby test can be found in the directory
> "tests/rss/test-derby-output". Have a look at manifoldcf.log, where all
> long-running queries are reported. Derby.log is also included, which shows
> only that during the test's cleanup phase the database is deleted before it
> is shutdown, which is not pertinent to the performance issue.
> I am available to assist with ManifoldCF, if that seems to be required.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
