Thanks Bryan! This was so obvious in retrospect - I was totally befuddled by the "heisenbug" nature of it (problem goes away when you try to examine it!).
I've made a rather crude workaround: I calculate a random number N then add WHERE <N> = <N> to the end of the query - it prevents the statement from being cached but doesn't seem to slow down the query at all. Now I get good performance all the time! I'll see if I can wrap this up into a test case. It does seem like "correct" behavior in a technical sense, but the outcome is certainly weird / undesirable so if there's something that can be done to make the optimizer avoid plans that have such a poor worst case performance (even if they are the "best" plan for the specific data at that moment) then that would definitely be a plus. Cheers, Simon On Sat, Aug 21, 2010 at 11:41 AM, Bryan Pendleton <bpendleton.de...@gmail.com> wrote: >> So the question is, why does the optimizer suddenly get much smarter >> just because I restarted the database? Is it caching the execution >> plan perhaps? Can I make it be this smart all the time somehow? > > Derby definitely caches the compiled form of the statement, and will re-use > it. > > There is a way to completely disable that cache, I believe, check > this bug report from some details: > > https://issues.apache.org/jira/browse/DERBY-4280 > > Given the dramatic behavior changes in your situation, it would be > awfully useful if you could package up whatever information you can > about this and log it in the Derby Jira for future users benefit. > > Ideally, if you could contribute a test program which demonstrates > the behavior that you describe, that would be great! But even things > like query plans of the good and bad behavior, descriptions of the > schemas, etc., can be of help. > > thanks, > > bryan >
