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: [jira] Commented: (DERBY-805) Push join predicates into union and other set operations. DERBY-649 implemented scalar (single table) predicate pushdown. Adding join predicate push down could improve performance significantly.
Date Thu, 16 Feb 2006 18:27:13 GMT

Army wrote:

> Jeff Lichtman (JIRA) wrote:
>> Why are you only dealing with join predicates? It would also be
>> useful to push simple search arguments (i.e. a column compared to a
>> constant), and this case might  be more common than join predicates:
> <Jeff Lichtman>
> > BTW, the business of pushing and pulling predicates during
> optimization can be
> > hard to understand and debug, so maybe it's best to only handle the
> simple
> > cases and do it during preprocessing.
> <Satheesh>
> The pushing is done before optimization... during preprocessing. [ ...
> ] You bring up a *great *point about pushing join predicates. I am not
> implementing this for UnionNode.

Right... I think more appropriate place to push single-table scalar
predicates is in preprocessing stage. My current changes don't yet
address all possible scalar predicate push downs. It should be possible
to push expressions like upper(name)='DERBY' or T1.a+T1.b=5. Even if
they don't help in choosing index, if available, evaluating expressions
closer to data could save large amount of processing.

That said, one advantage of pushing scalar predicates later during
optimization may be that more of join predicates may qualify to be
pushed. It may be possible to push T1.a+T1.b = T2.a+T2.b into T1 or T2
depending on join order. We may still be able to achieve this, but may
duplicate a lot of code from preprocessing time.

>> I would prefer to see any type of predicate pushed into a union -
>> even those containing complex expressions. This might be hard to
>> implement, though, as I don't know whether the cloning methods are
>> implemented for the entire ValueNode hierarchy.
> Sounds like an "itch" to me :) While it might indeed be nice to push
> predicates containing complex expressions, that's another enhancement
> of its own.  I won't be doing that with my DERBY-805 changes.

Right... I didn't find any existing code in Derby that clones an entire
expression for pushing. I cooked up some predicate copying logic that
only handles very simple cases. That improvement could be taken up

> Thanks again--I can't say that enough--for reading the document.  It's
> a huge one and I'm grateful for your time and feedback.

Thank you for working on this. This is a good improvement to Derby.


View raw message