[ http://issues.apache.org/jira/browse/DERBY-805?page=all ]
A B updated DERBY-805:
----------------------
Attachment: d805_followup_v1.patch
Attaching a follow-up patch, d805_followup_v1.patch, that addresses some issues
which remained after Phase 4 was committed. In particular:
1) Added logic to skip predicate pushdown when either of the predicate's column
references does not point to a base table. This can happen if, for example,
the column reference points to a literal or an aggregate expression. Further
work is required for such situations in order to correctly "remap" the column
reference to its source (or at least, to figure out what exactly it means to
remap a ColumnReference that doesn't point to a base table, and then to
implement the appropriate changes)--so in the meantime, I've just decided to
skip pushing the predicate for now.
2) Added logic to correctly set the column number of a "scoped" reference based
on whether or not the reference points to a base table. Existing comments in
the relevant sections of code describe why we need to set the column numbers
for references pointing to base tables, but the code itself didn't actually
check for the base table condition--it set the column number for all scoped
references, which wasn't always correct.
3) In cases where a ColumnReference's source ResultColumn's expression is not
another ColumnReference, made it so that the scope operation will return a
clone of ColumnReference (instead of the ColumnReference itself) since that
ColumnReference will be pushed to two result sets.
4) Added corresponding test cases to the lang/predicatePushdown.sql test and
updated the master file accordingly.
I ran derbyall on Red Hat Linux with ibm142 and saw no new failures.
If anyone has time to review, I'd be grateful. Thanks.
> 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,
> DERBY-805_v4.html, DERBY-805_v5.html, d805_followup_v1.patch,
> 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, d805_phase4_v1.patch, d805_phase4_v1.stat,
> d805_phase4_v2.patch, 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
