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

Re: Update using primary key slow

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Denis <denis(dot)sailer(at)yellowbook(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Update using primary key slow
Date: 2005-10-27 19:41:03
Message-ID: 23322.1130442063@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Denis <denis(dot)sailer(at)yellowbook(dot)com> writes:
> tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote in
> news:19722(dot)1130429883(at)sss(dot)pgh(dot)pa(dot)us: 
>> The 183 msec is the time needed to *fetch* the row, not the time to
>> update it.  So it could well be that the other time is just the time
>> needed to update the table and indexes.  If this seems slower than
>> your hardware ought to be able to handle, I'd wonder about how
>> recently the table has been vacuumed.

> Here is the latest vacuum today.

> INFO:  "contract": removed 64146 row versions in 26115 pages
> DETAIL:  CPU 1.94s/2.55u sec elapsed 7.78 sec.
> INFO:  "contract": found 64146 removable, 5106307 nonremovable row 
> versions in 129154 pages
> DETAIL:  890 dead row versions cannot be removed yet.
> There were 1905028 unused item pointers.

The "unused item pointers" number seems a bit high, but otherwise that
looks pretty reasonable.

Is it possible that the particular row you were updating has been
updated quite a lot of times since the last vacuum?  Or even quite
a few times within a single transaction?  The only thing I can think
of that would explain such a slow fetch is if the code has to reject
a bunch of recently-dead versions of the row.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: PostgreSQLDate: 2005-10-27 20:41:10
Subject: Re: What gets cached?
Previous:From: PostgreSQLDate: 2005-10-27 19:31:43
Subject: How much memory?

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