X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6DD2AD1B445 for ; Thu, 4 Dec 2003 19:34:08 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 97909-10 for ; Thu, 4 Dec 2003 15:33:38 -0400 (AST) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 1DDBFD1B483 for ; Thu, 4 Dec 2003 15:33:35 -0400 (AST) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 4022845; Thu, 04 Dec 2003 11:34:25 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Jack Coates , pgsql-performance Subject: Re: tuning questions Date: Thu, 4 Dec 2003 11:20:21 -0800 User-Agent: KMail/1.4.3 References: <1070553983.6498.52.camel@cletus.lyris.com> <3FCF6AEB.908@dsvr.net> <1070565411.13923.70.camel@cletus.lyris.com> In-Reply-To: <1070565411.13923.70.camel@cletus.lyris.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200312041120.21124.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/88 X-Sequence-Number: 4948 Jack, > Following this, I've done: > 2gb ram > =3D > 2,000,000,000 > bytes This calculation is fun, but I really don't know where you got it from. I= t=20 seems quite baroque. What are you trying to set, exactly? > getting the SQL query better optimized for PG is on my todo list, but > not something I can do right now -- this application is designed to be > cross-platform with MS-SQL, PG, and Oracle so tweaking SQL is a touchy > subject. Well, if you're queries are screwed up, no amount of .conf optimization is= =20 going to help you much. You could criticize that PG is less adept than= =20 some other systems at re-writing "bad queries", and you would be correct.= =20=20 However, there's not much to do about that on existing systems. How about posting some sample code? > The pgavd conversation is intriguing, but I don't really understand the > role of vacuuming. Would this be a correct statement: "PG needs to > regularly re-evaluate the database in order to adjust itself?" I'm > imagining that it continues to treat the table as a small one until > vacuum informs it that the table is now large? Not Vacuum, Analyze. Otherwise correct. Mind you, in "regular use" where= =20 only a small % of the table changes per hour, periodic ANALYZE is fine.=20= =20 However, in "batch data transform" analyze statements need to be keyed to t= he=20 updates and/or imports. BTW, I send a couple of e-mails to the Lyris documentation maintainer about= =20 updating out-of-date information about setting up PostgreSQL. I never got= a=20 response, and I don't think my changes were made. --=20 -Josh Berkus Aglio Database Solutions San Francisco