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: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, postgresql performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: difference between a unique constraint and a unique index ???
Date: 2007-11-12 18:32:34
Message-ID: 694A5959-73AC-4C18-AA5F-489407A6CFDC@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 12-Nov-07, at 11:37 AM, Tom Lane wrote:

> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 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

In response to

pgsql-performance by date

Next:From: Stephane BailliezDate: 2007-11-12 19:57:48
Subject: ERROR: "invalid memory alloc request size" or "unexpected end of data" on large table
Previous:From: Heikki LinnakangasDate: 2007-11-12 18:03:16
Subject: Re: Need to run CLUSTER to keep performance

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