Mark Thornton <mthornton(at)optrak(dot)com> wrote:
> On 23/06/11 16:05, Harry Mantheakis wrote:
>> UPDATE
>> table_A
>> [ ... ]
>> FROM
>> table_B
>> WHERE
>> table_B.id = table_A.id
> I frequently get updates involving a FROM clause wrong --- the
> resulting table is correct but the running time is quadratic.
The most frequent way I've seen that happen is for someone to do:
UPDATE table_A
[ ... ]
FROM table_A a, table_B b
WHERE b.id = a.id
Because a FROM clause on an UPDATE statement is not in the standard,
different products have implemented this differently. In Sybase ASE
or Microsoft SQL Server you need to do the above to alias table_A,
and the two references to table_A are treated as one. In PostgreSQL
this would be two separate references and you would effectively be
doing the full update of all rows in table_A once for every row in
table_A. I don't think that is happening here based on the plan
posted earlier in the thread.
-Kevin