X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id A2B11D1B4FE for ; Wed, 10 Sep 2003 19:38:10 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 61261-05 for ; Wed, 10 Sep 2003 16:38:01 -0300 (ADT) Received: from news.hub.org (unknown [64.117.224.194]) by svr1.postgresql.org (Postfix) with ESMTP id 05919D1B4E3 for ; Wed, 10 Sep 2003 16:38:00 -0300 (ADT) Received: from news.hub.org (host-64-117-224-194.altec1.com [64.117.224.194] (may be forged)) by news.hub.org (8.12.9/8.12.9) with ESMTP id h8AJbxsl024711 for ; Wed, 10 Sep 2003 19:37:59 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h8AJA0WG016559 for pgsql-performance@postgresql.org; Wed, 10 Sep 2003 19:10:00 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Upgrade Woes Date: Wed, 10 Sep 2003 14:25:19 -0400 Organization: Hub.Org Networking Services Lines: 35 Message-ID: <60he3kzek0.fsf@dev6.int.libertyrms.info> References: <20030910175340.GC5506@neteconomist.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1003 (Gnus v5.10.3) XEmacs/21.4 (Rational FORTRAN, linux) Cancel-Lock: sha1:fTCvURWh4L9JEAcB++ciMSsqexE= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/175 X-Sequence-Number: 3541 aturner@neteconomist.com writes: > P.S. Sorry this is so long, but I wanted to include as much info as possible. Throw in the non-commented lines in postgresql.conf; that would more than likely make numeric answers possible, for some of it. If the config is "out-of-the-box," then it's pretty likely that some significant improvements can be gotten from modifying a few of the config parameters. Increasing buffers would probably help query speed, and if you're getting too many dead tuples, increasing the free space map would make it possible for more to vacuum out. Beyond that, you might want to grab the code for pg_autovacuum, and drop that into place, as that would do periodic ANALYZEs that would probably improve the quality of your selects somewhat. (It's in the 7.4 code "contrib" base, but works fine with 7.3.) I think you might also get some significant improvements out of changing the way you load the properties. If you set up a schema that is suitably "permissive," and write a script that massages it a little, COPY should do the trick to load the data in, which should be helpful to the load process. If the data comes in a little more intelligently (which might well involve some parts of the process "dumbing down" :-)), you might take advantage of COPY and perhaps other things (we see through the glass darkly). I would think it also begs the question of whether or not you _truly_ need the "vacuum full." Are you _certain_ you need that? I would think it likely that running "vacuum analyze" (and perhaps doing it a little bit, continuously, during the load, via pg_autovacuum) would likely suffice. Have you special reason to think otherwise? -- output = ("cbbrowne" "@" "libertyrms.info") Christopher Browne (416) 646 3304 x124 (land)