On Mon, 2009-01-12 at 13:49 +0100, Knut Anders Hatlen wrote:
> 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
>
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
----------------------------------------------------------------
2009-01-12 21:20:23.471 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby - 10.4.2.0 - (689064): instance a816c00e-011e-ccb7-7b1a-00000e8bcb08
on database directory /home/summa/tmp/horizon_db
Database Class Loader started - derby.database.classpath=''
2009-01-12 21:20:51.711 GMT Thread[main,5,main] (XID = 4078867), (SESSIONID = 0), UPDATE summa_records SET base='my_base' WHERE id IN (SELECT parentId FROM summa_relations WHERE childId='horizon_2332668') ******* 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: 1563367.00
optimizer estimated cost: 13574131.34
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: 1563367.00
optimizer estimated cost: 13574131.34
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: 1563367.00
optimizer estimated cost: 13574131.34
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=45458
Number of rows qualified=1563362
Number of rows visited=1563362
Scan type=heap
start position:
null stop position:
null qualifiers:
None
optimizer estimated row count: 1563367.00
optimizer estimated cost: 6203637.62
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=4722555
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: 1563367.00
optimizer estimated cost: 7370493.72
