db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Why is the optimizer choosing such a bad path
Date Fri, 29 Mar 2013 16:30:01 GMT
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:
    public long getEstimatedRowCount()

this one should be called for base tables:
    public long getEstimatedRowCount()

View raw message