Thread: ILIKE '%term%' and Performance

I'm thinking of enabling searches that use queries like "select * from items where title ilike
'%term%'". The items table has tens of thousands of rows. Is it worth worrying about the
performance of such a query (since, if I'm not mistaken, it will never use indices). If it is,
what's the best option - use tsearch? How does tsearch (or whatever else) compare performance-wise
to not using it, or to typical index-based queries for that matter?

thanks
csn



__________________________________
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs

Hi!

CSN wrote:
> I'm thinking of enabling searches that use queries like "select *
> from items where title ilike '%term%'". The items table has tens
> of thousands of rows. Is it worth worrying about the performance
> of such a query (since, if I'm not mistaken, it will never use
> indices). If it is, what's the best option - use tsearch? How
> does tsearch (or whatever else) compare performance-wise to not
> using it, or to typical index-based queries for that matter?

This is my first post to this list and I'm quite a PostgreSQL
newbie. But I had the same question as you a few days ago so maybe
my answer is helpful to you.

In my experience, there was a huge difference between using tsearch2
and normal ILIKE. This is the query time on a table with about
236000 publication records when searching for a title:

1. Without tsearch2:
   SELECT * FROM publications WHERE title ILIKE '%quicksort%';
   Total runtime: 1673.439 ms

2. With tsearch2:
   SELECT * FROM publications
   WHERE idx_fti @@ to_tsquery('default', 'quicksort');
   Total runtime: 11.707 ms

So for my setup, tsearch2 was (as I expected) much faster, but of
course results will vary between different setups. But maybe it is
helpful to you.

Greetings,

- Stephan