From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Andres Freund <andres(at)anarazel(dot)de> |
Cc: | pgsql-performance(at)postgresql(dot)org, Oliver Kindernay <oliver(dot)kindernay(at)gmail(dot)com> |
Subject: | Re: Huge table searching optimization |
Date: | 2010-04-05 16:22:58 |
Message-ID: | 23156.1270484578@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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 | Robert Haas | 2010-04-05 16:54:29 | Re: How to fast the REINDEX |
Previous Message | Andres Freund | 2010-04-05 16:10:57 | Re: Huge table searching optimization |