To: "Bill Moran" <wmoran@collaborativefusion.com>
Cc: "Fernando Hevia" <fhevia@ip-tel.com.ar>,
 <pgsql-performance@postgresql.org>
Subject: Re: Big delete on big table... now what?
In-Reply-To: <20080822184407.37155367.wmoran@collaborativefusion.com> (Bill
	Moran's message of "Fri\, 22 Aug 2008 18\:44\:07 -0400")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <042c01c90496$ca8725f0$8f01010a@iptel.com.ar>
	<20080822184407.37155367.wmoran@collaborativefusion.com>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Sat, 23 Aug 2008 01:39:56 +0100
Message-ID: <87r68gpns3.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

"Bill Moran" <wmoran@collaborativefusion.com> writes:

> "Fernando Hevia" <fhevia@ip-tel.com.ar> wrote:
>>
>> Hi list.
>>=20=20
>> I have a table with over 30 million rows. Performance was dropping stead=
ily
>> so I moved old data not needed online to an historic table. Now the table
>> has about 14 million rows. I don't need the disk space returned to the OS
>> but I do need to improve performance. Will a plain vacuum do or is a vac=
uum
>> full necessary?
>> =BFWould a vacuum full improve performance at all?
>
> If you can afford the downtime on that table, cluster would be best.
>
> If not, do the normal vacuum and analyze.  This is unlikely to improve
> the performance much (although it may shrink the table _some_) but
> regular vacuum will keep performance from getting any worse.

Note that CLUSTER requires enough space to store the new and the old copies=
 of
the table simultaneously. That's the main reason for VACUUM FULL to still
exist.

There is also the option of doing something like (assuming id is already an
integer -- ie this doesn't actually change the data):

 ALTER TABLE x ALTER id TYPE integer USING id;

which will rewrite the whole table. This is effectively the same as CLUSTER
except it doesn't order the table according to an index. It will still requ=
ire
enough space to hold two copies of the table but it will be significantly
faster.

--=20
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!