Re: [jira] Updated: (DERBY-649) Useful indexes not used in UNION ALL

[Satheesh Bandaram]

Thanks for your review, Jeff. The pushing is done before optimization... during preprocessing. Yes, we already have logic to find "pushable" predicates that are single table references only. My current UNION optimization further restricts to only BinaryOperators and InList, but I do plan to expand on that patch. These cases benifit the most, by using available index. I will address more generic predicates (still single table predicates) and possibly other ResultSet nodes (not just SelectNode) for trunk. (main development codeline) I will also look at other set operations then. You bring up a great point about pushing join predicates. I am not implementing this for UnionNode. I do see how it can improve performance though. It seems current optimizer doesn't optimize join predicates as much as possible. I have been seeing many large queries with a few levels of FromSubquery nesting, some with/without outer joins, that Derby doesn't seem to handle as good as other databases. I am interested in making improvements in this area. If you have sometime, it would be great to share your knowledge about what kind of join predicates Derby does handle currently, where it can see improvements and possibly some ideas on how it could be done. I think this would be great information to the community. Thanks, Satheesh Jeffrey Lichtman wrote: But if it's done during optimization, it would be possible to push joins into the union as long as it's in the right place in the join order. For example: create view v as select * from t1 union all select * from t2; select * from v, t3 where v.c1 = t3.c2; In this select, if t3 is the outer table then the qualification could be pushed into the union and optimized there, but if t3 is the inner table the qualification can't be pushed into the union. If the pushing is done at preprocess time (i.e. before optimization) it is impossible to know whether a join qualification like this can be safely pushed. There's a comment in UnionNode.optimizeIt() saying: /* RESOLVE - don't try to push predicated through for now */ This is where I'd expect to see something for pushing predicates into the union during optimization. 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. Another question: should this fix be limited to unions? What about other set operations like INTERSECT?                        -        Jeff Lichtman                                 [EMAIL PROTECTED]                                 Check out Swazoo Koolak's Web Jukebox at                                 http://swazoo.com/