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 Wed, 27 Mar 2013 22:36:38 GMT
On 3/27/2013 12:16 PM, Bergquist, Brett wrote:
> Some background.
>
> I have a customer that is using an earlier release of our system that has
> Derby 10.8.2.1 installed.   Because of issues like
> 	https://issues.apache.org/jira/browse/DERBY-5680,
> it has been running with the indexStat daemon disabled.
>
> We are going to have a new release soon and it will be installing Derby 10.9.1.0 with
the indexStat
> daemon enabled.   I recently got a copy of the customer's database (132Gb) and ran into
> a very long query.   I manually ran SYS_UTIL.SYSCS_UPDATE_STATISTICS on all of the tables
> in the query to ensure that the statistics are up to date.
> ...

when I look at bad query plans i usually look for estimated row counts 
vs actual to see where the optimizer made some bad assumptions.  The 
plan you posted has a bunch of 0's.  So maybe you aren't gathering the
plan with all options enabled?  If optimizer actually thinks there are
0 rows in your 62 million row table that would be a problem.

Mime
View raw message