From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: 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:37:14 -0500
Message-ID: <6836.1194885434@sss.pgh.pa.us> (raw)
In-Reply-To: <20071112145630.GC14247@alvh.no-ip.org>
References: <E9D1152F-7846-4F03-A435-F1C05E034000@fastcrypt.com>
<20071112145630.GC14247@alvh.no-ip.org>
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
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: tgl@sss.pgh.pa.us, alvherre@alvh.no-ip.org, pg@fastcrypt.com
Subject: Re: difference between a unique constraint and a unique index ???
In-Reply-To: <6836.1194885434@sss.pgh.pa.us>
* 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