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 4AC9B3A45A0 for ; Thu, 6 Jan 2005 16:32:38 +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 13249-08 for ; Thu, 6 Jan 2005 16:32:18 +0000 (GMT) Received: from webmail.moph.go.th (health.moph.go.th [203.157.0.1]) by svr1.postgresql.org (Postfix) with SMTP id A8F073A4590 for ; Thu, 6 Jan 2005 16:32:16 +0000 (GMT) Received: (qmail 4850 invoked from network); 6 Jan 2005 16:34:47 -0000 Received: from unknown (HELO localhost) (127.0.0.1) by localhost with SMTP; 6 Jan 2005 16:34:47 -0000 Received: from webmail.moph.go.th ([127.0.0.1]) by localhost (webmail.moph.go.th [127.0.0.1]) (amavisd-new, port 10024) with SMTP id 02544-48 for ; Thu, 6 Jan 2005 23:34:44 +0700 (ICT) Received: (qmail 4828 invoked from network); 6 Jan 2005 16:34:44 -0000 Received: from unknown (HELO localhost) (203.157.0.1) by health.moph.go.th with SMTP; 6 Jan 2005 16:34:44 -0000 Received: from 203.157.100.42 ([203.157.100.42]) by webmail.moph.go.th (IMP) with HTTP for ; Thu, 6 Jan 2005 23:34:43 +0700 Message-ID: <1105029283.41dd68a3ead99@webmail.moph.go.th> Date: Thu, 6 Jan 2005 23:34:43 +0700 From: amrit@health2.moph.go.th To: Dawid Kuroczko Cc: PGsql-performance Subject: Re: Low Performance for big hospital server .. References: <1104831107.41da6283add0c@webmail.moph.go.th> <20050104094657.540232d7.frank@wiles.org> <1104939342.41dc094ecb1cf@webmail.moph.go.th> <758d5e7f050106041510f07dc5@mail.gmail.com> In-Reply-To: <758d5e7f050106041510f07dc5@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=TIS-620 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.2.3 X-Originating-IP: 203.157.100.42 X-Virus-Scanned: by amavisd-new at moph.go.th X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.178 tagged_above=0 required=5 tests=AWL, NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200501/66 X-Sequence-Number: 9756 > Ahh, the huge update. Below are my "hints" I've > found while trying to optimize such updates. > > First of all, does this update really changes this 'flag'? > Say, you have update: > UPDATE foo SET flag = 4 WHERE [blah]; > are you sure, that flag always is different than 4? > If not, then add: > UPDATE foo SET flag = 4 WHERE flag <> 4 AND [blah]; > This makes sure only tuples which actually need the change will > receive it. [ IIRC mySQL does this, while PgSQL will always perform > UPDATE, regardless if it changes or not ]; > > Divide the update, if possible. This way query uses > less memory and you may call VACUUM inbetween > updates. To do this, first SELECT INTO TEMPORARY > table the list of rows to update (their ids or something), > and then loop through it to update the values. > > I guess the problem with huge updates is that > until the update is finished, the new tuples are > not visible, so the old cannot be freed... Yes, very good point I must try this and I will give you the result , thanks a lot. Amrit Thailand