Обсуждение: Question, how intelligent is optimizer with subplans?
In general if I have a query like SELECT a, FROM (SELECT foo, (select count(*) from anothertable where a=t.a) AS n FROM x) AS t WHERE foo = 0 Is the optimizer clever enough not to run the "select count(*)" for every record in x but only the ones where foo=0? I can play with explain, but I won't know if I'm asking it to do the impossible or not. In case it's not obvious, the actual query is much more complex and involves a DISTINCT ON() and a sort and the "select count(*)" is part of the sort so I don't want to push it out manually and duplicate the expression. -- greg
On second thought what I wanted to do should work, I think. I'm not clear why the first of these works fine but the second doesn't. What I want to do is effectively the second of these: slo=> select (select count(*) from t2) as x from t order by x; x --- 0 (1 row) slo=> select (select count(*) from t2) as x from t order by sign(x); ERROR: Attribute "x" not found -- greg
Greg Stark <gsstark@MIT.EDU> writes: > On second thought what I wanted to do should work, I think. I'm not clear why > the first of these works fine but the second doesn't. What I want to do is > effectively the second of these: > slo=> select (select count(*) from t2) as x from t order by x; > x > --- > 0 > (1 row) > slo=> select (select count(*) from t2) as x from t order by sign(x); > ERROR: Attribute "x" not found The first of those works because the SQL spec says it should: unadorned names appearing in ORDER BY are output column names per the spec. The second of those is not legal per SQL spec (you can't put anything except an unadorned output column name or number in ORDER BY, according to the spec). Postgres accepts expressions in ORDER BY, but we consider them to be expressions in the input column names. You could do something like SELECT x FROM (select count(*) as x from t2) as ss ORDER BY sign(x); regards, tom lane