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
----------------------------------------------------------------
2009-01-08 15:44:40.851 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby - 10.4.2.0 - (689064): instance a816c00e-011e-b6ea-b0f9-00000e8bcae0
on database directory /flash01/summa-control/persistent/storage
Database Class Loader started - derby.database.classpath=''
2009-01-08 15:45:11.313 GMT Thread[main,5,main] (XID = 3871351), (SESSIONID = 0), UPDATE summa_records SET base='foobar' WHERE id IN ( SELECT parentId FROM summa_relations WHERE childId='horizon_2615441') ******* Update ResultSet using row locking:
deferred: false
Rows updated = 1
Indexes updated = 0
Execute Time = 0
Normalize ResultSet:
Number of opens = 1
Rows seen = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 1561194.00
optimizer estimated cost: 14224164.32
Source result set:
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 1561194.00
optimizer estimated cost: 14224164.32
Source result set:
Nested Loop Exists Join ResultSet:
Number of opens = 1
Rows seen from the left = 1563362
Rows seen from the right = 1
Rows filtered = 0
Rows returned = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 1561194.00
optimizer estimated cost: 14224164.32
Left result set:
Table Scan ResultSet for SUMMA_RECORDS at read committed isolation level using exclusive row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1563362
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of pages visited=50434
Number of rows qualified=1563362
Number of rows visited=1702326
Scan type=heap
start position:
null stop position:
null qualifiers:
None
optimizer estimated row count: 1561194.00
optimizer estimated cost: 6863915.21
Right result set:
Index Scan ResultSet for SUMMA_RELATIONS using index PC at read committed isolation level using share row locking chosen by the optimizer
Number of opens = 1563362
Rows seen = 1
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=4691130
Number of rows qualified=1
Number of rows visited=1563362
Scan type=btree
Tree height=3
start position:
>= on first 2 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 2 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 1561194.00
optimizer estimated cost: 7360249.11
