[
https://issues.apache.org/jira/browse/DERBY-6011?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-6011:
--------------------------------------
Attachment: prefer-unique-index-v2.diff
Attaching prefer-unique-index-v2.diff which implements the alternative
proposal. The patch also adds a new test that verifies that the unique index is
used.
The patch adjusts the cost estimate only if all of the following conditions
hold:
1) There is a unique index (A) on the table
2) The query has equality predicates for all the columns in the unique index A
3) There is another index (B) that is either non-unique or unique without
satisfying 2.
4) Index B is non-covering
5) The expected row count when using index B is less than 1
If all of the above conditions hold, the row count for index B is changed to 1
before estimating the cost of fetching rows from the base table.
Although using the unique index might be slightly more expensive in some cases,
it is safer as it will never access more than one row, whereas the non-unique
alternative could potentially access many rows.
The third, fourth and fifth select statements in the added test case were not
affected by the v1 patch, but the v2 patch makes them all use a unique index.
All the regression tests passed with the patch.
> 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,
> prefer-unique-index-v1.diff, prefer-unique-index-v2.diff
>
>
> 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
