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@center-f1.ru.
> 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@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

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