-----Ursprüngliche Nachricht----- Von: Schmitz, David Gesendet: Di 08.12.2009 00:14 An: Kevin Grittner Cc: Betreff: AW: performance penalty between Postgresql 8.3.8 and 8.4.1 -----Ursprüngliche Nachricht----- Von: Kevin Grittner [mailto:Kevin.Grittner@xxxxxxxxxxxx] Gesendet: Mo 07.12.2009 23:19 An: Schmitz, David; pgsql-performance@xxxxxxxxxxxxxx Cc: Betreff: Re: performance penalty between Postgresql 8.3.8 and 8.4.1 "Schmitz, David" <david.schmitz@xxxxxxxxxx> wrote: > It is carried out with poor performance on postgresql 8.4.1 > However postgresql 8.3.8 performs just fine. > If you take a closer look at the query with EXPLAIN, it becomes > obvious, that postgresql 8.4 does not consider the primary key at > level 3 and instead generates a hash join: > Postgresql 8.4.1: > > Sort (cost=129346.71..129498.64 rows=60772 width=61) > Postgresql 8.3.8: > > Sort (cost=3792.75..3792.95 rows=81 width=61) It determines the plan based on available statistics, which in this case seem to indicate rather different data. Do the two databases have identical data? Have they both been recently analyzed? What is the default_statistics_target on each? Do any columns in these tables have overrides? -Kevin Hello Kevin, both databases have identical / same data and hardware. On postgresql 8.3.8 default statistics target is 10 and at postgresql 8.4.1 it is 100. But i have been experimenting in both directions with postgres 8.4.1 10, 100, 1000 or 10000 does not matter perfomance remains bad. Analyze has been run recently on both databases (even an explicit analayze before query makes no difference). Autovaccuum and analyze are set quite aggressive at 0.01 (v) and 0.02 (a) and postgres 8.3.8 still outperforms 8.4.1. Regards dave ******************************************* 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
