Rick Hillegas (JIRA) wrote:
[ http://issues.apache.org/jira/browse/DERBY-754?page=comments#action_12363086 ]
Rick Hillegas commented on DERBY-754:
-------------------------------------
I spent a little time looking for cases which this patch breaks and didn't find any.
That's a hopeful sign that this patch may very well do the right thing. However, I
don't know the surrounding code well enough to certify that this patch is correct.
Maybe Jeff could take a look. I feel generally uneasy about this code: It is a lot
of logic to handle an edge case which doesn't arise from a customer complaint.
Well actually, this issue _did_ come up from a customer complaint. They have a
query with several levels of explicitly-specified joins (including ON
predicates) in them, and some of the joins have subqueries as the right-side
child. As Derby currently stands, the query takes 10-12 minutes to execute on
my machine. With this patch applied, it takes 10-12 _seconds_ the first time,
and only 2-3 seconds subsequent times.
That said, though, a recent discussion on a different thread suggests an
alternate--and perhaps cleaner--way of addressing this issue. In the thread
found here:
http://article.gmane.org/gmane.comp.apache.db.derby.devel/12042
Jeffrey Lichtman pointed out that Derby doesn't flatten some subqueries that it
could and probably _should_ flatten. On that same thread, I also mentioned that
the optimizer doesn't consider hash joins for non-flattened subqueries. Either
or both of those approaches could end up being alternate ways to solve this
particular problem. I plan to look at the hash-join-with-unflattened-queries
angle first, since that addresses another customer issue I've seen, and will see
if that applies to this issue (DERBY-754) as well.
In the meantime, this patch for DERBY-754 isn't entirely useless: I've found
that it can serve as a basis for more general predicate push-down into
subqueries, which I think will be a point on which other performance
improvements can be built. Take, for example, DERBY-805: I've started looking
at that issue and it seems like several parts of the code changes for DERBY-754
can be used as a starting point for a possible solution to DERBY-805. I need to
investigate that more, but my general feeling is that I'll resubmit the relevant
parts of this DERBY-754 patch as part of any work I do for DERBY-805.
Thanks much for taking the time to look at this patch, Rick. I hope you don't
feel like your time was wasted--the above-mentioned thread is on-going and it
was just yesterday that I realized it might be related to this issue. I
appreciate your review and your feedback...
Army
