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).