Re: text column indexing in UTF-8 database

On Thu, 2009-03-12 at 17:15 -0700, Reece Hart wrote:
> Jeff Davis gave me a tip to use text_pattern_ops on indexes to speed up
> regexp and like; that worked beautiful. But I discovered a caveat that
> t_p_o apparently doesn't handle equality. Thus, I think I need distinct
> indexes for the 4 cases above. Right?

It looks like an index using text_pattern_ops can be used for equality
(see my test case below).

This works apparently because texteq() is defined as bitwise-equality.
Is that really correct? I was under the impression that some locales do
not obey that rule, and may consider two slightly different strings to
be equal.

Regards,
Jeff Davis

create table a(t text);
create index a_idx on a (t text_pattern_ops);
insert into a values('foo');
set enable_seqscan='f';
analyze a;
explain analyze select * from a where t = 'foo';
QUERY PLAN
-----------------------------------------------
Index Scan using a_idx on a (cost=0.00..8.27 rows=1 width=4)
(actual time=0.009..0.010 rows=1 loops=1)
Index Cond: (t = 'foo'::text)
Total runtime: 0.036 ms
(3 rows)