Re: Oracle v. Postgres 9.0 query performance - Mailing list pgsql-performance

Hello

what is your settings for

random_page_cost, seq_page_cost and work_mem?

Regards

Pavel Stehule

2011/6/8 Tony Capobianco <tcapobianco@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, tv@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
>