Mikkel Kamstrup Erlandsen <[email protected]> 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
