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: Why is the optimizer choosing such a bad path
Date Thu, 28 Mar 2013 00:33:54 GMT
Mike, I would like to understand the "you aren't gathering the
plan with all options enabled" comment.   I followed this from the Derby Tuning guide:

-- turn on RUNTIMESTATISTICS for connection:
-- execute complex query here -- step through the result set
-- access runtime statistics information:

And placed my query in there.   So if there is something else that needs to be done, can
you point me in the correct direction. 

I am going to check the large table tonight and see if returns an error on that.   How
could the optimizer have the correct row counts?

From: Mike Matrigali [mikem_app@sbcglobal.net]
Sent: Wednesday, March 27, 2013 6:36 PM
To: derby-dev@db.apache.org
Subject: Re: Why is the optimizer choosing such a bad path

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 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 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.

View raw message