Bad performance on update from a join - Mailing list pgsql-sql

From Jean-Luc Lachance
Subject Bad performance on update from a join
Date
Msg-id 3CD98D4E.3553836D@nsd.ca
Whole thread Raw
In response to CURSOR/FETCH vs LIMIT/OFFSET  (Charles Hauser <chauser@acpub.duke.edu>)
List pgsql-sql
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


pgsql-sql by date:

Previous
From: Philip Hallstrom
Date:
Subject: Re: CURSOR/FETCH vs LIMIT/OFFSET
Next
From: Mike Diehl
Date:
Subject: having trouble w/ having clause...