From derby-user-return-10352-apmail-db-derby-user-archive=db.apache.org@db.apache.org Mon Jan 12 21:45:48 2009 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 36835 invoked from network); 12 Jan 2009 21:45:48 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 12 Jan 2009 21:45:48 -0000 Received: (qmail 25765 invoked by uid 500); 12 Jan 2009 21:45:46 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 25740 invoked by uid 500); 12 Jan 2009 21:45:46 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 25731 invoked by uid 99); 12 Jan 2009 21:45:46 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 12 Jan 2009 13:45:46 -0800 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [130.225.24.68] (HELO sbexch03.sb.statsbiblioteket.dk) (130.225.24.68) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 12 Jan 2009 21:45:37 +0000 Received: from [127.0.0.1] (130.225.25.246) by sbexch03.sb.statsbiblioteket.dk (130.225.24.68) with Microsoft SMTP Server id 8.1.336.0; Mon, 12 Jan 2009 22:45:16 +0100 Subject: Re: Bad performance with UPDATE on a nested SELECT From: Mikkel Kamstrup Erlandsen To: Derby Discussion In-Reply-To: References: <1231761599.3683.18.camel@pc280> Content-Type: multipart/mixed; boundary="=-xdrSYpNnT436ZpmzdnUz" Date: Mon, 12 Jan 2009 22:45:16 +0100 Message-ID: <1231796716.16875.3.camel@delight> MIME-Version: 1.0 X-Mailer: Evolution 2.24.2 X-Virus-Checked: Checked by ClamAV on apache.org --=-xdrSYpNnT436ZpmzdnUz Content-Type: text/plain Content-Transfer-Encoding: 7bit On Mon, 2009-01-12 at 13:49 +0100, Knut Anders Hatlen wrote: > Mikkel Kamstrup Erlandsen 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 --=-xdrSYpNnT436ZpmzdnUz Content-Disposition: attachment; filename="derby.log" Content-Type: text/x-log; name="derby.log"; charset="UTF-8" Content-Transfer-Encoding: 7bit ---------------------------------------------------------------- 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 --=-xdrSYpNnT436ZpmzdnUz--