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.2.0.0, 10.1.2.0, 10.1.2.1
Reporter: A B
Assigned to: A B
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
