Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; CHARSET=US-ASCII
Date: Wed, 21 Oct 2009 14:27:03 +0200
From: Knut Anders Hatlen <Knut.Hatlen@Sun.COM>
Subject: Re: Badly performing WHERE caluse
In-reply-to: <4ABA4AA0.2080001@amberpoint.com>
Sender: Knut.Hatlen@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <wjo8vdi9hqzs.fsf@sun.com>
Organization: Sun Microsystems
References: <25531166.post@talk.nabble.com> <4ABA4AA0.2080001@amberpoint.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (usg-unix-v)

Bryan Pendleton <bpendleton@amberpoint.com> writes:

>> Do people agree that this appears to be a bug with the query planner, as the
>> selection of the most appropriate index should not be influenced by static
>> conditions such as 1=1 or 2>1?
>
> I don't know of any reason why the 1=1 versus 2>1 should have affected the
> query plan chosen.

Sorry for the late response. I just came across some code in the engine
that made me remember this thread. It looks like Derby doesn't treat 1=1
and 2>1 as boolean constants during optimization, so it takes the
selectivity from the = operator and the > operator, respectively.

The = operator has selectivity 0.1, and > has selectivity 0.33. This
means that the optimizer thinks 1=1 will match 10% of the rows and
2>1 will match 33% of the rows. This is apparently enough to make the
optimizer choose another plan in this case. Of course, both of the
predicates will match 100% of the rows, so the the information that's
given to the optimizer is inaccurate in any case.

Logged here: https://issues.apache.org/jira/browse/DERBY-4416

-- 
Knut Anders