Thread: difference between a unique constraint and a unique index ???
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 ? Dave
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")
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
Alvaro Herrera <alvherre@alvh.no-ip.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.
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
On 12-Nov-07, at 11:37 AM, Tom Lane wrote: > Alvaro Herrera <alvherre@alvh.no-ip.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