Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 7A4F6B5DF91 for ; Wed, 8 Jun 2011 12:40:42 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 41768-08 for ; Wed, 8 Jun 2011 15:40:35 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from stan.prospectivdirect.com (mail.prospectiv.com [155.212.59.212]) by mail.postgresql.org (Postfix) with SMTP id 3D90DB5DF88 for ; Wed, 8 Jun 2011 12:40:35 -0300 (ADT) Received: (qmail 20105 invoked from network); 8 Jun 2011 15:40:34 -0000 Received: from unknown (HELO ?192.168.1.15?) (192.168.1.15) by 0 with SMTP; 8 Jun 2011 15:40:34 -0000 Subject: Re: Oracle v. Postgres 9.0 query performance From: Tony Capobianco To: Tom Lane Cc: pgsql-performance@postgresql.org In-Reply-To: <10260.1307547185@sss.pgh.pa.us> References: <1307545892.1990.23.camel@tony1.localdomain> <10260.1307547185@sss.pgh.pa.us> Content-Type: text/plain; charset="UTF-8" Date: Wed, 08 Jun 2011 11:40:34 -0400 Message-ID: <1307547634.1990.26.camel@tony1.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.32.2 (2.32.2-1.fc14) Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201106/70 X-Sequence-Number: 44003 pg_dw=# set enable_nestloop =0; SET Time: 0.165 ms 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 ----------------------------------------------------------------------------------------- HashAggregate (cost=4391163.81..4391288.05 rows=9939 width=12) -> Hash Join (cost=14.78..4344767.23 rows=9279316 width=12) Hash Cond: (o.emailcampaignid = s.emailcampaignid) -> Seq Scan on openactivity o (cost=0.00..3529930.67 rows=192540967 width=12) -> Hash (cost=8.79..8.79 rows=479 width=4) -> Seq Scan on ecr_sents s (cost=0.00..8.79 rows=479 width=4) Yikes. Two sequential scans. On Wed, 2011-06-08 at 11:33 -0400, Tom Lane wrote: > Tony Capobianco writes: > > 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) > > > Should this query be hashing the smaller table on Postgres rather than > > using nested loops? > > Yeah, seems like it. Just for testing purposes, do "set enable_nestloop > = 0" and see what plan you get then. > > regards, tom lane >