Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 3B6EC9FA0EF for ; Sat, 9 Dec 2006 03:08:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 04924-03 for ; Sat, 9 Dec 2006 03:07:56 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.178]) by postgresql.org (Postfix) with ESMTP id 009A49F9FE1 for ; Sat, 9 Dec 2006 03:07:57 -0400 (AST) Received: by py-out-1112.google.com with SMTP id a73so50099pye for ; Fri, 08 Dec 2006 23:07:57 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=cKd6lgmmZgx+kqTeLXY6ZzZ7dIyFaUpugdAhjpsJXdQwFvlMkzT4S8mwj1wK8S90Nccm/FLvfb4FFSPY/10NEb0FKofgXYWvMdd+UlnvzaTtfpVyO/RpVW6Pm9KBwZik4tWdTB+MuFVO6zF/OQnfv1zzym0l5/ykslKbj3q7xUM= Received: by 10.35.27.2 with SMTP id e2mr1954204pyj.1165648077117; Fri, 08 Dec 2006 23:07:57 -0800 (PST) Received: by 10.35.80.10 with HTTP; Fri, 8 Dec 2006 23:07:57 -0800 (PST) Message-ID: Date: Sat, 9 Dec 2006 12:37:57 +0530 From: "Rajesh Kumar Mallah" To: "Tom Lane" Subject: Re: Should duplicate indexes on same column and same table be allowed? Cc: "Lista Postgres" In-Reply-To: <8397.1165647371@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <8397.1165647371@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/97 X-Sequence-Number: 23889 On 12/9/06, Tom Lane wrote: > "Rajesh Kumar Mallah" writes: > > Some of our tables have duplicate indexes on same column by different > > index names. > > Should the database server check for the existance of (effectively) > > same index in > > a table before creating a new one. > > I'd vote not; I think this would get in the way of people who do know > what they're doing, as much as it would hold the hands of those who > don't. ("Build a database that even a fool can use, and only a fool > would want to use it.") > > An example: suppose you mistakenly created a plain index on foo.bar, > when you meant it to be a unique index. You don't want to just drop the > plain index before creating a unique index, because you have live > clients querying the table and their performance would tank with no > index at all. But surely a plain index and a unique index on the same > column are redundant, so a nannyish database should prevent you from > creating the desired index before dropping the unwanted one. I meant *exactly* the same index (pls ignore the word effectively in prv post). even same tablespace. Regds mallah. PS: (forgive me for my meager knowledge of internals) > > Other scenarios: is an index on X redundant with one on X,Y? Is a hash > index on X redundant if there's also a btree index on X? How about > partial or functional indexes with slightly varying definitions? > > There's been some discussion lately about an "index advisor", which > might reasonably provide some advice if it thinks you have redundant > indexes. But I'm not eager to put any sort of enforcement of the point > into the core database. > > regards, tom lane >