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@postnewspapers.com.au]
>Gesendet: Dienstag, 8. Dezember 2009 13:12
>An: Thom Brown
>Cc: Schmitz, David; Andres Freund; pgsql-performance@postgresql.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
sindoder 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
receivedthis e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorized copying,
disclosureor distribution of the contents in this e-mail is strictly forbidden.
*******************************************