From: Tom Lane <tgl@sss.pgh.pa.us>
To: Denis <denis.sailer@yellowbook.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Update using primary key slow
Date: Thu, 27 Oct 2005 15:41:03 -0400
Message-ID: <23322.1130442063@sss.pgh.pa.us> (raw)
In-Reply-To: <Xns96FC85EFAC158denissaileryellowboo@200.46.204.72>
References: <Xns96FC6CBBEC0CDdenissaileryellowboo@200.46.204.72>
<19722.1130429883@sss.pgh.pa.us>
<Xns96FC85EFAC158denissaileryellowboo@200.46.204.72>
Denis <denis.sailer@yellowbook.com> writes:
> tgl@sss.pgh.pa.us (Tom Lane) wrote in
> news:19722.1130429883@sss.pgh.pa.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, denis.sailer@yellowbook.com
Subject: Re: Update using primary key slow
In-Reply-To: <23322.1130442063@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox