X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B1D04D8AD1 for ; Mon, 12 Sep 2005 07:55:20 -0300 (ADT) 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 76040-05 for ; Mon, 12 Sep 2005 10:55:16 +0000 (GMT) Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by svr1.postgresql.org (Postfix) with ESMTP id 9C717D8B96 for ; Mon, 12 Sep 2005 07:55:17 -0300 (ADT) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id E44AD401D7A; Mon, 12 Sep 2005 11:55:12 +0100 (BST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 52E9215F1D; Mon, 12 Sep 2005 11:51:21 +0100 (BST) Received: from mainbox.archonet.com ([127.0.0.1]) by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 22166-03; Mon, 12 Sep 2005 11:51:18 +0100 (BST) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 3450215F1C; Mon, 12 Sep 2005 11:51:18 +0100 (BST) Message-ID: <43255DA5.9030602@archonet.com> Date: Mon, 12 Sep 2005 11:51:17 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Hilary Forbes Cc: pgsql-performance@postgresql.org Subject: Re: Slow update References: <5.1.0.14.0.20050912100150.01f47f18@mailserver.dmr.co.uk> In-Reply-To: <5.1.0.14.0.20050912100150.01f47f18@mailserver.dmr.co.uk> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.058 required=5 tests=[AWL=0.008, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200509/138 X-Sequence-Number: 14446 Hilary Forbes wrote: > > I need to set the value of a field in table A to a value in table B depending on the existence of the record in table B. So what I have done is > > UPDATE tableA set a.val1=b.somefield FROM tableA a, tableB b WHERE a.key1=b.key1; Check the EXPLAIN carefully, are you sure the tableA in "UPDATE" is the same as that in your "FROM" clause. If so, why are you SETting a.val1? If not, you've probably got an unconstrained join. -- Richard Huxton Archonet Ltd