From: Tom Lane <tgl@sss.pgh.pa.us>
To: Martin Lesser <ml-pgsql@bettercom.de>
Cc: pgsql-performance@postgresql.org, Denis <denis.sailer@yellowbook.com>
Subject: Re: Reasons and drawbacks for unused item pointers (was: Update using primary key slow)
Date: Thu, 27 Oct 2005 17:36:36 -0400
Message-ID: <24260.1130448996@sss.pgh.pa.us> (raw)
In-Reply-To: <87y84eznb0.fsf_-_@nb-aspire.bettercom.de>
References: <Xns96FC6CBBEC0CDdenissaileryellowboo@200.46.204.72>
<19722.1130429883@sss.pgh.pa.us>
<Xns96FC85EFAC158denissaileryellowboo@200.46.204.72>
<23322.1130442063@sss.pgh.pa.us>
<87y84eznb0.fsf_-_@nb-aspire.bettercom.de>
Martin Lesser <ml-pgsql@bettercom.de> writes:
> What causes this "unused item pointers" and which impact do they have
> regarding performance?
Those are item pointer slots that were once used but aren't used at the
moment. VACUUM leaves an empty slot behind when it removes a dead
tuple, and the slot is then available for re-use next time a tuple is
created on that page. See
http://developer.postgresql.org/docs/postgres/storage-page-layout.html
The direct performance impact is really pretty minimal (and none at all
on indexscans, AFAIR). The reason Denis' number drew my attention was
that it implied that the table had gone un-vacuumed for awhile at some
time in the past. His stats were showing about 64000 tuples deleted
per vacuum pass, which would have created 64000 unused item pointers
--- but in a steady-state situation those would be eaten up again by
the time of the next vacuum. To have 1905028 unused pointers in a
table with only 5106307 live entries suggests that at some point there
were 1.9 million (or so) dead but not-yet-vacuumed tuples, which
suggests insufficient vacuuming.
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, ml-pgsql@bettercom.de, denis.sailer@yellowbook.com
Subject: Re: Reasons and drawbacks for unused item pointers (was: Update using primary key slow)
In-Reply-To: <24260.1130448996@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