Re: performance penalty between Postgresql 8.3.8 and 8.4.1

-----Ursprüngliche Nachricht-----
Von: Schmitz, David
Gesendet: Di 08.12.2009 00:14
An: Kevin Grittner
Cc:
Betreff: AW: [PERFORM] performance penalty between Postgresql 8.3.8 and 8.4.1

-----Ursprüngliche Nachricht-----
Von: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
Gesendet: Mo 07.12.2009 23:19
An: Schmitz, David; pgsql-performance(at)postgresql(dot)org
Cc:
Betreff: Re: [PERFORM] performance penalty between Postgresql 8.3.8 and 8.4.1

"Schmitz, David" <david(dot)schmitz(at)harman(dot)com> 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.
*******************************************