Skip site navigation (1) Skip section navigation (2)

Re: difference between a unique constraint and a unique index ???

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Cc: postgresql performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: difference between a unique constraint and a unique index ???
Date: 2007-11-12 15:13:24
Message-ID: E36561B8-762D-41C9-A903-4413EE3B3253@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 12-Nov-07, at 9:56 AM, Alvaro Herrera wrote:

> 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.
>
You would be correct, thanks for the quick answer.

Dave

In response to

pgsql-performance by date

Next:From: Erik JonesDate: 2007-11-12 15:22:04
Subject: Re: PostgreSQL vs MySQL, and FreeBSD
Previous:From: Alvaro HerreraDate: 2007-11-12 14:56:30
Subject: Re: difference between a unique constraint and a uniqueindex ???

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group