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:cr...@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 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. ******************************************* -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
