From: "Fernando Hevia" <fhevia@ip-tel.com.ar>
To: "'Gregory Stark'" <stark@enterprisedb.com>,
	"'Bill Moran'" <wmoran@collaborativefusion.com>
Cc: <pgsql-performance@postgresql.org>
References: 
 <042c01c90496$ca8725f0$8f01010a@iptel.com.ar><20080822184407.37155367.wmoran@collaborativefusion.com>
	<87r68gpns3.fsf@oxford.xeocode.com>
Subject: Re: Big delete on big table... now what?
Date: Mon, 25 Aug 2008 10:38:08 -0300
Organization: IP-TEL
Message-ID: <04cd01c906b7$cfa0bf10$8f01010a@iptel.com.ar>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <87r68gpns3.fsf@oxford.xeocode.com>
Thread-Index: AckEuauHPRB93uW0Qdi10PGzLVp40wB+YBwA

> Gregory Stark <start@enterprisedb.com> writes:
>=20
> "Bill Moran" <wmoran@collaborativefusion.com> writes:
>=20
> > "Fernando Hevia" <fhevia@ip-tel.com.ar> wrote:
> >> Hi list.
> >> I have a table with over 30 million rows. Performance was dropping=20
> >> steadily so I moved old data not needed online to an=20
> historic table.=20
> >> Now the table has about 14 million rows. I don't need the=20
> disk space=20
> >> returned to the OS but I do need to improve performance.=20
> Will a plain=20
> >> vacuum do or is a vacuum 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=20
> to improve=20
> > the performance much (although it may shrink the table _some_) but=20
> > regular vacuum will keep performance from getting any worse.
>=20
> Note that CLUSTER requires enough space to store the new and=20
> the old copies of the table simultaneously. That's the main=20
> reason for VACUUM FULL to still exist.
>=20
> There is also the option of doing something like (assuming id=20
> is already an integer -- ie this doesn't actually change the data):
>=20
>  ALTER TABLE x ALTER id TYPE integer USING id;
>=20
> which will rewrite the whole table. This is effectively the=20
> same as CLUSTER except it doesn't order the table according=20
> to an index. It will still require enough space to hold two=20
> copies of the table but it will be significantly faster.
>=20

Yes, I can afford a downtime on Sunday.
Actually the clustering option would help since most of our slow queries =
use
the same index.

Thanks Bill and Gregory for the advice.
Regards,
Fernando.