Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id C32454762D2 for ; Wed, 8 May 2002 17:10:58 -0400 (EDT) Received: from beamish.nsd.ca (unknown [205.150.156.194]) by postgresql.org (Postfix) with ESMTP id 6FA2F4762A1 for ; Wed, 8 May 2002 16:35:19 -0400 (EDT) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id QAA04745 for ; Wed, 8 May 2002 16:35:20 -0400 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma004743; Wed, 8 May 02 16:35:11 -0400 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id QAA28400 for ; Wed, 8 May 2002 16:35:11 -0400 Message-ID: <3CD98D4E.3553836D@nsd.ca> Date: Wed, 08 May 2002 16:40:46 -0400 From: Jean-Luc Lachance X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-31 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Bad performance on update from a join References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200205/58 X-Sequence-Number: 7694 Hi all, Hi Tom, I was exploring ways to improve the time required to update a large table from the join of two others as in: UPDATE a FROM b, c; I found that whatever index I create, compound or not, PG insist on performing the cartesian product first. Now, if "b" and "c" are relatively small, that make sense, but when the cartesian product of "b" and "c" is and order of magnitude larger than "a" it makes no sense at all. Shouldn't the number of rows in "b" and "c" be reduced to the matching the criterias on "a" first? If "b" is meant to be a many to one relation to "c", shouldn't "a" be joined to "b" first? Is there a way I can force the "a" join "b" first? I also tried to do it in a PLPGSQL FOR LOOP, but because the script is processed as a transaction, I do not get much better performance. It would sure be nice to be able to commit the change on a row per row basis. THX jll