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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Lennin Caro | 2009-12-08 15:51:13 | Re: Optimizing Bitmap Heap Scan. |
Previous Message | Kevin Grittner | 2009-12-08 15:05:04 | Re: Checkpoint spikes |