X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 12D203A4417 for ; Thu, 6 Jan 2005 03:42:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23846-08 for ; Thu, 6 Jan 2005 03:41:37 +0000 (GMT) Received: from www.postgresql.com (unknown [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id E43293A443E for ; Thu, 6 Jan 2005 03:40:35 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.250.190.2]) by www.postgresql.com (Postfix) with ESMTP id 85231739541 for ; Thu, 6 Jan 2005 03:39:51 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id A16E83197C; Thu, 6 Jan 2005 04:33:09 +0100 (MET) From: William Yu X-Newsgroups: pgsql.performance Subject: Re: Low Performance for big hospital server .. Date: Wed, 05 Jan 2005 19:31:10 -0800 Organization: Hub.Org Networking Services Lines: 21 Message-ID: References: <1104831107.41da6283add0c@webmail.moph.go.th> <20050104094657.540232d7.frank@wiles.org> <1104939342.41dc094ecb1cf@webmail.moph.go.th> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.3) Gecko/20040910 X-Accept-Language: en-us, en In-Reply-To: <1104939342.41dc094ecb1cf@webmail.moph.go.th> To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/54 X-Sequence-Number: 9744 amrit@health2.moph.go.th wrote: > Now I turn hyperthreading off and readjust the conf . I found the bulb query > that was : > update one flag of the table [8 million records which I think not too much] > .When I turned this query off everything went fine. > I don't know whether update the data is much slower than insert [Postgresql > 7.3.2] and how could we improve the update method? UPDATE is expensive. Under a MVCC setup, it's roughtly the equivalent of DELETE + INSERT new record (ie, old record deprecated, new version of record. Updating 8 million records would be very I/O intensive and probably flushes your OS cache so all other queries hit disk versus superfast memory. And if this operation is run multiple times during the day, you may end up with a lot of dead tuples in the table which makes querying it deadly slow. If it's a dead tuples issue, you probably have to increase your freespace map and vacuum analyze that specific table more often. If it's an I/O hit issue, a lazy updating procedure would help if the operation is not time critical (eg. load the record keys that need updating and loop through the records with a time delay.)