Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id BE879DE314D for ; Tue, 3 Apr 2012 14:55:20 -0300 (ADT) Received: from plane.gmane.org ([80.91.229.3]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SF7wr-0005GW-Jy for pgsql-performance@postgresql.org; Tue, 03 Apr 2012 17:55:19 +0000 Received: from list by plane.gmane.org with local (Exim 4.69) (envelope-from ) id 1SF7we-0005Fk-J4 for pgsql-performance@postgresql.org; Tue, 03 Apr 2012 19:55:04 +0200 Received: from host-188-174-163-9.customer.m-online.net ([188.174.163.9]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 03 Apr 2012 19:55:04 +0200 Received: from spam_eater by host-188-174-163-9.customer.m-online.net with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 03 Apr 2012 19:55:04 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-performance@postgresql.org From: Thomas Kellerer Subject: Re: Update join performance issues Date: Tue, 03 Apr 2012 19:51:21 +0200 Lines: 22 Message-ID: References: <4F7B3394.3030703@consistentstate.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@dough.gmane.org X-Gmane-NNTP-Posting-Host: host-188-174-163-9.customer.m-online.net User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666 In-Reply-To: <4F7B3394.3030703@consistentstate.com> X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201204/32 X-Sequence-Number: 46678 Kevin Kempter wrote on 03.04.2012 19:29: > Hi All; > > I have a query that wants to update a table based on a join like this: > > update test_one > set f_key = t.f_key > from > upd_temp1 t, > test_one t2 > where > t.id_number = t2.id_number > > upd_temp1 has 248,762 rows > test_one has 248,762 rows > To extend on what Kevin has already answere: Quote from the manual: "Note that the target table must not appear in the from_list, unless you intend a self-join"