X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 656BD475F34 for ; Mon, 24 Feb 2003 14:03:54 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 38845-02 for ; Mon, 24 Feb 2003 14:03:43 -0500 (EST) Received: from jester.inquent.com (unknown [216.208.117.7]) by postgresql.org (Postfix) with ESMTP id BEC94475E3E for ; Mon, 24 Feb 2003 13:38:45 -0500 (EST) Received: from [127.0.0.1] (localhost [127.0.0.1]) by jester.inquent.com (8.12.6/8.12.6) with ESMTP id h1OIdC9P011671; Mon, 24 Feb 2003 13:39:13 -0500 (EST) (envelope-from rbt@rbt.ca) Subject: Re: slow query From: Rod Taylor To: Clarence Gardner Cc: Robert Treat , Oleg Lebedev , Josh Berkus , "pgsql-performance@postgresql.org" In-Reply-To: References: Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-2ofMzJOskQwspJ0Mxr9R" Organization: Message-Id: <1046111952.632.34.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 Date: 24 Feb 2003 13:39:12 -0500 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/182 X-Sequence-Number: 1238 --=-2ofMzJOskQwspJ0Mxr9R Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Mon, 2003-02-24 at 12:27, Clarence Gardner wrote: > On 24 Feb 2003, Robert Treat wrote: >=20 > >=20 > > If your seeing wildly dramatic improvments from vacuum full, you might > > want to look into running regular vacuums more often (especially for > > high turnover tables), increase your max_fsm_relations to 1000, and > > increasing your max_fsm_pages.=20 >=20 > I don't know about the settings you mention, but a frequent vacuum > does not at all obviate a vacuum full. My database is vacuumed every > night, but a while ago I found that a vacuum full changed a simple > single-table query from well over 30 seconds to one or two. We now > do a vacuum full every night. Sure is does. If your free-space-map (FSM) is up to date, tuples are not appended to the end of the table, so table growth will not occur (beyond a 'settling' point. Unless you remove more data from the table than you ever expect to have in the table again, this is enough. That said, the instant the FSM is empty, you're table starts to grow again and a VACUUM FULL will be required to re-shrink it. --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-2ofMzJOskQwspJ0Mxr9R Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (FreeBSD) iD8DBQA+WmbQ6DETLow6vwwRAnpFAJ9BY19SakriGuJg8WNIfrRHAEuRtQCfXzA7 biQ2S7m6qUjYIkjZwoFKvC4= =xri6 -----END PGP SIGNATURE----- --=-2ofMzJOskQwspJ0Mxr9R--