db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: Optimizer question
Date Fri, 07 Dec 2007 22:00:45 GMT
Bryan Pendleton 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.
> Hi Thomas & Army, this is a very interesting discussion and I'm learning
> a lot by following it, so please keep sending this useful information
> along as that way we all get a deeper understanding!
> It seems to me that there are perhaps several types of restrictions:
>  - Restrictions that can end up being pushed all the way down into
>    the store, to be satisfied by index qualification.

These are actually broken into two types and it is not just index 

  1) Start/stop keys for index scans (limits the set of rows returned 
from a index scan)

  2) qualifications executed by the conglomerate. Basically pushing the 
qualification as close to the data as possible, which will return in 
fewer rows being returned to the calling result set. As a conglomerate 
implementation detail the qualifications could be performed as the 
columns were being read from the page, thus allowing the conglomerate to 
avoid reading a column if an earlier column fails some qualification [I 
don't think Derby does this].

So, a heap scan of 1 million rows could have a qualifier pushed down to 
it that resulted in 100 rows being returned to the next result set, 
instead of 1 million and performing the qualification higher.


View raw message