On 28 November 2011 12:55, Wim Bertels <wim.bert...@khleuven.be> wrote:
> Hallo, > > if u compare the 2 queries, then they should be equivalent: > > -- normal > -- EXPLAIN ANALYZE > SELECT amproc, amprocnum - average AS difference > FROM pg_amproc, > (SELECT avg(amprocnum) AS average > FROM pg_amproc) AS tmp; > > -- trying to trick explain with a redundant join > -- EXPLAIN ANALYZE > SELECT amproc, amprocnum - average AS difference > FROM pg_amproc INNER JOIN > (SELECT avg(amprocnum) AS average > FROM pg_amproc) AS tmp > ON pg_amproc.amproc = pg_amproc.amproc; > > > If we look at the output of EXPLAIN ANALYZE, > then according to the COST the second query is best one, > but according to the ACTUAL TIME the first query is best > (which seems logical intuitively). > > So explain is being tricked, > and the reason for this seems the number of rows in de nested loop, > which are reduced to 1 for explain because of the join. > http://www.postgresql.org/docs/8.4/static/using-explain.html > > Suggestions, comments are always welcome. > > mvg, > Wim Bertels > > > > Hi, could you show us the output of explain analyze? regards Szymon
