[ http://issues.apache.org/jira/browse/DERBY-754?page=comments#action_12363086 ]
Rick Hillegas commented on DERBY-754: ------------------------------------- I spent a little time looking for cases which this patch breaks and didn't find any. That's a hopeful sign that this patch may very well do the right thing. However, I don't know the surrounding code well enough to certify that this patch is correct. Maybe Jeff could take a look. I feel generally uneasy about this code: It is a lot of logic to handle an edge case which doesn't arise from a customer complaint. > Push ON clause predicates down when optimizing SELECT queries. > -------------------------------------------------------------- > > Key: DERBY-754 > URL: http://issues.apache.org/jira/browse/DERBY-754 > Project: Derby > Type: Improvement > Components: Performance > Versions: 10.1.2.0, 10.1.2.1, 10.2.0.0 > Reporter: A B > Assignee: A B > Attachments: d754_draft.patch > > In cases where a SELECT subquery occurs as one of the operands to a Join, it > is sometimes beneficial to push join predicates down to the subquery, which > allows the optimizer to find a better access path for the subquery and thus > can improve query performance. > For example, take the following query: > SELECT t1.a, t1.b, temp.c > FROM t1 > LEFT OUTER JOIN ( > SELECT c,d > FROM t2 > ) as temp > ON > t1.a = temp.d > and temp.d = 8 > ; > Currently, when optimizing the inner SELECT query, Derby will only pass the > inner SELECT's WHERE predicates to the optimizer--the outer ON predicates are > ignored. Thus, in this case, the optimizer will have no predicates to work > with and so will do a table scan on t2. > If, however, Derby were to push the "temp.d = 8" predicate down into the > inner SELECT query, the optimizer could use that predicate to make a smarter > decision. For example, if a primary key existed on column "d" in T2, the > optimizer could use that and then choose to do an index/hash scan when > reading t2 (instead of a table scan). > Not only does can this kind of "pushing" lead to faster reading of tables, > but in some cases where the predicate being pushed references two tables, it > can also influence the optimizer's choice of join strategy, which can in turn > lead to improved performance. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
