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
qualification.
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.
Dan.
