Re: Huge table searching optimization

От: Tom Lane
Тема: Re: Huge table searching optimization
Дата: ,
Msg-id: 23156.1270484578@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Huge table searching optimization  (Andres Freund)
Ответы: Re: Huge table searching optimization  (Oliver Kindernay)
Список: pgsql-performance

Скрыть дерево обсуждения

Huge table searching optimization  (Oliver Kindernay, )
 Re: Huge table searching optimization  (hubert depesz lubaczewski, )
 Re: Huge table searching optimization  (Andres Freund, )
  Re: Huge table searching optimization  (Tom Lane, )
   Re: Huge table searching optimization  (Oliver Kindernay, )

Andres Freund <> writes:
> On Monday 05 April 2010 16:28:35 Oliver Kindernay wrote:
>> i am using this request:
>> select url from test2 where url ~* '^URLVALUE\\s*$';

> Depending on your locale it might be sensible to create a text_pattern_ops
> index - see the following link:
> http://www.postgresql.org/docs/current/static/indexes-opclass.html

text_pattern_ops won't help for a case-insensitive search.  The best bet
here would be to index on a case-folded, blank-removed version of the
url, viz

    create index ... on (normalize(url))

    select ... where normalize(url) = normalize('URLVALUE')

where normalize() is a suitably defined function.

Or if it's okay to only store the normalized form of the string,
you could simplify that a bit.

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: Oliver Kindernay
Дата:
Сообщение: Re: Huge table searching optimization
От: Brian Cox
Дата:
Сообщение: query slow; strace output worrisome