Re: How to boost performance of queries containing pattern matching characters

Gnanakumar wrote:
> Thanks for your suggestion.  Our production server is currently running
> PostgreSQL v8.2.3.  I think pg_trgm contrib module is not available for 8.2
> series.
>

You're going to find that most of the useful answers here will not work
on 8.2.  Full-text search was not fully integrated into the database
until 8.3.  Trying to run an app using it on 8.2 is going to be a
constant headache for you.  Also, moving from 8.2 to 8.3 is just a
general performance boost in many ways.


> Also, I read about WildSpeed - fast wildcard search for LIKE operator.  What
> is your opinion on that?
> http://www.sai.msu.su/~megera/wiki/wildspeed
>

WildSpeed works fine if you can handle the massive disk space and
maintenance overhead it introduces.  I consider it useful only for data
sets that are almost static, where you can afford to build its large
index structure once and then use it to accelerate reads continuously,
with minimal updates.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books