Re: Huge table searching optimization

Thanks to all, now it is 0.061 ms :)

2010/4/5 Tom Lane <tgl@sss.pgh.pa.us>:
> Andres Freund <andres@anarazel.de> 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
>