Just remembered, during discussions with Jeffl, he mentioned another
one... Improve unnesting of subqueries with more than one table. More
work. :-)
Satheesh
- Extend join predicate push downs into all table expressions.
Continuation of your work.
- Extend all scalar single table _expression_ pushdowns during
preprocessing into table expressions. (like T1.a+T1.b=5) Continuation
of my scalar predicate work.
- Enable hash-joins on select subqueries. DERBY-781.
- Consider materialization (like a VTI) as one possible execution
path for subqueries where hash-joins are not possible.
- Use invariant interface to determine if an _expression_ could
qualify as predicate or search clause. (for cases like 'name =
upper(c)') DERBY-824 and DERBY-813.
- Push some preprocessing work into bind phase, where possible.
Examples could be constant folding (a = upper('Sam'), re-arranging
predicates like 2>a to a>2. This is causing
missing of correctly identifying predicates early enough to enable
pushing.
- Better join search algorithms could help large query optimization.
- Enhanced OR/IN processing. (DERBY-47)
- Multiple index scans for one table. Extension of above to cover
cases like a=2 OR b=4 with index on both a and b.
- Improve unnesting of subqueries with more than one table
- I am sure there are others....
Satheesh Bandaram wrote:
Army wrote:
All of that said, my changes for DERBY-805 won't actually push the
predicate in the above example--DERBY-805 will only push predicates
into UNION nodes. But the argument is still the same...
Right... Some day extending your current optimizer changes to support
all table expressions would be a great enhancement to Derby optimizer.
I do believe Derby's optimizer can improve in the area of large query
optimization, especially with subqueries. Here are some of other
thoughts I have on where Derby can be improved. Let me know if this
seems incorrect. I have not done detailed analysis yet, but based on
some vague and preliminary findings.
- Extend join predicate push downs into all table expressions.
Continuation of your work.
- Extend all scalar single table _expression_ pushdowns during
preprocessing into table expressions. (like T1.a+T1.b=5) Continuation
of my scalar predicate work.
- Enable hash-joins on select subqueries. DERBY-781.
- Consider materialization (like a VTI) as one possible execution
path for subqueries where hash-joins are not possible.
- Use invariant interface to determine if an _expression_ could
qualify as predicate or search clause. (for cases like 'name =
upper(c)') DERBY-824 and DERBY-813.
- Push some preprocessing work into bind phase, where possible.
Examples could be constant folding (a = upper('Sam'), re-arranging
predicates like 2>a to a>2. This is causing
missing of correctly identifying predicates early enough to enable
pushing.
- Better join search algorithms could help large query
optimization.
- Enhanced OR/IN processing. (DERBY-47)
- Multiple index scans for one table. Extension of above to cover
cases like a=2 and b=4 with index on both a and b.
- I am sure there are others....
I can file JIRA improvement entries if this list is correct.
Does
that answer your question?
Army
Completely... Thanks.
Satheesh
|
