Re: Place of subselect

"Guillaume Bog" <guibog(at)gmail(dot)com> writes:
> I have performance issues if I do the following pseudo-query:

> SELECT a, b, (SELECT count(*) FROM t2 WHERE something) AS c
> FROM t1 ORDER BY a LIMIT 10;

> After some tests, it seems to me that the subquery on t2 is computed for all
> rows of t1.

Yeah. The SQL specification says that ORDER BY happens after computing
the SELECT output-list. In some cases that'll get optimized but you
can't count on it.

You can probably improve matters by using a sub-select:

SELECT a, b, (SELECT count(*) FROM t2 WHERE something) AS c
FROM ( SELECT a, b, ... FROM t1 ORDER BY a LIMIT 10 ) ss;

regards, tom lane