db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <qoz...@gmail.com>
Subject Re: Optimizer question
Date Fri, 07 Dec 2007 16:33:50 GMT
Thomas Nielsen wrote:
> 
> 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.

[ snip ]

> 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?

I think this is the key, yes.  When I ran the above query and did some 
tracing, Derby correctly "categorized" the predicate as "pushable" (see 
Predicate.categorize(), and especially the "categorize()" method of 
ColumnReference) and then tried to push the predicate down into the 
inner SELECT query.  In ProjectRestrictNode.pushExpressions() we see:


  /* If this is a PRN above a SelectNode, probably due to a
   * view or derived table which couldn't be flattened, then see
   * if we can push any of the predicates which just got pushed
   * down to our level into the SelectNode.
   */
   if (pushPList != null && (childResult instanceof SelectNode))
   {
     pushPList.pushExpressionsIntoSelect(
       (SelectNode) childResult, false);
   }

which brings us to PredicateList.pushExpressionIntoSelect(...), where we 
find the following:

   ColumnReference ref = (ColumnReference)e.nextElement();
   if (!ref.pointsToColumnReference())
   {
     state = false;
     break;
   }

That is to say, if the ColumnReference's source does not in turn point 
to another column reference, then "state" will become false and the 
predicate will *not* be pushed.  For the example of "a*b as R", the 
column reference "R" points to a BinaryArithmeticOperatorNode (for the 
expression "a*b"), which is not a ColumnReference.  Thus the predicate 
is *not* pushed down into the SELECT.

I'm guessing the same thing happens for your ROW_NUMBER() query.  Since 
ROW_NUMBER() is not a ColumnReference, the predicate is not pushed down 
into the SELECT, which means it will be enforced by the 
ProjectRestrictNode which sits above the SelectNode.  Hence the 
following at the top of your query plan:

******* Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 5
Rows filtered = 3
*restriction = true*  <== Predicate applied at the level of the PRN

So I think that explains the behavior you're seeing.  As to whether or 
not this "incorrect", I'm not sure.  Seems more likely that this is a 
case where support for arbitrary expressions under the ColumnReference 
was perhaps too complex (ex. subqueries, method calls, etc.) and so no 
support for that was added.

You could perhaps change "ref.pointsToColumnReference()" in the above 
code to account for OLAP-related functions like ROW_NUMBER(), but I do 
not know off-hand if that would/could lead to problems down the road. 
Might be worth a shot, though, to see what happens...

Does that help?

Army


Mime
View raw message