From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | <wsheldah(at)lexmark(dot)com> |
Cc: | Martijn van Oosterhout <kleptog(at)svana(dot)org>, Chris Albertson <chrisalbertson90278(at)yahoo(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Need SQL help, I'm stuck. |
Date: | 2001-12-11 18:55:36 |
Message-ID: | 20011211105303.B79645-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, 11 Dec 2001 wsheldah(at)lexmark(dot)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 :)
From | Date | Subject | |
---|---|---|---|
Next Message | guard | 2001-12-11 19:05:02 | v7.1.3 dump data can't import to v7.2b |
Previous Message | Wieger Uffink | 2001-12-11 18:45:14 | Re: primary key generation |