db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <derby-...@db.apache.org>
Subject [jira] Resolved: (DERBY-754) Push ON clause predicates down when optimizing SELECT queries.
Date Wed, 16 Aug 2006 02:32:14 GMT
     [ 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

        

Mime
View raw message