Re: Oracle v. Postgres 9.0 query performance

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(at)prospectiv(dot)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