Thread: unused item pointers?

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.

NFO: vacuuming "public.tbltimeseries"
INFO: index "idx_timeseries" now contains 26165807 row versions in
151713 pages
DETAIL: 8610108 index row versions were removed.
58576 index pages have been deleted, 36223 are currently reusable.
CPU 6.36s/18.46u sec elapsed 263.75 sec.
INFO: "tbltimeseries": removed 8610108 row versions in 500766 pages
DETAIL: CPU 37.07s/29.76u sec elapsed 826.82 sec.
INFO: "tbltimeseries": found 8610108 removable, 26165807 nonremovable
row versions in 5744789 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 235555635 unused item pointers.
0 pages are entirely empty.
CPU 119.13s/61.09u sec elapsed 2854.22 sec.
INFO: vacuuming "pg_toast.pg_toast_2361976783"
INFO: index "pg_toast_2361976783_index" now contains 24749150 row
versions in 108975 pages
DETAIL: 5857243 index row versions were removed.
33592 index pages have been deleted, 16007 are currently reusable.
CPU 4.15s/13.53u sec elapsed 78.56 sec.
INFO: "pg_toast_2361976783": removed 5857243 row versions in 1125801 pages
DETAIL: CPU 82.62s/69.48u sec elapsed 1571.43 sec.
INFO: "pg_toast_2361976783": found 5857243 removable, 24749150
nonremovable row versions in 10791766 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 33395357 unused item pointers.
0 pages are entirely empty.
CPU 235.46s/105.91u sec elapsed 4458.31 sec.
INFO: "pg_toast_2361976783": truncated 10791766 to 10778290 pages
DETAIL: CPU 0.21s/0.07u sec elapsed 7.09 sec.
INFO: analyzing "public.tbltimeseries"
INFO: "tbltimeseries": scanned 150000 of 5744789 pages, containing
691250 live rows and 0 dead rows; 150000 rows in sample, 26473903
estimated total rows

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.

The free space map is set at 11M pages and just today we popped up over
that amount in the vacuum output.  I don't think this is an issue here
though as the large number of unused item pointers has been present for
a while.

Thanks!

-- Alan

Alan Stange <stange@rentec.com> writes:
> INFO: "tbltimeseries": found 8610108 removable, 26165807 nonremovable
> row versions in 5744789 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 235555635 unused item pointers.
> 0 pages are entirely empty.

The item pointers themselves are not very interesting --- at 4 bytes
apiece, they're only accounting for 2% of the table space.  However
the fact that there are nearly 10x more unused than used ones suggests
that this table is suffering a pretty serious bloat problem.  Assuming
constant-width rows in the table, that implies something like 90% of
the space in the table is unused.  (contrib/pgstattuple might be useful
to confirm this estimate.)

VACUUM FULL, or perhaps better CLUSTER, would get you out of that.  And
yes, you will need to vacuum more often afterwards if you want to keep
the bloat under control.

            regards, tom lane

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