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.

[Jeffrey Lichtman <sw...@rcn.com>]

>>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
>   t2,
>   (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.

OK. One way to deal with this is to put a ProjectRestrictNode between 
the union node and the values clause as a place to park the 
predicate. To make things simple, you might want to always put 
ProjectRestrictNodes under both sides of the union during 
preprocessing (i.e. after binding but before optimization). In some 
cases the extra nodes won't be needed, but ProjectRestrictNodes (and 
the corresponding ProjectRestrictResultSets) are cheap. Also, you 
could eliminate unnecessary ProjectRestrictNodes at the end of 
optimization (possibly in modifyAccessPaths()).

This approach would give better performance in some cases, and could 
simplify the code (since you wouldn't have to figure out when the 
predicates are pushable).


                        -        Jeff Lichtman
                                 swazoo@rcn.com
                                 Check out Swazoo Koolak's Web Jukebox at
                                 http://swazoo.com/