Skip site navigation (1) Skip section navigation (2)

Re: performance penalty between Postgresql 8.3.8 and 8.4.1

From: "Schmitz, David" <david(dot)schmitz(at)harman(dot)com>
To: "Robert Haas" <robertmhaas(at)gmail(dot)com>,"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: performance penalty between Postgresql 8.3.8 and 8.4.1
Date: 2009-12-08 09:41:51
Message-ID: 02FE2F38DEB0714EACA6ADD491B2C01802FA16D2@OEKAW2EXVS04.hbi.ad.harman.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Andres,

This query returns for 8.4.1 and for 8.3.8 the same result:

stadistinct = -1
stanullfrac = 0
stawidth = 4
array_upper nothing 

Regards 

David

>-----Urspr√ľngliche Nachricht-----
>Von: Robert Haas [mailto:robertmhaas(at)gmail(dot)com] 
>Gesendet: Dienstag, 8. Dezember 2009 05:05
>An: Kevin Grittner
>Cc: Schmitz, David; pgsql-performance(at)postgresql(dot)org
>Betreff: Re: [PERFORM] performance penalty between Postgresql 
>8.3.8 and 8.4.1
>
>On Mon, Dec 7, 2009 at 5:19 PM, Kevin Grittner 
><Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> "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?
>
>I think Tom made some changes to the join selectivity code 
>which might be relevant here, though I'm not sure exactly 
>what's going on.  Can we see, on the 8.4.1 database:
>
>SELECT SUM(1) FROM rdf_admin_hierarchy;
>SELECT s.stadistinct, s.stanullfrac, s.stawidth, 
>array_upper(s.stanumbers1, 1) FROM pg_statistic s WHERE 
>s.starelid = 'rdf_admin_hierarchy'::regclass AND s.staattnum = 
>(SELECT a.attnum FROM pg_attribute a WHERE a.attname = 
>'admin_place_id' AND a.attrelid = 'rdf_admin_hierarchy'::regclass);
>
>...Robert
> 
 
*******************************************
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.
*******************************************

In response to

pgsql-performance by date

Next:From: Schmitz, DavidDate: 2009-12-08 09:59:51
Subject: Re: performance penalty between Postgresql 8.3.8 and 8.4.1
Previous:From: Dimitri FontaineDate: 2009-12-08 09:37:15
Subject: Re: Load experimentation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group