Skip site navigation (1) Skip section navigation (2)

Re: VACCUM FULL ANALYZE PROBLEM

From: PFC <lists(at)boutiquenumerique(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: VACCUM FULL ANALYZE PROBLEM
Date: 2005-02-15 08:51:22
Message-ID: opsl8djwxyth1vuj@musicbox (view raw or flat)
Thread:
Lists: pgsql-performance
	I don't know if this would work, but if you just want to restructure your  
rows, your could do this:

	UPDATE table SET id = id WHERE id BETWEEN 0 AND 20000;
	VACUUM table;
	UPDATE table SET id = id WHERE id BETWEEN 20001 AND 40000;
	VACUUM table;

	wash, rinse, repeat.

	The idea is that an update rewrites the rows (in your new format) and  
that VACUUM (not FULL) is quite fast when you just modified a part of the  
table, and non-locking.

	Would this work ?


> "Iain" <iain(at)mst(dot)co(dot)jp> writes:
>>> another  way  to speed up full vacuum?
>
>> Hmmm... a full vacuum may help to re-organize the structure of modified
>> tables, but whether this is significant or not is another matter.
>
> Actually, VACUUM FULL is designed to work nicely for the situation where
> a table has say 10% wasted space and you want the wasted space all
> compressed out.  When there is a lot of wasted space, so that nearly all
> the rows have to be moved to complete the compaction operation, VACUUM
> FULL is not a very good choice.  And it simply moves rows around, it
> doesn't modify the rows internally; so it does nothing at all to reclaim
> space that would have been freed up by DROP COLUMN operations.
>
> CLUSTER is actually a better bet if you want to repack a table that's
> suffered a lot of updates or deletions.  In PG 8.0 you might also
> consider one of the rewriting variants of ALTER TABLE.
>
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>



In response to

pgsql-performance by date

Next:From: Merlin MoncureDate: 2005-02-15 14:46:33
Subject: Re: seq scan cache vs. index cache smackdown
Previous:From: Greg StarkDate: 2005-02-15 08:10:39
Subject: Re: seq scan cache vs. index cache smackdown

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group