ILIKE '%term%' and Performance

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