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