db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Bad performance with UPDATE on a nested SELECT
Date Mon, 12 Jan 2009 12:49:41 GMT
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

Mime
View raw message