Re: small table left outer join big table
От | Li Jie |
---|---|
Тема | Re: small table left outer join big table |
Дата | |
Msg-id | 005301cba766$fb00fd80$0801a8c0@A0078508 обсуждение исходный текст |
Ответ на | small table left outer join big table (Jie Li <jay23jack@gmail.com>) |
Список | pgsql-hackers |
----- Original Message ----- From: "Alvaro Herrera" <alvherre@commandprompt.com> To: "Robert Haas" <robertmhaas@gmail.com> Cc: "Jie Li" <jay23jack@gmail.com>; "pgsql-hackers" <pgsql-hackers@postgresql.org> Sent: Wednesday, December 29, 2010 8:39 PM Subject: Re: [HACKERS] 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@gmail.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@commandprompt.com> > The PostgreSQL Company - Command Prompt, Inc. > PostgreSQL Replication, Consulting, Custom Development, 24x7 support The version is 9.0.1. I believe the latest version works in the same way. Thanks, Li Jie
В списке pgsql-hackers по дате отправления: