db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendle...@amberpoint.com>
Subject Re: [jira] Commented: (DERBY-2130) Optimizer performance slowdown from 10.1 to 10.2
Date Wed, 13 Dec 2006 16:45:47 GMT
> I'm curious: what is the optimization time that you consistently see 
> with jumpReset.patch?  Is it the lesser time (i.e 170-200 seconds) or 
> the greater time (500-650s)?  Or something else entirely?

The values I saw on the machine in question were consistently 192-198 secs.

> That said, I would like to emphasize that the underlying problem here 
> seems to be DERBY-1905.  Even if we get 10.2 to run repro.sql as 
> 'quickly' as 10.1, it still takes 10.1 way too long to optimize the 
> query (90 seconds on a "fairly powerful Windows machine", according to 
> the description for DERBY-2130), esp. given that there are no rows in 
> any of the tables.  The reason is because the cost estimates are too 
> high (infinity) and thus timeout does not take effect until too late.

I concur!

> Note, though, that it *may* be too risky to port changes for DERBY-1905 
> back to 10.2 (I don't know for sure since I don't know what those 
> changes will ultimately be).  Thus it might still be worth it to 
> investigate the aforementioned if-block angle for the sake of addressing 
> the performance regression seen between 10.1 and 10.2.  I.e. to 
> specifically address the 10.2 slowdown filed as DERBY-2130.

Either plan sounds fine to me. I think that making changes to 10.2 to
bring it back to the 10.1 levels of performance will benefit the entire
community; my specific motivation is to address the problem more fully
and arrive at an optimizer which can handle these queries in just a few seconds,
particularly because I think that as more and more people start using
these Object/Relational mapping libraries (which seem to be where these
union view subqueries commonly arise) we will see more and more queries
like these and we want to be able to handle them well.

Independently, I tried fiddling with the cost estimates myself, because as
I stepped through HeapCostController and BTreeCostController, my reaction
was that the costs were possibly 2 orders of magnitude too high. In my
experiments, I didn't see much benefit to the optimizer, but I wonder if
that's because I *also* need to have some of these other fixes (such as the
permute state fix from jumpReset.patch and the JUMPING-give-up change that
you've been studying) in order for the cost estimate changes to take effect?



View raw message