pg_dw=# show random_page_cost ; random_page_cost ------------------ 4 (1 row)
Time: 0.299 ms pg_dw=# show seq_page_cost ; seq_page_cost --------------- 1 (1 row) Time: 0.250 ms pg_dw=# show work_mem ; work_mem ---------- 768MB (1 row) On Wed, 2011-06-08 at 18:27 +0200, Pavel Stehule wrote: > Hello > > what is your settings for > > random_page_cost, seq_page_cost and work_mem? > > Regards > > Pavel Stehule > > 2011/6/8 Tony Capobianco <tcapobia...@prospectiv.com>: > > Here's the explain analyze: > > > > pg_dw=# explain analyze CREATE TABLE ecr_opens with (FILLFACTOR=100) > > as > > select o.emailcampaignid, count(memberid) opencnt > > from openactivity o,ecr_sents s > > where s.emailcampaignid = o.emailcampaignid > > group by o.emailcampaignid; > > > > QUERY > > PLAN > > ---------------------------------------------------------------------------------------------------------------------------------------------------------------- > > GroupAggregate (cost=0.00..1788988.05 rows=9939 width=12) (actual > > time=308630.967..2592279.526 rows=472 loops=1) > > -> Nested Loop (cost=0.00..1742467.24 rows=9279316 width=12) > > (actual time=31.489..2589363.047 rows=8586466 loops=1) > > -> Index Scan using ecr_sents_ecid_idx on ecr_sents s > > (cost=0.00..38.59 rows=479 width=4) (actual time=0.010..13.326 rows=479 > > loops=1) > > -> Index Scan using openact_emcamp_idx on openactivity o > > (cost=0.00..3395.49 rows=19372 width=12) (actual time=1.336..5397.139 > > rows=17926 loops=479) > > Index Cond: (o.emailcampaignid = s.emailcampaignid) > > Total runtime: 2592284.336 ms > > > > > > On Wed, 2011-06-08 at 17:31 +0200, t...@fuzzy.cz wrote: > >> > On Postgres, this same query takes about 58 minutes (could not run > >> > explain analyze because it is in progress): > >> > > >> > pg_dw=# explain CREATE TABLE ecr_opens with (FILLFACTOR=100) > >> > pg_dw-# as > >> > pg_dw-# select o.emailcampaignid, count(memberid) opencnt > >> > pg_dw-# from openactivity o,ecr_sents s > >> > pg_dw-# where s.emailcampaignid = o.emailcampaignid > >> > pg_dw-# group by o.emailcampaignid; > >> > QUERY > >> > PLAN > >> > ------------------------------------------------------------------------------------------------------------- > >> > GroupAggregate (cost=0.00..1788988.05 rows=9939 width=12) > >> > -> Nested Loop (cost=0.00..1742467.24 rows=9279316 width=12) > >> > -> Index Scan using ecr_sents_ecid_idx on ecr_sents s > >> > (cost=0.00..38.59 rows=479 width=4) > >> > -> Index Scan using openact_emcamp_idx on openactivity o > >> > (cost=0.00..3395.49 rows=19372 width=12) > >> > Index Cond: (o.emailcampaignid = s.emailcampaignid) > >> > (5 rows) > >> > > >> > >> Please, post EXPLAIN ANALYZE, not just EXPLAIN. Preferably using > >> explain.depesz.com. > >> > >> regards > >> Tomas > >> > >> > > > > > > > > -- > > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-performance > > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
