Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6JFVSa63628 for ; Thu, 19 Jul 2001 11:31:28 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 88042; Thu, 19 Jul 2001 08:31:08 -0700 From: "Josh Berkus" Subject: Re: Query optimizing - paradox behave To: "David M. Richter" , pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Thu, 19 Jul 2001 08:31:08 -0700 Message-ID: In-Reply-To: <3B56B479.123C88A3@DKFZ-heidelberg.de> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===88042====davinci.ethosmedia.com===_" X-Archive-Number: 200107/261 X-Sequence-Number: 4058 This is a multi-part MIME message --_===88042====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit David, You will no doubt hear later from the tuning experts on the list. However, let me save everybody some time by verifying some basics: 1. When you restructured the database, you ran VACUUM ANALYZE on the new database (pacs)? 2. You said that you "eliminated the indexes" because they weren't helping performance. Is this right? It seems a little hard to figure from here. 3. General Advice: If you're concerned about query performance, get rid of those VARCHAR(80) primary keys and replace them with INT4 or INT8! The math is easy to do: If you're processing INT8 keys for 1,000,000 table rows that's 8,000,000 (roughly 8mb) data on disk and data being processed. If you're processing VARCHAR(80) keys for 1,000,000 table rows, thats 82,000,000 bytes (82 mb) on disk and in ram to be processed. In theory, you could get a 10-fold increase in JOIN performance by switching to INT8 keys. In practice, its probably more like double or triple but that ain't bad, either. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco --_===88042====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===88042====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===88042====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===88042====davinci.ethosmedia.com===_--