Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 8549F9FA0EF for ; Sat, 9 Dec 2006 03:20:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 57847-01 for ; Sat, 9 Dec 2006 03:20:38 -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.183]) by postgresql.org (Postfix) with ESMTP id 051109F9FE1 for ; Sat, 9 Dec 2006 03:20:37 -0400 (AST) Received: by py-out-1112.google.com with SMTP id a73so51360pye for ; Fri, 08 Dec 2006 23:20:37 -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=kdmp6pcvyojyLiw7fngwsDEWaAtJw4+yIEL2TScH8KdauohMEysE8q9xQz7jSXfHla3jgKTdcaXQfTc4n61walqRw/jgWOHsRZut+IRhlu+7Q2E5RGm1qDV+pC0BilhCJ2niK5UPk8DYzlN1Me4MpEGUEdSSViTgqNIKFzB6E6Q= Received: by 10.35.134.19 with SMTP id l19mr7316174pyn.1165648837133; Fri, 08 Dec 2006 23:20:37 -0800 (PST) Received: by 10.35.80.10 with HTTP; Fri, 8 Dec 2006 23:20:37 -0800 (PST) Message-ID: Date: Sat, 9 Dec 2006 12:50:37 +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: 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/98 X-Sequence-Number: 23890 Sir, Suppose an index get corrupted. And you need create a new index with exact specs and then drop the old index. Is it better to have a performing corrupted index or not have it at all and temporarily suffer some performance degradation ? that was one scenerio which comes to my mind for having duplicate indexes. Regds mallah. On 12/9/06, Rajesh Kumar Mallah wrote: > 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 > > >