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 9FC6DD1E930 for ; Fri, 2 Apr 2004 11:56:29 +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 88171-02 for ; Fri, 2 Apr 2004 07:56:30 -0400 (AST) Received: from hotmail.com (law10-oe58.law10.hotmail.com [64.4.14.193]) by svr1.postgresql.org (Postfix) with ESMTP id 7406ED1E8C6 for ; Fri, 2 Apr 2004 07:56:27 -0400 (AST) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Fri, 2 Apr 2004 03:56:30 -0800 Received: from 67.81.102.201 by law10-oe58.law10.hotmail.com with DAV; Fri, 02 Apr 2004 11:56:30 +0000 X-Originating-IP: [67.81.102.201] X-Originating-Email: [awerman2@hotmail.com] X-Sender: awerman2@hotmail.com From: "Aaron Werman" To: , "Palle Girgensohn" , References: <24070000.1080860445@palle.girgensohn.se> <200404011635.45440.josh@agliodbs.com> Subject: Re: single index on more than two coulumns a bad thing? Date: Fri, 2 Apr 2004 06:56:42 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Message-ID: X-OriginalArrivalTime: 02 Apr 2004 11:56:30.0273 (UTC) FILETIME=[89261310:01C418A9] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/9 X-Sequence-Number: 6309 another thing that I have all over the place is a hierarchy: index on grandfather_table(grandfather) index on father_table(grandfather, father) index on son_table(grandfather, father, son) almost all of my indices are composite. Are you thinking about composite indices with low cardinality leading columns? /Aaron ----- Original Message ----- From: "Josh Berkus" To: "Palle Girgensohn" ; Sent: Thursday, April 01, 2004 7:35 PM Subject: Re: [PERFORM] single index on more than two coulumns a bad thing? > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >