From: | "Li Jie" <jay23jack(at)gmail(dot)com> |
---|---|
To: | "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>, "Robert Haas" <robertmhaas(at)gmail(dot)com> |
Cc: | "pgsql-hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: small table left outer join big table |
Date: | 2010-12-29 14:45:00 |
Message-ID: | 005301cba766$fb00fd80$0801a8c0@A0078508 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
----- Original Message -----
From: "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>
To: "Robert Haas" <robertmhaas(at)gmail(dot)com>
Cc: "Jie Li" <jay23jack(at)gmail(dot)com>; "pgsql-hackers" <pgsql-hackers(at)postgresql(dot)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(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
The version is 9.0.1. I believe the latest version works in the same way.
Thanks,
Li Jie
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2010-12-29 14:58:17 | Re: Libpq PGRES_COPY_BOTH - version compatibility |
Previous Message | Magnus Hagander | 2010-12-29 14:40:34 | Re: Streaming replication as a separate permissions |