Re: small table left outer join big table

Excerpts from Robert Haas's message of mié dic 29 09:17:17 -0300 2010:
> On Tue, Dec 28, 2010 at 5:13 AM, Jie Li <jay23jack(at)gmail(dot)com> wrote:
> > Hi,
> >
> > Please see the following plan:
> >
> > postgres=# explain select * from small_table left outer join big_table using
> > (id);
> >                                  QUERY PLAN
> > ----------------------------------------------------------------------------
> >  Hash Left Join  (cost=126408.00..142436.98 rows=371 width=12)
> >    Hash Cond: (small_table.id = big_table.id)
> >    ->  Seq Scan on small_table  (cost=0.00..1.09 rows=9 width=8)
> >    ->  Hash  (cost=59142.00..59142.00 rows=4100000 width=8)
> >          ->  Seq Scan on big_table  (cost=0.00..59142.00 rows=4100000
> > width=8)
> > (5 rows)
> >
> > Here I have a puzzle, why not choose the small table to build hash table? It
> > can avoid multiple batches thus save significant I/O cost, isn't it?
>
> Yeah, you'd think. Can you post a full reproducible test case?

Also, what version is this?

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support