From: | "Campbell, Lance" <lance(at)uiuc(dot)edu> |
---|---|
To: | "Gregory Stark" <stark(at)enterprisedb(dot)com>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Suggestions on an update query |
Date: | 2007-10-29 16:33:57 |
Message-ID: | B10E6810AC2A2F4EA7550D072CDE8760197DC9@SAB-FENWICK.sab.uiuc.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Thanks for all of your help. The problem was that the result_entry table
had some constraints that pointed to a third table. When I removed
those constraints the performance was amazing. The update took less
than seven minutes to execute. I did not even consider the fact that
constraints to another table would impact the performance.
Thanks again,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Gregory
Stark
Sent: Friday, October 26, 2007 9:05 PM
To: Joshua D. Drake
Cc: Campbell, Lance; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Suggestions on an update query
"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> On Fri, 26 Oct 2007 15:31:44 -0500
> "Campbell, Lance" <lance(at)uiuc(dot)edu> wrote:
>
>> I forgot to include an additional parameter I am using in
>> Postgresql.conf:
>>
>
> O.k. first, just to get it out of the way (and then I will try and
> help). Please do not top post, it makes replying contextually very
> difficult.
>
>> PostgreSql version 8.2.4
>>
>> Memory = 8 Gig
>>
>> CPUs 1 dual core Zeon running at 3.0
>>
>
> O.k. first you might be grinding through your 20 checkpoint segments
> but in reality what I think is happening is you are doing foreign key
> checks against all of it and slowing things down.
If you're going to berate someone about top-posting perhaps you should
attach
your own commentary to relevant bits of context :P
But the original post didn't include any foreign key constraints. I
suspect
you've guessed it right though. In fact I suspect what's happening is he
doesn't have an index on the referencing column so the foreign key
checks are
doing sequential scans of.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
From | Date | Subject | |
---|---|---|---|
Next Message | Luke Lonergan | 2007-10-30 05:06:02 | Re: partitioned table and ORDER BY indexed_field DESC LIMIT 1 |
Previous Message | Heikki Linnakangas | 2007-10-29 15:22:07 | Re: Append Cost in query planners |