Re: tricking EXPLAIN?

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