Message-ID: <3CD94F3C.4B116582@nsd.ca>
Date: Wed, 08 May 2002 12:15:56 -0400
From: Jean-Luc Lachance <jllachan@nsd.ca>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Performance on update from join
References: <20020507142203.4114.qmail@uwdvg001.cms.usa.net>
	<200205081145.37254.mallah@trade-india.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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