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? thanks, bryan
