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
