X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1A26FD1D44F for ; Fri, 2 Apr 2004 00:36:17 +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 71483-09 for ; Thu, 1 Apr 2004 20:36:17 -0400 (AST) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 2369FD1E9D5 for ; Thu, 1 Apr 2004 20:36:14 -0400 (AST) Received: from [63.195.55.98] (HELO 192.168.1.29) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 4750237; Thu, 01 Apr 2004 16:37:35 -0800 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Palle Girgensohn , pgsql-performance@postgresql.org Subject: Re: single index on more than two coulumns a bad thing? Date: Thu, 1 Apr 2004 16:35:45 -0800 User-Agent: KMail/1.5.4 References: <24070000.1080860445@palle.girgensohn.se> In-Reply-To: <24070000.1080860445@palle.girgensohn.se> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200404011635.45440.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/3 X-Sequence-Number: 6303 Palle, > Is it always bad to create index xx on yy (field1, field2, field3); No, it seldom bad, in fact. I have some indexes that run up to seven columns, becuase they are required for unique keys. Indexes of 3-4 columns are often *required* for many-to-many join tables. I'm afraid that you've been given some misleading advice. > I guess the problem is that the index might often grow bigger than the > table, or at least big enough not to speed up the queries? Well, yes ... a 4-column index on a 5-column table could be bigger than the table if allowed to bloat and not re-indexed. But that's just a reason for better maintainence. -- -Josh Berkus Aglio Database Solutions San Francisco