X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 95624D1D722 for ; Tue, 2 Dec 2003 16:41:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 49804-08 for ; Tue, 2 Dec 2003 12:40:53 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id E4B61D1DAF2 for ; Tue, 2 Dec 2003 12:40:47 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 1D99E36CF3; Tue, 2 Dec 2003 11:40:52 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1ARDa0-00046L-00; Tue, 02 Dec 2003 11:40:52 -0500 To: Erik Norvelle Cc: pgsql-performance@postgresql.org Subject: Re: Update performance ... is 200, 000 updates per hour what I should expect? References: In-Reply-To: From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 02 Dec 2003 11:40:51 -0500 Message-ID: <87u14j9nqk.fsf@stark.dyndns.tv> Lines: 54 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/25 X-Sequence-Number: 4885 Erik Norvelle writes: > Here's the query I am running: > update indethom > set query_counter = nextval('s2.query_counter_seq'), -- Just for keeping track of how fast the query is running > sectref = (select clavis from s2.sectiones where > s2.sectiones.nomeoper = indethom.nomeoper > and s2.sectiones.refere1a = indethom.refere1a and s2.sectiones.refere1b = indethom.refere1b > and s2.sectiones.refere2a = indethom.refere2a and s2.sectiones.refere2b = indethom.refere2b > and s2.sectiones.refere3a = indethom.refere3a and s2.sectiones.refere3b = indethom.refere3b > and s2.sectiones.refere4a = indethom.refere4a and s2.sectiones.refere4b = indethom.refere4b); > > Here´s the query plan: > QUERY PLAN > ------------------------------------------------------------------------------------- > Seq Scan on indethom (cost=0.00..1310352.72 rows=10631972 width=212) > SubPlan > -> Index Scan using sectiones_ndx on sectiones (cost=0.00..6.03 rows=1 width=4) > Index Cond: ((nomeoper = $0) AND (refere1a = $1) AND (refere1b = $2) AND (refere2a = $3) AND (refere2b = $4) AND (refere3a = $5) AND (refere3b = $6) AND (refere4a = $7) AND (refere4b = $8)) > (4 rows) Firstly, you might try running "vacuum full" on both tables. If there are tons of extra dead records that are left-over they could be slowing down the update. This isn't the fastest possible plan but it's pretty good. You might be able to get it somewhat faster using the non-standard "from" clause on the update statement. update indethom set sectref = clavis from sectiones where sectiones.nomeoper = indethom.nomeoper and sectiones.refere1a = indethom.refere1a and sectiones.refere1b = indethom.refere1b and sectiones.refere2a = indethom.refere2a and sectiones.refere2b = indethom.refere2b and sectiones.refere3a = indethom.refere3a and sectiones.refere3b = indethom.refere3b and sectiones.refere4a = indethom.refere4a and sectiones.refere4b = indethom.refere4b This might be able to use a merge join which will take longer to get started because it has to sort both tables, but might finish faster. You might also try just paring the index down to just the two or three most useful columns. Is it common that something matches refere1a and refere1b but doesn't match the remaining? A 8-column index is a lot of overhead. I'm not sure how much that effects lookup times but it might be substantial. -- greg