db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Nielsen <Thomas.Niel...@Sun.COM>
Subject Optimizer question
Date Fri, 07 Dec 2007 11:10:50 GMT
I have a optimizer question I was hoping for someone well versed could 
provide some input to:

As part of my work on DERBY-2998, the implementation of the ROW_NUMBER() 
window function, I'm having trouble with the optimizer and its (lack of) 
pushing predicates down the query tree.

The scenario I have is a nested SELECT with a WHERE clause on the outer 
SELECT referring an aliased column in the inner SELECT. The query in 
question is this one:

SELECT * FROM (SELECT ROW_NUMBER() AS R, T.* FROM T) AS TR WHERE R < 3;

I've attached the output from derby.language.logQueryPlan along with 
derby.debug.true=DumpOptimizedTree for reference.

Reading the log of the optimized tree and query plan, I deduce the 
optimizer does not push the where predicate(s) into the inner select 
like it should (ought to), and I can't seem to wrap my head around why 
this isn't so, and where in the code this was supposed to have happened. 
I've probably just gone blind at this, and I may be interpreting the 
graph from DumpOptimizedTree wrong for all I know.

Ideally I'd like the optimizer to push the where predicate all the way 
into the OLAPResultSet node, and stop the execution there once the 
restricion given in the where predicate is met. In this particular case 
that decision is based on the fact that row_number() is ever increasing. 
But that's all part of the restriction evaluation, and at this stage I 
have no restriction/where predicate in OLAPResultSet - even after 
optimization.

To add to the info I see the same kind of queryplan in the similar query 
where the aliased expression is exchanged with 'a*b':

SELECT * FROM (SELECT a*b AS R, T.* FROM T) AS TR WHERE R < 3;

So I'm starting to wonder if this is a general Derby problem, where 
restrictions on aliased expression columns are not properly optimized?

Your comments and input is greatly appreciated!

Cheers,
Thomas
-- 
Thomas Nielsen

Mime
View raw message