From: Jean-Luc Lachance <jllachan@nsd.ca>
To: pgsql-sql@postgresql.org
Subject: Performance on update from join
Date: Wed, 08 May 2002 12:15:56 -0400
Message-ID: <3CD94F3C.4B116582@nsd.ca> (raw)
References: <20020507142203.4114.qmail@uwdvg001.cms.usa.net>
<200205081145.37254.mallah@trade-india.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jllachan@nsd.ca
Subject: Re: Performance on update from join
In-Reply-To: <3CD94F3C.4B116582@nsd.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox