text column indexing in UTF-8 database

From: Reece Hart <reece(at)harts(dot)net>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: text column indexing in UTF-8 database
Date: 2009-03-13 00:15:05
Message-ID: 1236903305.22843.57.camel@snafu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Do I really need 4 indexes per column to handle the 4 combinations of
{equality,like/regexp} x {unfolded,casefolded} in a UTF-8 encoded
database?

I have a column that I'd like to be able to search with equality and
regexp (or like), optionally casefolded. The database is UTF-8 encoded.
The table and index defs are below.

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?

Thanks,
Reece

rkh(at)csb-dev=> \d pannotation
Table "unison.pannotation"
Column | Type | Modifiers
----------------+--------------------------+------------------------
pannotation_id | integer | not null default
origin_id | integer | not null
alias | text | not null
descr | text |
tax_id | integer |
added | timestamp with time zone | not null default timenow()
Indexes:
...
"pannotation_alias" btree (alias)
"pannotation_alias_cf" btree (lower(alias))
"pannotation_alias_cf_tpo" btree (lower(alias) text_pattern_ops)
"pannotation_alias_tpo" btree (alias text_pattern_ops)
...

where those indexes are defined as:
rkh(at)csb-dev=> \x
rkh(at)csb-dev=> select indexname,indexdef from pg_indexes
where indexname~'^pannotation_alias';
-[ RECORD 1 ]--------------------------------------------------------
indexname | pannotation_alias_cf_tpo
indexdef | CREATE INDEX pannotation_alias_cf_tpo ON pannotation USING btree (lower(alias) text_pattern_ops)
-[ RECORD 2 ]---------------------------------------------------------
indexname | pannotation_alias_tpo
indexdef | CREATE INDEX pannotation_alias_tpo ON pannotation USING btree (alias text_pattern_ops)
-[ RECORD 3 ]---------------------------------------------------------
indexname | pannotation_alias
indexdef | CREATE INDEX pannotation_alias ON pannotation USING btree (alias)
-[ RECORD 4 ]---------------------------------------------------------
indexname | pannotation_alias_cf
indexdef | CREATE INDEX pannotation_alias_cf ON pannotation USING btree
(lower(alias))

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2009-03-13 00:23:05 Re: Fwd: Question about Privileges
Previous Message Eric Soroos 2009-03-13 00:12:47 Re: pg_standby error - can't find 00000001.history