Re: text column indexing in UTF-8 database
От | Reece Hart |
---|---|
Тема | Re: text column indexing in UTF-8 database |
Дата | |
Msg-id | 1236913926.22843.103.camel@snafu обсуждение исходный текст |
Ответ на | Re: text column indexing in UTF-8 database (Steve Atkins <steve@blighty.com>) |
Список | pgsql-general |
On Thu, 2009-03-12 at 17:32 -0700, Steve Atkins wrote:
Good idea. Thanks. The niggling remaining problem is that the DB is open to a SQL-savvy audience and it'd be nice to avoid telling them to casefold their predicates.
For regexps, lower(alias) ~* lower(regexp) won't work because extended regexps might contain character classes (e.g., \S != \s). And, I guess that alias ~* regexp requires a seqscan because the index isn't ordered over ~* (right?). How about lower(alias) ~* regexp ? Is PG smart enough to know that that ordering is well defined? Is my head on straight there?
Thanks again,
Reece
If A=B then lower(A) = lower(B), and if A like B then lower(A) like
lower(B).
So, if nothing else, you could rewrite "where alias = 'Foo'" as
"where lower(alias) = lower('Foo') and alias='Foo'" and take advantage
of the lower() functional index.
Good idea. Thanks. The niggling remaining problem is that the DB is open to a SQL-savvy audience and it'd be nice to avoid telling them to casefold their predicates.
For regexps, lower(alias) ~* lower(regexp) won't work because extended regexps might contain character classes (e.g., \S != \s). And, I guess that alias ~* regexp requires a seqscan because the index isn't ordered over ~* (right?). How about lower(alias) ~* regexp ? Is PG smart enough to know that that ordering is well defined? Is my head on straight there?
Thanks again,
Reece
-- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 |
В списке pgsql-general по дате отправления: