Re: tricking EXPLAIN?

On 28 November 2011 12:55, Wim Bertels <wim(dot)bertels(at)khleuven(dot)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