db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [OPTIMIZER] Proposal for pushing join predicates into Unions posted to DERBY-805.
Date Mon, 13 Feb 2006 19:36:02 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<body bgcolor="#ffffff" text="#000000">
This is a great improvement to Derby providing good performance
improvement. Thanks Army for the writeup... It does look impressive. I
will take this home for some "light" reading. <span
 class="moz-smiley-s1"><span> :-)&nbsp; </span></span>I would like
invite other optimizer experts to review the doc and code patch when
ready. Jeff Lichtman and Rick, do you have some time to review the doc?<br>
I have added an entry to <a
</a>under improvements for this. Hopefully this work will lead to
further improvements to Derby Optimizer in this area.<br>
Army wrote:
<blockquote cite="mid43F048D9.5040909@sbcglobal.net" type="cite">I have
attached a description of the changes I plan to submit for DERBY-805 to
that Jira issue.&nbsp; This is a rather complicated enhancement so the
description of the changes is pretty long.&nbsp; In short, though, I outline
a 6-step approach to pushing join predicates down into Unions:
1 - Add the ability to take a predicate and scope it to a target result
set so that it can be pushed to that result set.
2 - Implement the "pushOptPredicate()" and "optimizeIt()" methods for
UnionNodes.&nbsp; The former method should take predicates that are passed
into the UnionNode from outer queries, scope them (per step 1) for the
left and right children of the UnionNode, and store them locally.&nbsp; The
latter method should then pass the scoped predicates down to both
children so that they can use the predicates in their own
optimize()/optimizeIt() calls.
3 - Take scoped predicates (created in step 1) that are pushed into the
children result sets of a UnionNode (per step 2) and allow the the
children to push the scoped predicates even further down the tree,
until we eventually get them to a base table.
4 - Make sure predicates that are pushed down into subqueries of a
UnionNode are correctly "pulled" back up (if they are unscoped) or
discarded (if they are scoped) for every permutation seen during
5 - Ensure that the best access path for a UnionNode that pushes
predicates is correctly saved during optimization and correctly
retrieved when it comes time to finalize the query's overall access
6 - And finally, when optimization is complete, make sure all relevant
predicates are pushed down the tree one last time and left there, in
preparation for code generation.
See DERBY-805.html for all the gory details.
I have made the changes described in this document locally and they all
seem to work, with a couple of exceptions as noted at the end of the
document.&nbsp; I plan to break the changes down into separate patches where
it's possible to do so, and will be posting those patches in the coming
days.&nbsp; In the meantime, if anyone has time to review this document and
provide feedback, direction, or suggestions, I would be grateful.&nbsp; As I
myself am still trying to learn all the subtleties of Derby
optimization, the more feedback I get, the better...

View raw message