Skip site navigation (1) Skip section navigation (2)

Re: performance penalty between Postgresql 8.3.8 and 8.4.1

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: "Schmitz, David" <david(dot)schmitz(at)harman(dot)com>
Cc: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>, Thom Brown <thombrown(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, pgsql-performance(at)postgresql(dot)org
Subject: Re: performance penalty between Postgresql 8.3.8 and 8.4.1
Date: 2009-12-08 15:13:51
Message-ID: 603c8f070912080713g62cda9aapc32df822807b8e94@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Lennin CaroDate: 2009-12-08 15:51:13
Subject: Re: Optimizing Bitmap Heap Scan.
Previous:From: Kevin GrittnerDate: 2009-12-08 15:05:04
Subject: Re: Checkpoint spikes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group