Re: Performance of UPDATE SET = FROM vs UPDATE SET = (SELECT ...)

These updates aren't equivalent. It's very important you know this, because you're also inflating your table with a lot of extra updated rows.

Take the first UPDATE:

> UPDATE second SET time1 = orig.time1
> FROM orig
> WHERE second.key1 = orig.key1;

If you wrote this as a SELECT, it would look like this:

SELECT second.time1, orig.time1
FROM second
JOIN ORIG ON (second.key1 = orig.key1)

Since second is a many to one subset of orig, you now have several simultaneous updates. Your second UPDATE:

> UPDATE second SET time1 = (SELECT orig.time1 FROM orig,second
> WHERE orig.key1 = second.key1 LIMIT 1);

Is equivalent to this SELECT:

SELECT second.time1,
(SELECT orig.time1 FROM orig,second
WHERE orig.key1 = second.key1 LIMIT 1)
FROM second;

Meaning you'd only get as many updates as there are rows in second. The difference is your LIMIT 1. However, since you're not using an ORDER BY clause, the actual value you get for time1 will be indeterminate. Something like this would remove the row inflation and fix the random time1 behavior, but I'm not sure it was your intent:

UPDATE second
SET time1 = orig.time1
FROM (SELECT DISTINCT ON (key1) key1, time1
FROM orig
ORDER BY key1, time1 DESC) sub
WHERE second.key1 = sub.key1;

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email