Re: Postgres8: subselect and optimizer/planner

Erwin Moller <erwin@darwine.nl> writes:
> SELECT U.userid, U.username,
> (SELECT G.groupname FROM tblgroup WHERE (G.userid=U.userid)) AS ingroup
> FROM tbluser WHERE (bla..bla...);

> Will this approach be slower than a regular join?

Probably; it's unlikely to be faster anyway.  The best plan you'll get
from this is equivalent to a nestloop with inner indexscan on
tblgroup.userid.  Now that might be the best plan anyway, or it might
not --- if you are selecting many rows from ingroup it's likely to suck.

> Or is my question too general and is the answer 'it depends'?

The only way I could see for this way to win would be if a nestloop is
actually the fastest plan, but the planner misestimates and decides to
use merge or hash join instead.  Which could happen :-(

            regards, tom lane