[ http://issues.apache.org/jira/browse/DERBY-754?page=all ]
A B resolved DERBY-754.
-----------------------
Resolution: Won't Fix
Assignee: (was: A B)
I have been assigned to this issue for a very long time but have not done any
active work on it. Instead, I did some work for subquery materialization via
hash join (see DERBY-781) that seems like a cleaner solution and that solves
the performance problems I was seeing when I filed this issue.
So I'm resolving this as "Won't Fix" since I have no further plans to address
this particular issue. If down the road anyone feels like looking more closely
at this to see if it is still a beneficial effort after DERBY-781, then that
person should feel free to open another Jira issue describing his/her
thoughts/motivations/plans.
> Push ON clause predicates down when optimizing SELECT queries.
> --------------------------------------------------------------
>
> Key: DERBY-754
> URL: http://issues.apache.org/jira/browse/DERBY-754
> Project: Derby
> Issue Type: Improvement
> Components: Performance
> Affects Versions: 10.2.0.0, 10.1.2.0, 10.1.2.1
> Reporter: 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
