Skip site navigation (1) Skip section navigation (2)

Re: How to boost performance of ilike queries ?

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Antony Paul <antonypaul24(at)gmail(dot)com>
Cc: Russell Smith <mr-russ(at)pws(dot)com(dot)au>,pgsql-performance(at)postgresql(dot)org
Subject: Re: How to boost performance of ilike queries ?
Date: 2005-01-25 14:01:25
Message-ID: 20050125055555.J11415@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.


In response to

pgsql-performance by date

Next:From: Tatsuo IshiiDate: 2005-01-25 14:19:17
Subject: Re: PostgreSQL clustering VS MySQL clustering
Previous:From: Antony PaulDate: 2005-01-25 13:01:45
Subject: PostgreSQL not utilising available memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group