Re: Oracle v. Postgres 9.0 query performance

> * 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.

Just out of curiosity, is there any chance that this kind of query is
speeding up in 9.1 because of following changes?

* Allow FULL OUTER JOIN to be implemented as a hash join, and allow
either side of a LEFT OUTER JOIN or RIGHT OUTER JOIN to be hashed
(Tom Lane)
Previously FULL OUTER JOIN could only be implemented as a merge
join, and LEFT OUTER JOIN and RIGHT OUTER JOIN could hash only the
nullable side of the join. These changes provide additional query
optimization possibilities.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp