text column indexing in UTF-8 database
От | Reece Hart |
---|---|
Тема | text column indexing in UTF-8 database |
Дата | |
Msg-id | 1236903305.22843.57.camel@snafu обсуждение исходный текст |
Ответы |
Re: text column indexing in UTF-8 database
Re: text column indexing in UTF-8 database |
Список | 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@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
В списке pgsql-general по дате отправления: