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 0252C3A44E2 for ; Thu, 6 Jan 2005 12:15:42 +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 59648-01 for ; Thu, 6 Jan 2005 12:15:33 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.193]) by svr1.postgresql.org (Postfix) with ESMTP id 3161E3A445F for ; Thu, 6 Jan 2005 12:15:33 +0000 (GMT) Received: by rproxy.gmail.com with SMTP id a36so69007rnf for ; Thu, 06 Jan 2005 04:15:33 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=qFFJMc6oIfyfWHI90u2eSP/mvMllXTd+t9ltiHH9V4dxEMeXgTnyCZfjn8eiKzUPG3RnMKyK7n9Wwy6CVcNGh1UO7OQRo6sge+elUC7EyoyvHwyDJjgKF/wPqQP9E/ovUTpUR03/kT53MOJZVOygog3m0/l1sHCaknHDZCpiSFc= Received: by 10.38.92.16 with SMTP id p16mr87628rnb; Thu, 06 Jan 2005 04:15:33 -0800 (PST) Received: by 10.38.163.47 with HTTP; Thu, 6 Jan 2005 04:15:33 -0800 (PST) Message-ID: <758d5e7f050106041510f07dc5@mail.gmail.com> Date: Thu, 6 Jan 2005 13:15:33 +0100 From: Dawid Kuroczko Reply-To: Dawid Kuroczko To: pgsql-performance@postgresql.org Subject: Re: Low Performance for big hospital server .. In-Reply-To: <1104939342.41dc094ecb1cf@webmail.moph.go.th> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <1104831107.41da6283add0c@webmail.moph.go.th> <20050104094657.540232d7.frank@wiles.org> <1104939342.41dc094ecb1cf@webmail.moph.go.th> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.54 tagged_above=0 required=5 tests=FROM_ENDS_IN_NUMS, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200501/56 X-Sequence-Number: 9746 On Wed, 5 Jan 2005 22:35:42 +0700, 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] 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... Regards, Dawid