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