Subject: Re: unused item pointers?
From: "Jeffrey W. Baker" <jwbaker@acm.org>
To: Alan Stange <stange@rentec.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <430A8F30.7050706@rentec.com>
References: <430A8F30.7050706@rentec.com>
Content-Type: text/plain
Date: Mon, 22 Aug 2005 22:15:08 -0700
Message-Id: <1124774108.6978.7.camel@noodles>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Mon, 2005-08-22 at 22:51 -0400, Alan Stange wrote:
> Hello all,
> 
> what are unused item pointers and how do I get rid of them?
> 
> We have a fairly large table which is vacuumed daily and reindexed every 
> weekend.

> as you can see we have 235M unused item pointers in the main table and a 
> few 10's of millions more in other associated tables. 
> 
> Please note that the advice "vacuum more often" is a non-starter as the 
> total time here is already about 3 hours and this is just one table.  
> This is a fairly active table to which about 20M rows are added and 
> removed daily.

That may be so, but the answer is still to VACUUM more often.  Try the
autovacuum.  If it takes 3 hours with 90% wasted records, it would only
take 20 minutes when running properly.

You might be able to change your application to avoid generating so many
dead rows.  For example, test before insert so you don't make a dead
tuple on duplicate insert.

To repair this table, you can try VACUUM FULL but this is likely to take
longer than you find reasonable.  I would recommend dump and reload.

-jwb