Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 5FC92B5DF86 for ; Wed, 8 Jun 2011 12:31:35 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 19952-04 for ; Wed, 8 Jun 2011 15:31:28 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from elizabeth.gransy.com (elizabeth.gransy.com [89.187.132.199]) by mail.postgresql.org (Postfix) with ESMTP id 362E0B5DF83 for ; Wed, 8 Jun 2011 12:31:27 -0300 (ADT) Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id 3F09115E40BD; Wed, 8 Jun 2011 17:31:26 +0200 (CEST) Received: from 85.160.195.232 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Wed, 8 Jun 2011 17:31:26 +0200 Message-ID: In-Reply-To: <1307545892.1990.23.camel@tony1.localdomain> References: <1307545892.1990.23.camel@tony1.localdomain> Date: Wed, 8 Jun 2011 17:31:26 +0200 Subject: Re: Oracle v. Postgres 9.0 query performance From: tv@fuzzy.cz To: "Tony Capobianco" Cc: pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.21 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201106/68 X-Sequence-Number: 44001 > 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