Re: text column indexing in UTF-8 database

On Thu, 2009-03-12 at 18:02 -0700, Jeff Davis wrote:
> It looks like an index using text_pattern_ops can be used for equality
> (see my test case below).

Odd. I can't reproduce your test case. I noticed that I edited out the
version and platform from my OP. (A: 8.3.6, x86_64 linux). You're on
8.3.6, or do you happen to be testing on the 8.4 branch?

I see this:

rkh(at)rkh=> \i tpo-test.sql
version
--------------------------------------------------------------------------------------------
PostgreSQL 8.3.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.0 (SUSE Linux)

select name,setting from pg_settings where name~'locale|encoding';
name | setting
-----------------+---------
client_encoding | UTF8
server_encoding | UTF8
(2 rows)

\!echo $LANG
en_US.UTF-8

create temp table a(t text);
CREATE TABLE

insert into a values('foo');
INSERT 0 1

set enable_seqscan='f';
SET

create index a_t_tpo on a (t text_pattern_ops);
CREATE INDEX

analyze a;
ANALYZE

explain analyze select * from a where t = 'foo';
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------
Seq Scan on a (cost=100000000.00..100000001.01 rows=1 width=4) (actual
time=0.014..0.016 rows=1 loops=1)
Filter: (t = 'foo'::text)
Total runtime: 0.047 ms
(3 rows)

create index a_t on a (t);
CREATE INDEX

analyze a;
ANALYZE

explain analyze select * from a where t = 'foo';
QUERY
PLAN
-------------------------------------------------------------------------------------------------------
Index Scan using a_t on a (cost=0.00..8.27 rows=1 width=4) (actual
time=0.061..0.062 rows=1 loops=1)
Index Cond: (t = 'foo'::text)
Total runtime: 0.099 ms
(3 rows)

script at http://harts.net/reece/tpo-test.sql

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0