Re: Vacuum runs in a loop? - Mailing list pgsql-novice

From Tom Lane
Subject Re: Vacuum runs in a loop?
Date
Msg-id 17243.1063648034@sss.pgh.pa.us
Whole thread Raw
In response to Vacuum runs in a loop?  (Wim <wdh@belbone.be>)
Responses Re: Vacuum runs in a loop?
List pgsql-novice
Wim <wdh@belbone.be> writes:
> I deleted half of the records in my table and to free up the space I do
> a VACUUM VERBOSE.
> Is it normal that my VACUUM VERBOSE runs in a loop?
> If not, what can I do to free up disk space?

It's normal for VACUUM to make multiple passes over the indexes when
it's got to clean up lots of tuples.  You can reduce the number of
passes by increasing vacuum_mem, which basically limits the number of
dead tuples that VACUUM can remember at one time.

It seems odd though that the indexes report "Deleted 0" each time.

> INFO:  Index i_flowdata_dst_as: Pages 171123; Tuples 23875181: Deleted
> 0.
>         CPU 10.78s/14.52u sec elapsed 831.52 sec.

Normally I'd expect the index deleted count to agree with the number of
tuples removed from the underlying table in each pass:

> INFO:  Removed 1397825 tuples in 21505 pages.
>         CPU 2.36s/2.65u sec elapsed 73.53 sec.

These wouldn't happen to be partial indexes would they?  If they're
partial, and would not have had entries for the rows you are cleaning
out, then the behavior makes sense to me.  Otherwise there's something
weird going on.

The indexes seem a tad bloated compared to the underlying file size
anyway.  Maybe you should REINDEX them.

            regards, tom lane

pgsql-novice by date:

Previous
From: "Max Nachlinger"
Date:
Subject: recursive call to function returning SETOF
Next
From: Stephan Szabo
Date:
Subject: Re: recursive call to function returning SETOF