[ http://issues.apache.org/jira/browse/DERBY-805?page=all ]
Andrew McIntyre updated DERBY-805:
----------------------------------
Other Info: (was: [Patch available])
Derby Info: [Patch Available]
> 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.
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-805
> URL: http://issues.apache.org/jira/browse/DERBY-805
> Project: Derby
> Type: Sub-task
> Components: SQL
> Versions: 10.1.2.0, 10.2.0.0
> Environment: generic
> Reporter: Satheesh Bandaram
> Assignee: A B
> Fix For: 10.2.0.0
> Attachments: DERBY-805.html, DERBY-805_v2.html, DERBY-805_v3.html,
> d805_phase1_v1.patch, d805_phase1_v1.stat, d805_phase1_v2.patch,
> d805_phase1_v2.stat, d805_phase1_v3.patch, d805_phase1_v3.stat,
> d805_phase2_v1.patch, d805_phase2_v1.stat, d805_phase3_v1.patch,
> d805_phase3_v1.stat, phase2_javadocFix.patch, predPushdown_testFix.patch
>
> 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 optimization.
> 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 preprocessing.
--
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
