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 Timeout question
Date Wed, 15 Nov 2006 19:00:45 GMT
Bryan Pendleton wrote:

> So here's the question: as I understand it, the Optimizer
> Timeout mechanism kicks in during optimizer.getNextPermutation().

Yes, that's correct.

> But what if the "inner" while loop has a very large number
> of decorated permutations to cost for this particular pass through
> the outer loop?
> Shouldn't we be checking for timeout in the inner loop as well?

Yes, I've often wondered about that, too, though I usually don't stop to give it 
much thought.  I'm glad you brought it up.

I don't really have much to offer here except that Yes, I think it would be a 
good idea.  Without having dug into it, the one question that I would ask is: 
Are the conglomerates for a given table always going to be retrieved in a fixed 
order?  That is, if I create some table and then create a bunch of indexes on 
that table, will the order in which the optimizer iterates through the 
conglomerates be the same across databases?

Here's why I ask.  Asume we create a table with indexes and we execute some 
query for which the optimizer times out after the d'th decorated permutation in 
the j'th join order permutation.  Then we re-create the same table in a 
different database with the same indexes and execute the same query, and again 
we timeout after the d'th decorated permutation in the j'th join order 
permutation.  Can we say with certainty that the decorated permutations that the 
optimizer saw before timing out will be the same in the two databases?  If not, 
that would make it awfully hard to reproduce query plans across databases, which 
could lead to confusion for both users and developers.

I do not know what the answer to the conglomerate-ordering question is.  The 
conglomerates through which the optimizer iterates are retrieved via a call to 
TableDescriptor.getConglomerateDescriptors(), but the comments for that method 
do not indicate whether the conglomerates are ordered (and if they are, what are 
they ordered on?).  Maybe that information is given someplace else in the 
code--I admit I didn't look very hard.

Having said that, one may be inclined to ask how this applies to the join order 
permutations returned by getNextPermutation().  The answer is that for a given 
query, the order in which the various permutations are costed is consistent 
across all databasees--as described in more detail on the 
"JoinOrderPermutations" wiki page.  So if we assume optimizer timeout after the 
j'th call to getNextPermutation() for the same query (with the same tables/data) 
on two different databases, we should get the same query plan every time.  This 
consistency across databases with respect to the j'th join order permutation 
makes it easier to reproduce query plans.  It also ensures that users with the 
same data and the same queries on different databases will see the same results 
(if we take hardware out of the equation).

That's a lot of quickly-written blabbering that is perhaps more confusing than 
anything.  The short version is simply this:

   1. Does TableDescriptor.getConglomerateDescriptors() always return the 
conglomerates for a table in a predictable order, and if so, what is the 
ordering based on?

   2. Timeout logic within getNextDecoratedPermutation() seems like a good idea 
to me and is certainly something worth looking into more.

#1 is just an inquiry and should *not* be seen as a reason to block #2.  As I 
said, this was just the first question that came to me when I read your email, 
so I thought I'd share it...


View raw message