Re: 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