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 E6459D1EA71 for ; Fri, 2 Apr 2004 04:36:59 +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 42904-01 for ; Fri, 2 Apr 2004 00:36:48 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 6835BD1D5A3 for ; Fri, 2 Apr 2004 00:36:47 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i324ac56018755; Thu, 1 Apr 2004 23:36:38 -0500 (EST) To: josh@agliodbs.com Cc: Palle Girgensohn , pgsql-performance@postgresql.org Subject: Re: single index on more than two coulumns a bad thing? In-reply-to: <200404011635.45440.josh@agliodbs.com> References: <24070000.1080860445@palle.girgensohn.se> <200404011635.45440.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Thu, 01 Apr 2004 16:35:45 -0800" Date: Thu, 01 Apr 2004 23:36:38 -0500 Message-ID: <18754.1080880598@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/4 X-Sequence-Number: 6304 Josh Berkus writes: >> Is it always bad to create index xx on yy (field1, field2, field3); > I'm afraid that you've been given some misleading advice. I'd say it's a matter of getting your optimizations straight. If you have a query that can make use of that index, and the query is executed often enough to make it worth maintaining the index during table updates, then by all means make the index. The standard advice is meant to warn you against creating a zillion indexes without any thought to what you'll be paying in update costs. Indexes with more than a couple of columns are usually of only narrow applicability, and so you have to be sure that they'll really pay for themselves... regards, tom lane