Re: Big number of "unused" pages as reported by VACUUM

I think you want to use VACUUM FULL to actually shrink the table. In
7.2.X, VACUUM only records free space for later reuse.

---------------------------------------------------------------------------

Yury Bokhoncovich wrote:
> Hello!
>
> Some time ago I've got troubles with performance of my PG.
> After investigation I had found that the most probable reason was the big
> number of "unused" pages. Below follows what VACUUM reported:
>
> =======================
> vacuum verbose goods;
> NOTICE: --Relation goods--
> NOTICE: Pages 15068: Changed 0, Empty 0; Tup 16157: Vac 0, Keep 0, UnUsed 465938.
> =======================
> select count(*) from goods;
> count
> -------
> 16157
> =======================
>
> The same schema with the almost identical number of rows gives completely
> different result on another table:
> =======================
> vacuum verbose goods;
> NOTICE: --Relation goods--
> NOTICE: Pages 912: Changed 0, Empty 0; Tup 11209: Vac 0, Keep 0, UnUsed
> 19778.
> =======================
> select count(*) from goods;
> count
> -------
> 11209
> =======================
>
> Two questions:
>
> 1) Where to seek the real source of the enormous big number of unused
> pages?
>
> 2) How to shrink the table (i.e. how can I get rid those unused pages)?
>
> PG: was 7.2.1, now 7.2.2.
>
> --
> WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group.
> Phone: +7 (3832) 106228, ext.140, E-mail: byg(at)center-f1(dot)ru(dot)
> Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073