Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: neutral (asf.osuosl.org: local policy)
Message-ID: <44079CF6.6060204@sbcglobal.net>
Date: Thu, 02 Mar 2006 17:33:42 -0800
From: Army <qozinx@sbcglobal.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
 rv:1.7.1) Gecko/20040707
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Re: [OPTIMIZER] Optimizer "timeout" for subqueries?
References: <44079238.90307@sbcglobal.net>
In-Reply-To: <44079238.90307@sbcglobal.net>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Army wrote:

> This hasn't really been an issue to date because the "best plan" chosen 
> by the subquery is typically independent of the outer query's current 
> permutation--with the exception of "outerCost", which is passed in from 
> the outer query and is factored into the subquery's cost estimates.  
> Because of this relative independence, the plan chosen by the subquery 
> would rarely (if ever?) change with different permutations of the outer 
> query, so if the subquery timed out once there was no point in trying to 
> re-optimize it again later.
> 
> With my changes for DERBY-805, though, I'm introducing the notion of 
> pushing predicates from outer queries down into subqueries--which means 
> that the outer join order can have a very significant impact on the plan 
> chosen by the subquery.

After re-reading what I wrote, I realized we have a basis for logic to only 
reset the timeout state when it could potentially be beneficial to do so--i.e. 
when we have predicates pushed from the outer query.

I could add logic to check to see if the OptimizerImpl has predicates from outer 
queries and, if so, then reset the timeout state; otherwise, since the 
subquery's "best plan" probably won't change much from the previous round, we 
would leave the timeout state as it is.  This approach allows the optimizer to 
consider plans that use pushed predicates while at the same time reducing the 
likelihood that queries which have timed out in the past (prior to 10.2) would 
now take longer to compile.

I'll code that up and run derbyall tonight; in the meantime, I'd still like to 
hear comments/suggestions from any readers out there...

Thanks,
Army