From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Antony Paul <antonypaul24@gmail.com>
Cc: Russell Smith <mr-russ@pws.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to boost performance of ilike queries ?
Date: Tue, 25 Jan 2005 06:01:25 -0800 (PST)
Message-ID: <20050125055555.J11415@megazone.bigpanda.com> (raw)
In-Reply-To: <2989532e05012500235c78fd47@mail.gmail.com>
References: <2989532e05012401184b0c3ef9@mail.gmail.com>
<200501242058.54670.mr-russ@pws.com.au>
<2989532e05012500235c78fd47@mail.gmail.com>
On Tue, 25 Jan 2005, Antony Paul wrote:
> Creating an index and using lower(column) does not change the explain
> plan estimates.
> It seems that it is not using index for like or ilike queries
> irrespective of whether it have a pattern matching character in it or
> not. (using PostgreSQL 7.3.3)
I believe in 7.3.x an index is only considered for like in "C" locale, I
think the *_pattern_op opclasses were added in 7.4 for which you can make
indexes that are considered for non wildcard starting search strings in
non "C" locales. And it may have trouble doing estimates before 8.0 on the
functional index because of lack of statistics. You may want to consider
an upgrade once 8.0 shakes out a bit.
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: sszabo@megazone.bigpanda.com, antonypaul24@gmail.com, mr-russ@pws.com.au
Subject: Re: How to boost performance of ilike queries ?
In-Reply-To: <20050125055555.J11415@megazone.bigpanda.com>
* 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