Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 3B7DF37C694 for ; Mon, 12 Nov 2007 14:32:40 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 96730-07 for ; Mon, 12 Nov 2007 14:32:32 -0400 (AST) Received: from beaver.fastcrypt.com (beaver.fastcrypt.com [64.7.141.17]) by postgresql.org (Postfix) with ESMTP id C9C2537C687 for ; Mon, 12 Nov 2007 14:32:33 -0400 (AST) Received: from [192.168.1.14] (dcdsl.ebox.com [64.7.143.116]) by beaver.fastcrypt.com (Postfix) with ESMTP id 8CA0C1580658; Mon, 12 Nov 2007 13:42:15 -0500 (EST) Cc: Alvaro Herrera , postgresql performance list Message-Id: <694A5959-73AC-4C18-AA5F-489407A6CFDC@fastcrypt.com> From: Dave Cramer To: Tom Lane In-Reply-To: <6836.1194885434@sss.pgh.pa.us> Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Mime-Version: 1.0 (Apple Message framework v912) Subject: Re: difference between a unique constraint and a unique index ??? Date: Mon, 12 Nov 2007 13:32:34 -0500 References: <20071112145630.GC14247@alvh.no-ip.org> <6836.1194885434@sss.pgh.pa.us> X-Mailer: Apple Mail (2.912) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/188 X-Sequence-Number: 27603 On 12-Nov-07, at 11:37 AM, Tom Lane wrote: > Alvaro Herrera writes: >> Well, AFAIK the index with varchar_pattern_ops is used for LIKE >> queries, >> whereas the other one is going to be used for = queries. So you >> need to >> keep both indexes. > > Given the current definition of text equality, it'd be possible to > drop > ~=~ and have the standard = operator holding the place of equality in > both the regular and pattern_ops opclasses. Then it'd be possible to > support regular equality queries, as well as LIKE, with only the > pattern_ops index. > That would be ideal. Having two indexes on the same column isn't optimal. Dave