pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feeddifference between a unique constraint and a unique index ???
5+ messages / 3 participants
[nested] [flat]
* difference between a unique constraint and a unique index ???
@ 2007-11-12 14:51 Dave Cramer <pg@fastcrypt.com>
2007-11-12 14:56 ` Re: difference between a unique constraint and a unique index ??? Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 5+ messages in thread
From: Dave Cramer @ 2007-11-12 14:51 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: difference between a unique constraint and a unique index ???
2007-11-12 14:51 difference between a unique constraint and a unique index ??? Dave Cramer <pg@fastcrypt.com>
@ 2007-11-12 14:56 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2007-11-12 15:13 ` Re: difference between a unique constraint and a unique index ??? Dave Cramer <pg@fastcrypt.com>
2007-11-12 16:37 ` Re: difference between a unique constraint and a unique index ??? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Alvaro Herrera @ 2007-11-12 14:56 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: pgsql-performance
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")
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: difference between a unique constraint and a unique index ???
2007-11-12 14:51 difference between a unique constraint and a unique index ??? Dave Cramer <pg@fastcrypt.com>
2007-11-12 14:56 ` Re: difference between a unique constraint and a unique index ??? Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2007-11-12 15:13 ` Dave Cramer <pg@fastcrypt.com>
1 sibling, 0 replies; 5+ messages in thread
From: Dave Cramer @ 2007-11-12 15:13 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: difference between a unique constraint and a unique index ???
2007-11-12 14:51 difference between a unique constraint and a unique index ??? Dave Cramer <pg@fastcrypt.com>
2007-11-12 14:56 ` Re: difference between a unique constraint and a unique index ??? Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2007-11-12 16:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-12 18:32 ` Re: difference between a unique constraint and a unique index ??? Dave Cramer <pg@fastcrypt.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2007-11-12 16:37 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Dave Cramer <pg@fastcrypt.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: difference between a unique constraint and a unique index ???
2007-11-12 14:51 difference between a unique constraint and a unique index ??? Dave Cramer <pg@fastcrypt.com>
2007-11-12 14:56 ` Re: difference between a unique constraint and a unique index ??? Alvaro Herrera <alvherre@alvh.no-ip.org>
2007-11-12 16:37 ` Re: difference between a unique constraint and a unique index ??? Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-11-12 18:32 ` Dave Cramer <pg@fastcrypt.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dave Cramer @ 2007-11-12 18:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-11-12 18:32 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-12 14:51 difference between a unique constraint and a unique index ??? Dave Cramer <pg@fastcrypt.com>
2007-11-12 14:56 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2007-11-12 15:13 ` Dave Cramer <pg@fastcrypt.com>
2007-11-12 16:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-12 18:32 ` Dave Cramer <pg@fastcrypt.com>
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