Search Postgresql Archives

Re: tricking EXPLAIN?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On ma, 2011-11-28 at 13:00 +0100, Szymon Guz wrote:
> 
> 
> On 28 November 2011 12:55, Wim Bertels <wim.bertels@xxxxxxxxxxx>
> 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;

"Nested Loop  (cost=5.04..13.13 rows=243 width=38) (actual
time=0.333..0.953 rows=243 loops=1)"
"  ->  Aggregate  (cost=5.04..5.05 rows=1 width=2) (actual
time=0.326..0.327 rows=1 loops=1)"
"        ->  Seq Scan on pg_amproc  (cost=0.00..4.43 rows=243 width=2)
(actual time=0.003..0.157 rows=243 loops=1)"
"  ->  Seq Scan on pg_amproc  (cost=0.00..4.43 rows=243 width=6) (actual
time=0.002..0.147 rows=243 loops=1)"
"Total runtime: 1.117 ms"


>         
>         -- 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;

"Nested Loop  (cost=5.04..10.11 rows=1 width=38) (actual
time=0.376..80.891 rows=243 loops=1)"
"  ->  Seq Scan on pg_amproc  (cost=0.00..5.04 rows=1 width=6) (actual
time=0.028..0.249 rows=243 loops=1)"
"        Filter: ((amproc)::oid = (amproc)::oid)"
"  ->  Aggregate  (cost=5.04..5.05 rows=1 width=2) (actual
time=0.327..0.328 rows=1 loops=243)"
"        ->  Seq Scan on pg_amproc  (cost=0.00..4.43 rows=243 width=2)
(actual time=0.002..0.156 rows=243 loops=243)"
"Total runtime: 81.101 ms"


>         
>         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? 


cf supra,
Wim



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux