From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Dave Cramer <pg@fastcrypt.com>
Cc: postgresql performance list <pgsql-performance@postgresql.org>
Subject: Re: difference between a unique constraint and a unique index ???
Date: Mon, 12 Nov 2007 11:56:30 -0300
Message-ID: <20071112145630.GC14247@alvh.no-ip.org> (raw)
In-Reply-To: <E9D1152F-7846-4F03-A435-F1C05E034000@fastcrypt.com>
References: <E9D1152F-7846-4F03-A435-F1C05E034000@fastcrypt.com>
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")
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alvherre@alvh.no-ip.org, pg@fastcrypt.com
Subject: Re: difference between a unique constraint and a unique index ???
In-Reply-To: <20071112145630.GC14247@alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox