Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 045679FA4C9 for ; Thu, 9 Aug 2007 12:32:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 43398-01 for ; Thu, 9 Aug 2007 12:32:19 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 12C609FB58A for ; Thu, 9 Aug 2007 12:32:19 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id ECEF456476; Thu, 9 Aug 2007 10:32:18 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Thu, 9 Aug 2007 10:32:15 -0500 Date: Thu, 9 Aug 2007 10:32:15 -0500 From: Decibel! To: Merlin Moncure Cc: Mark Makarowsky , pgsql-performance@postgresql.org Subject: Re: Update table performance Message-ID: <20070809153215.GQ20424@nasby.net> References: <20070807214227.GR25704@nasby.net> <670773.87500.qm@web32209.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="Hmb9n29fjuiORm2l" Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:070809:mmoncure@gmail.com::Gkwo2xaddyxwc4n5:0000000000000000000 0000000000000000000000004+fR X-Hashcash: 1:20:070809:bedrockconstruction@yahoo.com::6UoWmF6V7MBh67mL:00000000 0000000000000000000000007aOR X-Hashcash: 1:20:070809:pgsql-performance@postgresql.org::09Smq+tteWFDY7NH:00000 0000000000000000000000000c1J X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/86 X-Sequence-Number: 26216 --Hmb9n29fjuiORm2l Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Thu, Aug 09, 2007 at 06:04:09PM +0530, Merlin Moncure wrote: > That said, 'update' is the slowest operation for postgresql relative > to other databases that are not MVCC. Actually, it depends on how you do MVCC. In Oracle, DELETE is actually the most expensive operation, because they have to not only remove the row from the heap, they have to copy it to the undo log. And they need to do something with indexes as well. Whereas we just update 4 bytes in the heap and that's it. An UPDATE in Oracle OTOH just needs to store whatever fields have changed in the undo log. If you haven't messed with indexed fields, it doesn't have to touch those either. --=20 Decibel!, aka Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) --Hmb9n29fjuiORm2l Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.3 (FreeBSD) iD8DBQFGuzN/dO30qud8SkgRAiGqAJ9PG/QmEYsVEm/Svc3lvNkSAaEjLgCfd3oK MQIApbc1rROC4acnLpk/LQk= =2IFq -----END PGP SIGNATURE----- --Hmb9n29fjuiORm2l--