Re: DELETE syntax on JOINS

On Tue, Aug 25, 2009 at 2:50 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> On the performance front: yeah, you can recast most joins as subqueries,
> but you tend to end up with the equivalent of a nestloop plan.  Works
> okay for small numbers of rows, scales horribly.

Well that's our problem isn't it? I thought we were capable of
genearting semijoins for subqueries these days though?

The problem I thought was if you wanted to pull multiple values out of
the subquery.

So something like

UPDATE foo set a=aa, b=bb FROM bar WHERE ...

If you wanted to do an outer join from foo to bar then how would you
write it as an subquery even if our optimizer could notice the
semijoin and optimize it properly?

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).

--
greg
http://mit.edu/~gsstark/resume.pdf