Re: Oracle v. Postgres 9.0 query performance

2011/6/8 Tony Capobianco <tcapobianco(at)prospectiv(dot)com>:
> 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)
>
>

it is ok.

Pavel

>
>
> 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 <tcapobianco(at)prospectiv(dot)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, tv(at)fuzzy(dot)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(at)postgresql(dot)org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-performance
>> >
>>
>
>
>