Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id B5A6437C6E0 for ; Mon, 12 Nov 2007 12:38:51 -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 61170-04 for ; Mon, 12 Nov 2007 12:38:46 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 056E737C6FA for ; Mon, 12 Nov 2007 12:38:46 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id lACGbE7U006837; Mon, 12 Nov 2007 11:37:14 -0500 (EST) To: Alvaro Herrera cc: Dave Cramer , postgresql performance list Subject: Re: difference between a unique constraint and a unique index ??? In-reply-to: <20071112145630.GC14247@alvh.no-ip.org> References: <20071112145630.GC14247@alvh.no-ip.org> Comments: In-reply-to Alvaro Herrera message dated "Mon, 12 Nov 2007 11:56:30 -0300" Date: Mon, 12 Nov 2007 11:37:14 -0500 Message-ID: <6836.1194885434@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/184 X-Sequence-Number: 27599 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. This would break any applications explicitly using ~=~, but how many of those are there? (For backwards compatibility it'd be nice if we could allow both = and ~=~ in the opclass, but the unique index on pg_amop seems to preclude that.) regards, tom lane