Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id D8E572E003B for ; Fri, 2 May 2008 18:23:51 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 20892-02 for ; Fri, 2 May 2008 18:23:42 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from westnet.com (westnet.com [216.187.52.2]) by developer.postgresql.org (Postfix) with ESMTP id 7B5742E0031 for ; Fri, 2 May 2008 18:23:47 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id m42LNjv9005786; Fri, 2 May 2008 17:23:45 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id m42LNjoR005783; Fri, 2 May 2008 17:23:45 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Fri, 2 May 2008 17:23:45 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: Alexy Khrabrov cc: pgsql-performance@postgresql.org Subject: Re: two memory-consuming postgres processes In-Reply-To: Message-ID: References: <27A4D379-2553-496D-A96C-102F2A5E62C2@gmail.com> <21403.1209759217@sss.pgh.pa.us> <72E02D29-848B-467A-AE6B-401568010254@gmail.com> <481B8160.2090106@emolecules.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/32 X-Sequence-Number: 30000 On Fri, 2 May 2008, Alexy Khrabrov wrote: > I created several indices for the primary table, yes. That may be part of your problem. All of the indexes all are being updated along with the main data in the row each time you touch a record. There's some optimization there in 8.3 but it doesn't make index overhead go away completely. As mentioned already, the optimal solution to problems in this area is to adjust table normalization as much as feasible to limit what you're updating. > Basically, the derived data is not critical at all, -- can I turn (1) > off transactional behavior for an UPDATE, What you can do is defer transaction commits to only happen periodically rather than all the time by turning off syncronous_commit and increasing wal_writer_delay; see http://www.postgresql.com.cn/docs/8.3/static/wal-async-commit.html > (2) should I care about vacuuming being done on the fly when saving RAM, > or need I defer it/manage it manually? It's hard to speculate from here about what optimal vacuum behavior will be. You might find it more efficient to turn autovacuum off when doing these large updates. The flip side is that you'll be guaranteed to end up with more dead rows in the table and that has its own impact later. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD