Thread: 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