From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Tony Capobianco <tcapobianco(at)prospectiv(dot)com> |
Cc: | tv(at)fuzzy(dot)cz, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Oracle v. Postgres 9.0 query performance |
Date: | 2011-06-08 16:27:08 |
Message-ID: | BANLkTi=jqRXH24OFfaCkAYMhaHY=B5vYWQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tony Capobianco | 2011-06-08 16:28:06 | Re: Oracle v. Postgres 9.0 query performance |
Previous Message | Tony Capobianco | 2011-06-08 16:22:08 | Re: Oracle v. Postgres 9.0 query performance |