On Tue, Dec 8, 2009 at 8:27 AM, Schmitz, David <david.schm...@harman.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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
