Re: performance penalty between Postgresql 8.3.8 and 8.4.1

Hi Craig,

that is exactly the problem postgresql 8.4.1 does not consider the primary key but instead calculates
a hash join. This can only result in poorer performance. I think this is a bug.

Regards

David

>-----Ursprüngliche Nachricht-----
>Von: Craig Ringer [mailto:craig(at)postnewspapers(dot)com(dot)au]
>Gesendet: Dienstag, 8. Dezember 2009 13:12
>An: Thom Brown
>Cc: Schmitz, David; Andres Freund; pgsql-performance(at)postgresql(dot)org
>Betreff: Re: [PERFORM] performance penalty between Postgresql
>8.3.8 and 8.4.1
>
>On 8/12/2009 6:11 PM, Thom Brown wrote:
>
>> Your output shows that the xdf_admin_hierarchy tables
>between versions
>> are drastically different. 8.3.8 only contains 1 row, whereas 8.4.1
>> contains 84211 rows.
>
>That's just because one of them is doing a nested loop where
>it looks up a single row from xdf_admin_hierarchy via its
>primary key on each iteration. The other plan is doing a hash
>join on a sequential scan over xdf_admin_hierarchy so it
>reports all the rows at once.
>
>--
>Craig Ringer
>

*******************************************
innovative systems GmbH Navigation-Multimedia
Geschaeftsfuehrung: Edwin Summers - Michael Juergen Mauser
Sitz der Gesellschaft: Hamburg - Registergericht: Hamburg HRB 59980

*******************************************
Diese E-Mail enthaelt vertrauliche und/oder rechtlich geschuetzte Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtuemlich erhalten haben, informieren Sie bitte sofort den Absender und loeschen Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorized copying, disclosure or distribution of the contents in this e-mail is strictly forbidden.
*******************************************