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] Updated: (DERBY-805) Push join predicates into union and other set operations. DERBY-649 implemented scalar (single table) predicate pushdown. Adding join predicate push down could improve performance significantly.
Date Mon, 13 Feb 2006 08:45:45 GMT
     [ http://issues.apache.org/jira/browse/DERBY-805?page=all ]

A B updated DERBY-805:

    Attachment: DERBY-805.html

Attaching a description of the changes I plan to submit for this issue.  This is a rather
complicated enhancement so the description of the changes is pretty long.  In short, though,
I outline a 6-step approach to pushing join predicates down into Unions:

1 - Add the ability to take a predicate and scope it to a target result set so that it can
be pushed to that result set.

2 - Implement the "pushOptPredicate()" and "optimizeIt()" methods for UnionNodes.  The former
method should take predicates that are passed into the UnionNode from outer queries, scope
them (per step 1) for the left and right children of the UnionNode, and store them locally.
 The latter method should then pass the scoped predicates down to both children so that they
can use the predicates in their own optimize()/optimizeIt() calls.

3 - Take scoped predicates (created in step 1) that are pushed into the children result sets
of a UnionNode (per step 2) and allow the the children to push the scoped predicates even
further down the tree, until we eventually get them to a base table.

4 - Make sure predicates that are pushed down into subqueries of a UnionNode are correctly
"pulled" back up (if they are unscoped) or discarded (if they are scoped) for every permutation
seen during optimization.

5 - Ensure that the best access path for a UnionNode that pushes predicates is correctly saved
during optimization and correctly retrieved when it comes time to finalize the query's overall
access path.

6 - And finally, when optimization is complete, make sure all relevant predicates are pushed
down the tree one last time and left there, in preparation for code generation.

See DERBY-805.html for all the gory details.

I have made the changes described in this document locally and they all seem to work, with
a couple of exceptions as noted at the end of the document.  I plan to break the changes down
into separate patches where it's possible to do so, and will be posting those patches in the
coming days.  In the meantime, if anyone has time to review this document and provide feedback/direction/suggestion,
I would be grateful.  As I myself am still trying to learn all the subtleties of Derby optimization,
the more feedback I get, the better...

> Push join predicates into union and other set operations. DERBY-649 implemented scalar
(single table) predicate pushdown. Adding join predicate push down could improve performance
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>          Key: DERBY-805
>          URL: http://issues.apache.org/jira/browse/DERBY-805
>      Project: Derby
>         Type: Sub-task
>   Components: SQL
>     Versions:,
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: A B
>      Fix For:
>  Attachments: DERBY-805.html
> Fix for DERBY-649 implemented scalar (single table) predicate push down into UNIONs.
While this improves performance for one set of queries, ability to push join-predicates further
improves Derby performance by enabling use of indices where possible.
> For example,
> create view V1 as select i, j from T1 union all select i,j from T2; 
> create view V2 as select a,b from T3 union all select a,b from T4; 
> insert into T1 values (1,1), (2,2), (3,3), (4,4), (5,5); 
> For a query like
> select * from V1, V2 where V1.j = V2.b and V1.i =1;
> If the join order choosen is V1,V2, V1 can use index on V1.i (if present) following fix
for DERBY-649. But if there is a index on V2.b also, Derby currently can't use that index.
By pushing join predicate, Derby would be able to use the index and improve performance. Some
of the queries I have seen (not the one shown here...) could improve from 70-120 seconds to
about one second.
> Note there is a good comment by Jeff Lichtman about join-predicate push down. I am copying
parts of it here for completeness of this report: (Modified)
> If predicate push down is done during optimization, it would be possible to push joins
into the union as long as it's in the right place in the join order.
> For example:
> create view v as select * from t1 union all select * from t2;
> select * from v, t3 where v.c1 = t3.c2;
> In this select, if t3 is the outer table then the qualification could be pushed into
the union and optimized there, but if t3 is the inner table the qualification can't be pushed
into the union.
> If the pushing is done at preprocess time (i.e. before optimization) it is impossible
to know whether a join qualification like this can be safely pushed.
> There's a comment in UnionNode.optimizeIt() saying:
> /* RESOLVE - don't try to push predicated through for now */
> This is where I'd expect to see something for pushing predicates into the union during
> BTW, the business of pushing and pulling predicates during optimization can be hard to
understand and debug, so maybe it's best to only handle the simple cases and do it during

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators:
For more information on JIRA, see:

View raw message