Re: pgstattuple, vacuum and free_space

hi all,

I have a question :-
when we should consider vacuuming the table
is there an average *dead_tuple_percent* that should be taken into account
before vacuuming any table ?. i have

rpt_production=# select * from pgstattuple('table1');
-[ RECORD 1 ]------+----------
table_len | 105537536
tuple_count | 126420
tuple_len | 41581608
tuple_percent | 39.4
dead_tuple_count | 9792
dead_tuple_len | 3284784
dead_tuple_percent | 3.11
free_space | 58950944
free_percent | 55.86

Should i vacuum this table ?.

regards
Gourish

On 11/25/05, Colton Smith <smith(at)skio(dot)peachnet(dot)edu> wrote:
>
> Hi:
>
> I did the following after installing the pgstattuple contrib code:
>
> select * from pgstattuple('wind');
> -[ RECORD 1 ]------+----------
> table_len | 224854016
> tuple_count | 1492601
> tuple_len | 207535124
> tuple_percent | 92.3
> dead_tuple_count | 11569
> dead_tuple_len | 1361848
> dead_tuple_percent | 0.61
> free_space | 3311416
> free_percent | 1.47
>
>
> vacuum verbose wind;
> INFO: vacuuming "public.wind"
> INFO: index "wind_pkey" now contains 1492601 row versions in 4152 pages
> DETAIL: 11569 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.56s/1.77u sec elapsed 36.73 sec.
> INFO: index "wind_measurement_date_index" now contains 1492601 row
> versions in 4156 pages
> DETAIL: 11569 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.43s/1.83u sec elapsed 35.90 sec.
> INFO: "wind": removed 11569 row versions in 371 pages
> DETAIL: CPU 0.03s/0.03u sec elapsed 2.38 sec.
> INFO: "wind": found 11569 removable, 1492601 nonremovable row versions
> in 27448 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 2.01s/3.95u sec elapsed 79.39 sec.
> VACUUM
>
> select * from pgstattuple('wind');
> -[ RECORD 1 ]------+----------
> table_len | 224854016
> tuple_count | 1492601
> tuple_len | 207535124
> tuple_percent | 92.3
> dead_tuple_count | 0
> dead_tuple_len | 0
> dead_tuple_percent | 0
> free_space | 4673256
> free_percent | 2.08
>
>
> My question: when you vacuum a table and generate 'free_space', who is
> allowed to consume this 'free_space'? Is it released to the OS for
> general use? Or is it reserved just for the database? If the latter, is
> it reserved just for 'wind' (in this case)?
>
> Thanks again!
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>