db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mikkel Kamstrup Erlandsen <...@statsbiblioteket.dk>
Subject Bad performance with UPDATE on a nested SELECT
Date Mon, 12 Jan 2009 11:59:59 GMT
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.

The problematic query looks like:

UPDATE records
SET base='my_base'
WHERE id IN (
  SELECT parentId
  FROM relations
  WHERE childId='id_1');

The right SELECT returns two rows (and in general 0-3 or something like
that). If I instead of the SELECT hardcode two ids, making the right
part look like "IN ('id_2', 'id_3')" the query runs in < 1ms.

It would appear that Derby iterates over the entire 'records' table or
something like that. This also appears to be the case as far as I can
read from the attached query plan...

Any pointers or ideas on how to tackle this are most appreciated.

Cheers,
Mikkel

Mime
View raw message