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@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@csb-dev=> \x
rkh@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
