db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject Re: I need help on getting Derby to use a primary key index on a query
Date Fri, 23 Aug 2013 17:38:26 GMT
Thanks for taking the time to respond Mamta.   I am running a patched version of
 The patches have to do with issues that I provided patches for to Derby relating to LogicalConnection
deadlocks.   These patches were just incorporated as I saw emails indicating so just the other

I have verified that I cannot reproduce this problem using release.   I exported
the data from my production database and created a new database using release and
do not see the problem.  I am trying to get this into a readily reproducible issue so that
if I make a change I can verify it works or not because of the change.

According to the jira


It says the fix is in,, and   Since my production database
was hard upgraded to, I have no possibility of going backwards.  I know how to build
Derby (from the trunk) so I would like to build Derby but don't know the proper way
to get the source for that revision.   Any help here will be greatly appreciated as well.

One not, when I do not see the problem, the optimizer plan indicates that the estimated row
count is 0.0 with a cost > 0.0 for both tables.  When I do see the problem, the optimizer
plan indicates that the estimated row count is 0.0 with a cost of 0.0 which seems incorrect.
  Note that I have used the system procedure to drop that database statistics and recreated
them to no avail.

I am in the process of trying this with the derby.storage.indexStats.debug.keepDisposableStats=true
option as well and will provide follow up.

On Aug 23, 2013, at 1:10 PM, Mamta Satoor <msatoor@gmail.com<mailto:msatoor@gmail.com>>

Hi Brett,

We have encountered and fixed at least one bug in case of single-column unique index, where
starting 10.9 we have decided not to keep stats for such indexes. The bug fixed is DERBY-6045
and it is in Derby 1480153). I was wondering if you could share java org.apache.derby.tools.sysinfo
so we know if you have that fix.

If you do have that fix, then it is possible (though I am not certain) that there may be other
bug in this area that
you are running into. One way to figure that out is to use the property derby.storage.indexStats.debug.keepDisposableStats=true.
This property will force Derby to collect the stats for single-column unique index. So, if
you can recreate your db with this property and run the query, we can check the query plan
to see if it starts using the single-column unique index. It might be possible to use existing
db with this property and recreate the stats and try the query if creating the db from scratch
with this property is a time consuming process.


On Wed, Aug 21, 2013 at 4:55 PM, Bryan Pendleton <bpendleton.derby@gmail.com<mailto:bpendleton.derby@gmail.com>>
Any thoughts or comments on why this the optimizer chooses on over the other?

Unfortunately, no.

But there have been concerns about the optimizer's cost
analysis for years; see https://issues.apache.org/jira/browse/DERBY-1905
and some of the other issues linked from it (DERBY-1259, DERBY-1260).

So, while I know it's frustrating for you to be wrestling with
an optimizer that won't behave, it's also good news, for Derby,
that you've made such good progress in trying to understand
this peculiar behavior.

Perhaps when we get to the bottom of the strange behavior
you're seeing, we'll have a better understanding of some of
the other strange optimizer behaviors we've seen in the past.



View raw message