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@xxxxxxxxx] >Gesendet: Dienstag, 8. Dezember 2009 05:05 >An: Kevin Grittner >Cc: Schmitz, David; pgsql-performance@xxxxxxxxxxxxxx >Betreff: Re: performance penalty between Postgresql >8.3.8 and 8.4.1 > >On Mon, Dec 7, 2009 at 5:19 PM, Kevin Grittner ><Kevin.Grittner@xxxxxxxxxxxx> wrote: >> "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? > >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. ******************************************* -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
