Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 52DEC37C839 for ; Mon, 12 Nov 2007 10:59:46 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 10107-01-9 for ; Mon, 12 Nov 2007 10:59:29 -0400 (AST) Received: from perhan.alvh.no-ip.org (200-126-65-209.bk5-dsl.surnet.cl [200.126.65.209]) by postgresql.org (Postfix) with ESMTP id D20B537C96D for ; Mon, 12 Nov 2007 10:56:18 -0400 (AST) Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 9F6CC47C7A; Mon, 12 Nov 2007 11:56:30 -0300 (CLST) Date: Mon, 12 Nov 2007 11:56:30 -0300 From: Alvaro Herrera To: Dave Cramer Cc: postgresql performance list Subject: Re: difference between a unique constraint and a unique index ??? Message-ID: <20071112145630.GC14247@alvh.no-ip.org> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.16 (2007-06-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.004 tagged_above=0 required=5 tests=AWL=0.065, BAYES_50=0.001, RCVD_IN_DSBL=0.961, RCVD_IN_SORBS_DUL=0.877, RDNS_DYNAMIC=0.1 X-Spam-Level: ** X-Archive-Number: 200711/175 X-Sequence-Number: 27590 Dave Cramer wrote: > In order to get like queries to use an index with database initialized with > a UTF-8 character set I added a unique index to a table with a > varchar_pattern_ops > > This table already had a unique constraint on the column so I dropped the > unique constraint. > > I can't give exact measurements however this caused my application to slow > down considerably. > > The only thing I can figure is that the varchar_pattern_ops operator is > significantly slower ??? > > Is there some other piece of the puzzle to fill in ? 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. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC Officer Krupke, what are we to do? Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke")