On Tue, 11 Dec 2001 wsheldah@lexmark.com wrote:
>
>
> In just eyeballing the various responses, it looks like the one using DISTINCT
> ON manages to avoid using a subquery at all. Would this give it the edge in
> performance? I had somehow never noticed the DISTINCT ON syntax before, this
> looks very handy.
Distinct on often performs better than subquery options, however it's a
PostgreSQL addition, so you need to be careful if you want to run the
query on anything else :)
> Also, my first attempt was to put the subquery in the WHERE clause, but I
> noticed that several put the subquery in the FROM clause. Does putting it in the
> FROM clause just run it once, with the results of the run joined to the outer
> tables? It certainly seemed like putting the query in the WHERE clause was
> running it for every row. Thanks,
I've generally assumed that subselects in from are effectively a cursor
that gets scanned as opposed to running the entire query for each row.
I haven't looked to confirm that, but it seems reasonable :)