Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B1D319FBE14 for ; Tue, 23 Oct 2007 14:41:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 05498-02 for ; Tue, 23 Oct 2007 14:40:57 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 7ECF29FBF09 for ; Tue, 23 Oct 2007 14:40:57 -0300 (ADT) Received: from scratch (or-69-34-217-90.sta.embarqhsd.net [69.34.217.90]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l9NHeuHa031195; Tue, 23 Oct 2007 10:40:57 -0700 Date: Tue, 23 Oct 2007 10:41:13 -0700 From: "Joshua D. Drake" To: Ron St-Pierre Cc: Alvaro Herrera , pgsql-performance@postgresql.org Subject: Re: 12 hour table vacuums Message-ID: <20071023104113.1f4d0aa0@scratch> In-Reply-To: <471E2895.4030601@shaw.ca> References: <471E18ED.3090704@shaw.ca> <20071023161204.GK18013@alvh.no-ip.org> <471E2895.4030601@shaw.ca> Organization: Joshua D. Drake X-Mailer: Claws Mail 3.0.2 (GTK+ 2.12.0; x86_64-pc-linux-gnu) Mime-Version: 1.0 Content-Type: multipart/signed; boundary="Sig_/Q_3cMLBJDchu5.TbGfRd2NP"; protocol="application/pgp-signature"; micalg=PGP-SHA1 X-Virus-Scanned: ClamAV 0.91.1/4572/Tue Oct 23 00:50:50 2007 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Tue, 23 Oct 2007 10:40:59 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/262 X-Sequence-Number: 27284 --Sig_/Q_3cMLBJDchu5.TbGfRd2NP Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable On Tue, 23 Oct 2007 10:00:05 -0700 Ron St-Pierre wrote: > Alvaro Herrera wrote: > > Ron St-Pierre wrote: > > > > =20 > >> Okay, here's our system: > >> postgres 8.1.4 > >> =20 > > > > Upgrade to 8.1.10 > > =20 > Any particular fixes in 8.1.10 that would help with this? > > =20 > >> Here's the table information: > >> The table has 140,000 rows, 130 columns (mostly NUMERIC), 60 > >> indexes.=20 > > > > 60 indexes? You gotta be kidding. You really have 60 columns on > > which to scan? > > > > =20 > Really. 60 indexes. They're the most commonly requested columns for=20 > company information (we believe). Any ideas on testing our > assumptions about that? I would like to know definitively what are > the most popular columns. Do you think that rules would be a good > approach for this? (Sorry if I'm getting way off topic here) I suggest you: 1. Turn on stats and start looking in the stats columns to see what indexes are actually being used. 2. Strongly review your normalization :) Joshua D. Drake --=20 =3D=3D=3D The PostgreSQL Company: Command Prompt, Inc. =3D=3D=3D Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ --Sig_/Q_3cMLBJDchu5.TbGfRd2NP Content-Type: application/pgp-signature; name=signature.asc Content-Disposition: attachment; filename=signature.asc -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHHjI8ATb/zqfZUUQRAkpgAKCiseiXgoj77dFVDooYe3ZmklfihACdF7ke yGxagAygenFotsYW2cz5A94= =jkJg -----END PGP SIGNATURE----- --Sig_/Q_3cMLBJDchu5.TbGfRd2NP--