Re: performance penalty between Postgresql 8.3.8 and 8.4.1

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux