Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id E0EAD9FB823 for ; Thu, 28 Jun 2007 02:19:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 41480-03 for ; Thu, 28 Jun 2007 02:19:16 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.229]) by postgresql.org (Postfix) with ESMTP id 6B3059FB5DA for ; Thu, 28 Jun 2007 02:01:50 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id m22so180993nzf for ; Wed, 27 Jun 2007 22:01:49 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:subject:content-type:content-transfer-encoding; b=mustul8DktfcTiAw335F9iPtRvFBdeQ4VWv4pt5GArnRQR3tGE0Bs+s2nvSaBUi+cCCVp69hmZxkWPQamBFbMjDAN8jIj5RzcAJXgf4anFpNX2oD5yd3JA5rV+JN4zFpUf+qf6eTLZiglJ5YqvU9IWbwzeltqjW4ls60mLOkTAc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:subject:content-type:content-transfer-encoding; b=Z0gLPLvSogkX5U0iBRjRVU+hcIvo3fzCkKtWOaW9p45v5AZPsKheYIMYiYpP8qu4UY9KXOZXsDRYhwco4DWSNN9rST234rUCNs+XvCC5MFq2yrGJz8YsHFSlfG7DvKlQhDJhtB6KJiX1Q6RDi1yV3ypzAYBwdW8g/xKvnggtBWw= Received: by 10.114.198.1 with SMTP id v1mr1193392waf.1183006908770; Wed, 27 Jun 2007 22:01:48 -0700 (PDT) Received: from ?10.1.1.15? ( [203.217.18.65]) by mx.google.com with ESMTP id n22sm18259907pof.2007.06.27.22.01.47 (version=TLSv1/SSLv3 cipher=RC4-MD5); Wed, 27 Jun 2007 22:01:48 -0700 (PDT) Message-ID: <46834124.5010600@gmail.com> Date: Thu, 28 Jun 2007 15:03:32 +1000 From: Chris User-Agent: Thunderbird 2.0.0.4 (Windows/20070604) MIME-Version: 1.0 To: Pgsql performance Subject: update query taking too long Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/609 X-Sequence-Number: 25642 Hi all, I'm trying to do an update of a reasonably large table and it's taking way too long so I'm trying to work out why and if I need to tweak any settings to speed it up. The table is around 3.5 million records. The query is update table set domainname=substring(emailaddress from position('@' in emailaddress)); I've left it running for over 20 minutes and it hasn't finished so I'm doing something terribly bad but I have no idea what ;) Maybe there's another way to write the query but I'm not sure how to make it better. Most settings are default, I have bumped up shared_buffers a bit to 65536 - I think that works out to 512Meg ? The machine has 2G ram. Running version 8.1.9. Any pointers about settings etc are most welcome. -- Postgresql & php tutorials http://www.designmagick.com/