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 (nike.apache.org: local policy)
Date: Mon, 12 Jan 2009 13:49:41 +0100
From: Knut Anders Hatlen <Knut.Hatlen@Sun.COM>
Subject: Re: Bad performance with UPDATE on a nested SELECT
In-reply-to: <1231761599.3683.18.camel@pc280>
Sender: Knut.Hatlen@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <wjo8wsd0vgd6.fsf@sun.com>
Organization: Sun Microsystems
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
References: <1231761599.3683.18.camel@pc280>
User-Agent: Gnus/5.110011 (No Gnus v0.11) Emacs/23.0.60 (usg-unix-v)

Mikkel Kamstrup Erlandsen <mke@statsbiblioteket.dk> writes:

> Hi list,
>
> I am seeing some bad performance on an UPDATE on a nested SELECT. The
> query in case takes about 30s, but I think it should be a lot faster
> because the same query on a PostgresQL runs < 1 ms... Anyway, here's the
> setup:
>
> I have two tables 'records' and 'relations'.
>
> The 'records' table stores a bunch of records along with some metadata.
> It has a unique index 'i' on the 'id' column and a normal index 'm' on
> the  'mtime' column. This table holds about 1.5M rows.
>
> The 'relations' table stores parent/child relations between records and
> has two rows 'parentId' and 'childId'. There is a unique index 'pc' on
> (parentId,childId) and a normal index 'c' on childId. This table holds
> about 35k rows.

Hi Mikkel,

Derby sometimes has problems with the cardinality statistics getting
stale in non-unique indexes, which may lead to bad decisions being made
by the optimizer. Do you still see the problem if you compress the
relations table (alternatively, you could drop the index on childId and
recreate it)? If this speeds up the execution, you're probably
experiencing these issues:

https://issues.apache.org/jira/browse/DERBY-269
https://issues.apache.org/jira/browse/DERBY-3788

-- 
Knut Anders