Hi Robert, unfortunatley its non of the things :-( see below: - EXPLAIN SELECT * FROM xdf.xdf_admin_hierarchy WHERE admin_place_id = 150738434 On Postgresql 8.4.1 and 8.3.8 Index Scan using pk_rdf_admin_hierarchy on rdf_admin_hierarchy (cost=0.00..8.28 rows=1 width=34) Index Cond: (admin_place_id = 150738434) - SELECT reltuples FROM pg_class WHERE oid = 'pk_xdf_admin_hierarchy'::regclass returns 84211 on postgresql 8.4.1 and 8.3.8 - work_mem is 512MB on both systems - unfortunately I can not hand out any data because of legal issues so we will have to do further debugging if necessary So how should we proceed with this issue? Regards David >-----Ursprüngliche Nachricht----- >Von: Robert Haas [mailto:robertmhaas@xxxxxxxxx] >Gesendet: Dienstag, 8. Dezember 2009 16:14 >An: Schmitz, David >Cc: Craig Ringer; Thom Brown; Andres Freund; >pgsql-performance@xxxxxxxxxxxxxx >Betreff: Re: performance penalty between Postgresql >8.3.8 and 8.4.1 > >On Tue, Dec 8, 2009 at 8:27 AM, Schmitz, David ><david.schmitz@xxxxxxxxxx> wrot >> that is exactly the problem postgresql 8.4.1 does not consider the >> primary key but instead calculates a hash join. This can >only result in poorer performance. I think this is a bug. > >Your statement that "this can only result in poorer >performance" is flat wrong. Just because there's a primary >key doesn't mean that an inner-indexscan plan is fastest. >Frequently a hash join is faster. I can think of a couple of >possible explanations for the behavior you're >seeing: > >- Something could be blocking PostgreSQL from using that index at all. > If you do EXPLAIN SELECT * FROM xdf_admin_hierarchy WHERE >admin_place_id = <some particular value>, does it use the >index or seq-scan the table? > >- The index on your 8.4.1 system might be bloated. You could >perhaps SELECT reltuples FROM pg_class WHERE oid = >'pk_xdf_admin_hierarchy'::regclass on both systems to see if >one index is larger than the other. > >- You might have changed the value of the work_mem parameter >on one system vs. the other. Try "show work_mem;" on each >system and see what you get. > >If it's none of those things, it's could be the result of a >code change, but I'm at a loss to think of which one would >apply in this case. I suppose we could do a bisection search >but that's a lot of work for you. If you could extract a >reproducible test case (complete with data) that would allow >someone else to try to track it down. > >...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
