On 3/29/2013 5:28 AM, Bergquist, Brett wrote:
Mike I have a reproducible case but the database is 132GB so it is not
practical. I am willing to dig in and try to help debug this. On the issue
that was pointed to, Mamta seemed to have used a debug procedure that would
print out more information on why the optimizer is choosing what it does.
This might be useful in this case as well.
So if someone can point me in the right area of the code to start looking and
adding some debug that would be helpful. I have done some fixes for Derby in
the past so I am able to build and debug Derby.
That's great. I am out of my depth debugging optimizer problems,
hopefully rick and/or mamta can help with that tool they were working
on. I think rick did some work to make it easier to turn on/off using
optional tools.
From what you have described so far seems key to understand what is
the difference in estimates in your original database after running
update stats on all tables and when you drop/recreate that index that
leads to the behavior change.
starting bottom up I can point you at the routines I would expect the
optimizer to call to get the number of rows in a table. Making sure
these routines are returning reasonable numbers would be a good start.
You are looking for getEstimatedRowCount() interfaces. The 2 most
interesting implementations are:
this one should be called for indexes:
java/engine/org/apache/derby/impl/store/access/btre/OpenBTree.java:200:
public long getEstimatedRowCount()
this one should be called for base tables:
java/engine/org/apache/derby/impl/store/access/conglomerate/GenericController.java:180:
public long getEstimatedRowCount()
