db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mikkel Kamstrup Erlandsen <...@statsbiblioteket.dk>
Subject Re: Bad performance with UPDATE on a nested SELECT
Date Mon, 12 Jan 2009 21:45:16 GMT
On Mon, 2009-01-12 at 13:49 +0100, Knut Anders Hatlen wrote:
> 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
> 

Hi Knut,

I just tried compressing both the 'relations' and the 'records' table.
This makes the query run in about 20s. So a small win, but nothing
significant.

I have attached the query plan that I got when running with compressed
indexes. To me it looks just like the first one i attached, but I don't
speak fluent "query plan".

Cheers,
Mikkel

Mime
View raw message