Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: neutral (nike.apache.org: local policy)
Message-ID: <5155C189.70502@sbcglobal.net>
Date: Fri, 29 Mar 2013 09:30:01 -0700
From: Mike Matrigali <mikem_app@sbcglobal.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:17.0) Gecko/20130307 Thunderbird/17.0.4
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Re: Why is the optimizer choosing such a bad path
References: 
 <97EB699F861AD841B5908C7CA9C9565602699D8230DE@VSERVER1.canoga.com>,<5153713B.706@sbcglobal.net>
 <97EB699F861AD841B5908C7CA9C9565602698C87D0E2@VSERVER1.canoga.com>
 <97EB699F861AD841B5908C7CA9C9565602699D823154@VSERVER1.canoga.com>
 <97EB699F861AD841B5908C7CA9C9565602699D823155@VSERVER1.canoga.com>
 <97EB699F861AD841B5908C7CA9C9565602699D823158@VSERVER1.canoga.com>
 <5154B1F0.8080900@sbcglobal.net>
 <97EB699F861AD841B5908C7CA9C9565602699D823238@VSERVER1.canoga.com>
In-Reply-To: 
 <97EB699F861AD841B5908C7CA9C9565602699D823238@VSERVER1.canoga.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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()