[
https://issues.apache.org/jira/browse/DERBY-754?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dag H. Wanvik updated DERBY-754:
--------------------------------
Derby Categories: [Performance]
> Push ON clause predicates down when optimizing SELECT queries.
> --------------------------------------------------------------
>
> Key: DERBY-754
> URL: https://issues.apache.org/jira/browse/DERBY-754
> Project: Derby
> Issue Type: Improvement
> Affects Versions: 10.1.2.1, 10.2.1.6
> 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.
-
You can reply to this email to add a comment to the issue online.
