-----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.gritt...@wicourts.gov]
Gesendet:       Mo 07.12.2009 23:19
An:     Schmitz, David; pgsql-performance@postgresql.org
Cc:     
Betreff:        Re: [PERFORM] performance penalty between Postgresql 8.3.8 and 
8.4.1

"Schmitz, David" <david.schm...@harman.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.
*******************************************

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Reply via email to