Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 948CAB5F3E1 for ; Wed, 8 Jun 2011 13:28:27 -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 88947-01-5 for ; Wed, 8 Jun 2011 16:28:20 +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 D2AD3B5E0DD for ; Wed, 8 Jun 2011 13:28:08 -0300 (ADT) Received: (qmail 3470 invoked from network); 8 Jun 2011 16:28:07 -0000 Received: from unknown (HELO ?192.168.1.15?) (192.168.1.15) by 0 with SMTP; 8 Jun 2011 16:28:07 -0000 Subject: Re: Oracle v. Postgres 9.0 query performance From: Tony Capobianco To: Stephen Frost Cc: Tom Lane , pgsql-performance@postgresql.org In-Reply-To: <20110608155159.GA18128@tamriel.snowman.net> References: <1307545892.1990.23.camel@tony1.localdomain> <10260.1307547185@sss.pgh.pa.us> <1307547634.1990.26.camel@tony1.localdomain> <20110608155159.GA18128@tamriel.snowman.net> Content-Type: text/plain; charset="UTF-8" Date: Wed, 08 Jun 2011 12:28:06 -0400 Message-ID: <1307550486.1990.29.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/75 X-Sequence-Number: 44008 Well, this ran much better. However, I'm not sure if it's because of set enable_nestloop = 0, or because I'm executing the query twice in a row, where previous results may be cached. I will try this setting in my code for when this process runs later today and see what the result is. Thanks! pg_dw=# explain analyze 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) (actual time=167254.751..167254.937 rows=472 loops=1) -> Hash Join (cost=14.78..4344767.23 rows=9279316 width=12) (actual time=0.300..164577.131 rows=8586466 loops=1) Hash Cond: (o.emailcampaignid = s.emailcampaignid) -> Seq Scan on openactivity o (cost=0.00..3529930.67 rows=192540967 width=12) (actual time=0.011..124351.878 rows=192542480 loops=1) -> Hash (cost=8.79..8.79 rows=479 width=4) (actual time=0.253..0.253 rows=479 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 17kB -> Seq Scan on ecr_sents s (cost=0.00..8.79 rows=479 width=4) (actual time=0.010..0.121 rows=479 loops=1) Total runtime: 167279.950 ms On Wed, 2011-06-08 at 11:51 -0400, Stephen Frost wrote: > * Tony Capobianco (tcapobianco@prospectiv.com) wrote: > > 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. > > Err, isn't that more-or-less exactly what you want here? The smaller > table is going to be hashed and then you'll traverse the bigger table > and bounce each row off the hash table. Have you tried actually running > this and seeing how long it takes? The bigger table doesn't look to be > *that* big, if your i/o subsystem is decent and you've got a lot of > memory available for kernel cacheing, should be quick. > > Thanks, > > Stephen