Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id A15CF9FBDED for ; Thu, 9 Aug 2007 09:34:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 44324-05 for ; Thu, 9 Aug 2007 09:34:07 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.169]) by postgresql.org (Postfix) with ESMTP id 66FA29FB5E2 for ; Thu, 9 Aug 2007 09:34:12 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so375260ugf for ; Thu, 09 Aug 2007 05:34:09 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=SnsALu0U7sbneSRBl9xGSM16sd2r9d2UEOyu1juY9+wsBT4nwG5G7HwWBw9xcM0F3ACo0coXpNxR9TIeUSvvgL9WdpMpRa+5BIArHzdFyBIYraYyKKWOxRD8zGFFBRF4xCupwnMYul0/E0WBDyec0sEDoj7hltCqfjUJSfdlCGk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=rL/mYxYueMo0aX96+WuwIfZfGlavf0Fc/h2G21l9I7SpcP6a+jia166rxOvAKUvrERGSly/noB3kcv8rSYZm7KJiiPzcq9e49T5lqkDlhHQL4DnX7EkMzp1d0yV5ppTObmRkCYrEFVlpMxnIXUTZ7FuBrVvarBxIAiOc7T1+en4= Received: by 10.78.132.2 with SMTP id f2mr867716hud.1186662849326; Thu, 09 Aug 2007 05:34:09 -0700 (PDT) Received: by 10.78.18.9 with HTTP; Thu, 9 Aug 2007 05:34:09 -0700 (PDT) Message-ID: Date: Thu, 9 Aug 2007 18:04:09 +0530 From: "Merlin Moncure" To: "Mark Makarowsky" Subject: Re: Update table performance Cc: pgsql-performance@postgresql.org In-Reply-To: <670773.87500.qm@web32209.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <20070807214227.GR25704@nasby.net> <670773.87500.qm@web32209.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/80 X-Sequence-Number: 26210 On 8/8/07, Mark Makarowsky wrote: > Can you provide more detail on what you mean by your > two suggestions below: > > Yeah, I've used "vertical partitioning" very > successfully in the past, though I've never done it > for just a single field. I'll typically leave the few > most common fields in the "main" table and pull > everything else into a second table. > > I should mention that if you can handle splitting the > update into multiple transactions, that will help a > lot since it means you won't be doubling the size of > the table. > > I guess I was just surprised by the speed it takes to > update the field in Postgres since on an almost > identical table in FoxPro (400,000 records less), it > updates the table with the same exact update table > statement in about 4 minutes. FoxPro is a single process DBF based system with some sql access. When you update th records, it updates them in place since all the records are fixed size and padded. Be careful with this comparison...while certain operations like the above may feel faster, the locking in foxpro is extremely crude compared to PostgreSQL. There are many other things about dbf systems in general which are pretty lousy from performance perspective. That said, 'update' is the slowest operation for postgresql relative to other databases that are not MVCC. This is balanced by extremely efficient locking and good performance under multi user loads. PostgreSQL likes to be used a certain way...you will find that when used properly it is extremely fast. keep an eye for the HOT feature which will hopefully make 8.3 that will highly reduce the penalty for (small) updates in many cases. merlin