Re: performance penalty between Postgresql 8.3.8 and 8.4.1

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(at)gmail(dot)com]
>Gesendet: Dienstag, 8. Dezember 2009 16:14
>An: Schmitz, David
>Cc: Craig Ringer; Thom Brown; Andres Freund;
>pgsql-performance(at)postgresql(dot)org
>Betreff: Re: [PERFORM] performance penalty between Postgresql
>8.3.8 and 8.4.1
>
>On Tue, Dec 8, 2009 at 8:27 AM, Schmitz, David
><david(dot)schmitz(at)harman(dot)com> 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.
*******************************************