Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id E67289FA248 for ; Mon, 11 Dec 2006 15:31:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 29534-07 for ; Mon, 11 Dec 2006 15:31:14 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail01.enterprisedb.com (mail01.enterprisedb.com [63.246.7.168]) by postgresql.org (Postfix) with ESMTP id AC4909FA182 for ; Mon, 11 Dec 2006 15:31:15 -0400 (AST) thread-index: AccdWu4d0jX+fyBiSQmvWvHrAZfBJg== Received: from [192.168.0.20] ([84.12.196.220]) by mail01.enterprisedb.com with Microsoft SMTPSVC(6.0.3790.1830); Mon, 11 Dec 2006 14:31:17 -0500 Subject: Re: Should duplicate indexes on same column and same tablebe allowed? From: "Simon Riggs" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2757 Content-Class: urn:content-classes:message Importance: normal To: "Tom Lane" Cc: "Rajesh Kumar Mallah" , "Lista Postgres" In-Reply-To: <18891.1165686391@sss.pgh.pa.us> References: <8397.1165647371@sss.pgh.pa.us> <18891.1165686391@sss.pgh.pa.us> Content-Type: text/plain; charset="iso-8859-1" Date: Mon, 11 Dec 2006 19:30:27 +0000 Message-ID: <1165865427.3816.90.camel@silverbirch.site> MIME-Version: 1.0 X-Mailer: Evolution 2.6.0 Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 11 Dec 2006 19:31:17.0218 (UTC) FILETIME=[ED7FB420:01C71D5A] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/150 X-Sequence-Number: 23942 On Sat, 2006-12-09 at 12:46 -0500, Tom Lane wrote: > "Rajesh Kumar Mallah" writes: > > 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 ? > > The case that was being discussed just a day or two ago was where you > wanted to do the equivalent of REINDEX because of index bloat, not any > functional "corruption". In that case it's perfectly clear that > temporarily not having the index isn't acceptable ... especially if > it's enforcing a unique constraint. I can see that is quite handy. However, I can't see any benefit to allowing multiple FKs: postgres=# alter table accounts add foreign key (bid) references branches; ALTER TABLE Time: 1678.240 ms postgres=# alter table accounts add foreign key (bid) references branches; ALTER TABLE Time: 909.706 ms postgres=# alter table accounts add foreign key (bid) references branches; ALTER TABLE Time: 507.673 ms postgres=# alter table accounts add foreign key (bid) references branches; ALTER TABLE Time: 597.909 ms postgres=# alter table accounts add foreign key (bid) references branches; ALTER TABLE Time: 677.125 ms postgres=# \d accounts Table "public.accounts" Column | Type | Modifiers ----------+---------+----------- aid | integer | not null bid | integer | abalance | integer | Indexes: "accounts_pkey" PRIMARY KEY, btree (aid) Foreign-key constraints: "accounts_bid_fkey" FOREIGN KEY (bid) REFERENCES branches(bid) "accounts_bid_fkey1" FOREIGN KEY (bid) REFERENCES branches(bid) "accounts_bid_fkey2" FOREIGN KEY (bid) REFERENCES branches(bid) "accounts_bid_fkey3" FOREIGN KEY (bid) REFERENCES branches(bid) "accounts_bid_fkey4" FOREIGN KEY (bid) REFERENCES branches(bid) -- Simon Riggs EnterpriseDB http://www.enterprisedb.com