Searching and performance

[csn]

Is there a "best way" to enable keyword searches? I'm thinking of using:

select * from table where title ilike '%keyword%';

But this way an index wouldn't be used ('keyword%' would). There are thousands of rows. Worth worrying about performance?

[Sxooter]

For thousands of rows, it's no big deal for pgsql to handle such a query.

However, as you approach 10,000, then 100,000, then 1,000,000 rows, it is a big deal.

At that point, you should look at tsearch2 in teh contrib directory. It quite literally flies. It's every bit a match for the built in full text search engine in MySQL.