Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2B431650EC8 for ; Thu, 26 Jun 2008 10:55:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 27234-07 for ; Thu, 26 Jun 2008 10:55:03 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from lists.commandprompt.com (host-159.commandprompt.net [207.173.203.159]) by postgresql.org (Postfix) with ESMTP id 0058E650EDD for ; Thu, 26 Jun 2008 10:55:08 -0300 (ADT) Received: from commandprompt.com (227-54-222-209.mycybernet.net [209.222.54.227]) (authenticated bits=0) by lists.commandprompt.com (8.13.8/8.13.8) with ESMTP id m5QDuxv7000492 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Thu, 26 Jun 2008 06:57:02 -0700 Date: Thu, 26 Jun 2008 09:55:01 -0400 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: ??: Postgresql update op is very very slow Message-ID: <20080626135501.GF6918@commandprompt.com> Mail-Followup-To: Andrew Sullivan , pgsql-performance@postgresql.org References: <473587.97248.qm@web32701.mail.mud.yahoo.com> <004b01c8d671$3dec1630$0544000a@hz.ali.com> <486219B6.7050003@enterprisedb.com> <005101c8d773$ff81b2c0$0644000a@hz.ali.com> <48638939.5080500@enterprisedb.com> <486396A9.1030208@postnewspapers.com.au> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <486396A9.1030208@postnewspapers.com.au> User-Agent: Mutt/1.5.17 (2007-11-01) X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0 (lists.commandprompt.com [207.173.203.159]); Thu, 26 Jun 2008 06:57:03 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/170 X-Sequence-Number: 30520 On Thu, Jun 26, 2008 at 09:16:25PM +0800, Craig Ringer wrote: > I think Pg already does in place updates, or close, if the tuples being > replaced aren't referenced by any in-flight transaction. I noticed a while > ago that if I'm doing bulk load/update work, if there aren't any other > transactions no MVCC bloat seems to occur and updates are faster. Are you on 8.3? That may be HOT working for you. MVCC doesn't get turned off if there are no other transactions (it can't: what if another transaction starts part way through yours?). A -- Andrew Sullivan ajs@commandprompt.com +1 503 667 4564 x104 http://www.commandprompt.com/