From: | Oliver Kindernay <oliver(dot)kindernay(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Andres Freund <andres(at)anarazel(dot)de>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Huge table searching optimization |
Date: | 2010-04-05 17:11:52 |
Message-ID: | r2o53553b7f1004051011za5735fb2x970a5def1a35a815@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Thanks to all, now it is 0.061 ms :)
2010/4/5 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Andres Freund <andres(at)anarazel(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Cox | 2010-04-06 01:01:16 | query slow; strace output worrisome |
Previous Message | Robert Haas | 2010-04-05 16:54:29 | Re: How to fast the REINDEX |