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 190C9D1D93E for ; Tue, 2 Dec 2003 16:29:41 +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 50317-01 for ; Tue, 2 Dec 2003 12:29:14 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 31C85D1D92C for ; Tue, 2 Dec 2003 12:29:10 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 4BB53354FA; Tue, 2 Dec 2003 08:29:15 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 4A16A354F8; Tue, 2 Dec 2003 08:29:15 -0800 (PST) Date: Tue, 2 Dec 2003 08:29:15 -0800 (PST) From: Stephan Szabo To: Erik Norvelle Cc: pgsql-performance@postgresql.org Subject: Re: Update performance ... is 200,000 updates per hour In-Reply-To: Message-ID: <20031202082100.T87630@megazone.bigpanda.com> References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: QUOTED-PRINTABLE X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/23 X-Sequence-Number: 4883 On Tue, 2 Dec 2003, Erik Norvelle wrote: > ** My question has to do with whether or not I am getting maximal speed > out of PostgreSQL, or whether I need to perform further optimizations. > I am currently getting about 200,000 updates per hour, and updating the > entire 10 million rows thus requires 50 hours, which seems a bit much. Well, it doesn't entirely surprise me much given the presumably 10 million iterations of the index scan that it's doing. Explain analyze output (even over a subset of the indethom table by adding a where clause) would probably help to get better info. I'd suggest seeing if something like: update indethom set query_counter=3D...,sectref=3Ds.clavis FROM s2.sectiones s where s2.sectiones.nomeoper =3D indethom.nomeoper and ...; tries a join that might give a better plan. > Here's the query I am running: > update indethom > set query_counter =3D nextval('s2.query_counter_seq'), -- Just > for keeping track of how fast the query is running > sectref =3D (select clavis from s2.sectiones where > s2.sectiones.nomeoper =3D indethom.nomeoper > and s2.sectiones.refere1a =3D indethom.refere1a and > s2.sectiones.refere1b =3D indethom.refere1b > and s2.sectiones.refere2a =3D indethom.refere2a and > s2.sectiones.refere2b =3D indethom.refere2b > and s2.sectiones.refere3a =3D indethom.refere3a and > s2.sectiones.refere3b =3D indethom.refere3b > and s2.sectiones.refere4a =3D indethom.refere4a and > s2.sectiones.refere4b =3D indethom.refere4b); > > Here=B4s the query plan: > QUERY PLAN > ------------------------------------------------------------------------ > ------------- > Seq Scan on indethom (cost=3D0.00..1310352.72 rows=3D10631972 width=3D= 212) > SubPlan > -> Index Scan using sectiones_ndx on sectiones (cost=3D0.00..6.03 > rows=3D1 width=3D4) > Index Cond: ((nomeoper =3D $0) AND (refere1a =3D $1) AND > (refere1b =3D $2) AND (refere2a =3D $3) AND (refere2b =3D $4) AND (refere= 3a =3D > $5) AND (refere3b =3D $6) AND (refere4a =3D $7) AND (refere4b =3D $8)) > (4 rows)