Greg Stark <gsstark@mit.edu> writes:
> You would have to write something like
> UPDATE foo set a = (select aa from bar where...)
> b = (select bb from bar where...)
> and then the optimizer would have to notice the duplicates and
> consolidate them? That seems inconvenient (and fragile).
Well, that's why the spec nowadays allows you to write
UPDATE foo SET (a,b) = (select aa,bb from bar where ...)
But we haven't got that, and if we did it would generate a nestloop
plan. Getting to the point of absolute performance equivalence between
subqueries and joins would take a *lot* of work; I'm not even sure it's
possible at all. And once we'd done all that work there would still
remain the fact that people are accustomed to using join syntax instead.
There's a lot of existing code out there that would be a lot easier
to port to PG if we supported that style (which was exactly the point
made by the OP).
regards, tom lane
