Postgres8: subselect and optimizer/planner
Hi,
I am fairly new to EXPLAIN, butl working on it. ;-)
I have a few slow running queries I am trying to optimize.
First thing I wonder: I sometimes (lazy) add a subselect to queries.
A stupid example to clearify what I mean:
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?
I mean, will this construct 'force' a repetitive query for each result,
or will Postgres8 see my clumpsy construct, and make a join of it
internally?
Or is my question too general and is the answer 'it depends'?
I found a lot of queries I wrote like that in earlier projects, and I
wonder if I should fix them.
Thanks for any insights!
Regards,
Erwin Moller
--
-------------------
Erwin Moller
Darwine BV
Groenendaal 25f
3011 SK Rotterdam
tel 010-2133996
-------------------
Erwin Moller wrote:
SELECT U.userid, U.username,
(SELECT G.groupname FROM tblgroup WHERE (G.userid=U.userid)) AS ingroup
typo, that should be 'tblgroup as G' of course.
FROM tbluser WHERE (bla..bla...);
-------------------
Erwin Moller
Darwine BV
Groenendaal 25f
3011 SK Rotterdam
tel 010-2133996
-------------------
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