From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
Cc: pgsql-performance@postgresql.org, Oliver Kindernay <oliver.kindernay@gmail.com>
Subject: Re: Huge table searching optimization
Date: Mon, 05 Apr 2010 12:22:58 -0400
Message-ID: <23156.1270484578@sss.pgh.pa.us> (raw)
In-Reply-To: <201004051810.57673.andres@anarazel.de>
References: <w2y53553b7f1004050728j90050ad3wc9fa4f6c958c3263@mail.gmail.com>
<201004051810.57673.andres@anarazel.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, andres@anarazel.de, oliver.kindernay@gmail.com
Subject: Re: Huge table searching optimization
In-Reply-To: <23156.1270484578@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox