From derby-dev-return-21053-apmail-db-derby-dev-archive=db.apache.org@db.apache.org Thu May 25 21:25:18 2006
Return-Path:
Delivered-To: apmail-db-derby-dev-archive@www.apache.org
Received: (qmail 39770 invoked from network); 25 May 2006 21:25:18 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199)
by minotaur.apache.org with SMTP; 25 May 2006 21:25:18 -0000
Received: (qmail 47108 invoked by uid 500); 25 May 2006 21:25:15 -0000
Delivered-To: apmail-db-derby-dev-archive@db.apache.org
Received: (qmail 47038 invoked by uid 500); 25 May 2006 21:25:15 -0000
Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help:
List-Unsubscribe:
List-Post:
List-Id:
Reply-To:
Delivered-To: mailing list derby-dev@db.apache.org
Received: (qmail 47001 invoked by uid 99); 25 May 2006 21:25:15 -0000
Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49)
by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 25 May 2006 14:25:15 -0700
X-ASF-Spam-Status: No, hits=0.0 required=10.0
tests=
X-Spam-Check-By: apache.org
Received: from [209.237.227.198] (HELO brutus.apache.org) (209.237.227.198)
by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 25 May 2006 14:25:14 -0700
Received: from brutus (localhost [127.0.0.1])
by brutus.apache.org (Postfix) with ESMTP id 4EA2D71429D
for ; Thu, 25 May 2006 21:24:32 +0000 (GMT)
Message-ID: <27836083.1148592272319.JavaMail.jira@brutus>
Date: Thu, 25 May 2006 21:24:32 +0000 (GMT+00:00)
From: "A B (JIRA)"
To: derby-dev@db.apache.org
Subject: [jira] Resolved: (DERBY-1073) Reset optimizer timeout for
subqueries on a per-round basis to allow consideration of plans that use
pushed predicates.
In-Reply-To: <1286534507.1141400712594.JavaMail.jira@ajax.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-Virus-Checked: Checked by ClamAV on apache.org
X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N
[ http://issues.apache.org/jira/browse/DERBY-1073?page=all ]
A B resolved DERBY-1073:
------------------------
Fix Version: 10.2.0.0
10.1.2.5
10.1.2.4
Resolution: Fixed
Timeout fix was checked into 10.1 with svn 397682, so I'm marking this issue as resolved. Thanks for the commits, Satheesh.
> Reset optimizer timeout for subqueries on a per-round basis to allow consideration of plans that use pushed predicates.
> -----------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1073
> URL: http://issues.apache.org/jira/browse/DERBY-1073
> Project: Derby
> Type: Sub-task
> Components: Performance
> Versions: 10.2.0.0
> Reporter: A B
> Assignee: A B
> Priority: Minor
> Fix For: 10.2.0.0, 10.1.2.4, 10.1.2.5
> Attachments: d1073_timeoutFix_v1.patch, d1073_v1.patch, d1073_v2.patch
>
> I wanted to file this as subtask to DERBY-805, but since DERBY-805 is itself a subtask to DERBY-649, I was not able to do so. So I'm creating this issue as a(nother) subtaks for DERBY-649.
> [ Based on derby-dev thread found here: http://article.gmane.org/gmane.comp.apache.db.derby.devel/16007 ]
> If we have a query such as:
> select from
> (select t1.i, t2.j from t1, t2 where ) X1,
> T3
> where
> then we would have one "outer" query and one "subquery". The outer query would be "select from X1, T3", the subquery would be "select t1.i, t2.j from t1, t2".
> In this case the Derby optimizer will create two instances of OptimizerImpl: one for the outer query (call it OI_OQ) and one for the subquery (call it OI_SQ). Each OptimizerImpl has its own timeout "clock" that it initializes at creation time--but never resets. If timeout occurs, the OptimizerImpl will stop searching for "the" best plan and will just take the best plan found so far.
> That said, for every permutation of the outer query a call will be made to optimize the subquery. To simplify things, let's assume there are only two permutations of the outer query: one with join order {X1, T3} and another with join order {T3, X1}.
> Now let's say we're looking at the first permutation {X1, T3}. OI_OQ will make a call to optimize the subquery represented by OI_SQ. Let's further say that the subquery tries some permutation {T1, T2} and then times out. It then returns the plan information for {T1, T2} to the outer query. The outer query, which has *not* yet timed out, then decides to try its second permutation {T3, X1}. So it again makes a call to optimize the subquery. In this case, the subquery--which has already timed out--will *immediately* return without trying to optimize anything. The outer query will then make a decision about its second permutation based on the un-optimized subquery's plan results.
> 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 DERBY-805, though, Derby has acquired the ability to push 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. But because the timeout mechanism is never reset, we could end up skipping the second optimization phase of the subquery, which means we never get a chance to see how much the outer predicates can help, and thus we could end up skipping over some plans that have the potential to give us significant performance improvement.
> So resolution of this issue would involve resetting the timeout state for subqueries to allow the Derby optimizer to consider plans that rely on pushed predicates.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira