db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <qoz...@sbcglobal.net>
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 16:21:44 GMT
Jeff Lichtman (JIRA) wrote:
>     [ http://issues.apache.org/jira/browse/DERBY-805?page=comments#action_12366598 ]

> Jeff Lichtman commented on DERBY-805:
> -------------------------------------
> I have been reading Army's (A B''s) document, and I have some questions.

Thank you very much for taking the time to read it.  I really appreciate it.

> How could a predicate be pushable to only one side of a union? Can you 
> provide an example of a predicate that can be pushed only to one side?

If we take something along the lines of:

select ... from
   (select * from t1 union values (1,2), (3,4), (5,6)) X1 (a,b)
where X1.a = t2.i;

In this case the predicate X1.a = t2.i could be pushed to the left ("select * 
from t1") and used when reading T1, but couldn't be pushed to the VALUES clause 
  because there's no underlying table.  If we pushed to the left but not to the 
right, then removed it from the UnionNode's predList--which is the 
restrictionList of the ProjectRestrictNode above the UnionNode--the rows from 
the right would remain unqualified and thus we'd return incorrect results (more 
rows that intended).

> 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:

Actually, when I first made the changes described in the document, I pushed any 
predicate that was a binary relational operator with a column reference on at 
least one side and a query-invariant value (i.e. constant or parameter) on 
whichever side was not a column reference (if either).  This covered the case of 
a column compared to a constant.  All of my changes worked, so from a 
logical/coding perspective we could indeed do just that.  However, I then put in 
the join predicate limitation because it seemed to me (based on very brief 
inspection) that the case of a comparison with a constant was covered by 
Satheesh's fix for DERBY-649, so I thought it might be extra unnecessary work to 
continually push/pull those predicates throughout the optimization process.  The 
following comments re: DERBY-649 made me think I didn't need to worry about 
one-sided 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.

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.

And of the course, the "summary" of DERBY-805 itself says "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 

So given that, I figured the goal for DERBY-805 was to focus on pushing join 
predicates--and that's what I've done.  One final comment from OptimizerImpl 
further prompted me lean toward this limitation:

	** Pull the predicates at from the optimizable and put
	** them back in the predicate list.
	** NOTE: This is a little inefficient because it pulls the
	** single-table predicates, which are guaranteed to always
	** be pushed to the same optimizable.  We could make this
	** leave the single-table predicates where they are.

So it seemed like pushing more single-sided predicates would be adding to the 
"inefficiency" mentioned here, and since the predicates are (as I understand it) 
already handled in preprocessing for DERBY-649, I didn't think we'd benefit from 
pushing them during optimization.  Perhaps I'm missing something somewhere or 
drawing the wrong conclusion?

> 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.

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.


View raw message