Is 4.5 sec reasonable for a ILIKE request with 1M items

Question

Rails: 3.2.11 Ruby: 1.8.7 Database: Postgres

I have a custom made search engine that uses ILIKE for a search among 1 million entries like this:

formatted_query_string = '%this%search%string'
like_variable = "ILIKE"

product_feed_query_line = [
  "name " + like_variable, formatted_query_string, " OR", 
  "description " + like_variable, formatted_query_string, " OR", 
  "category = ", formatted_query_string
    ].join(" ")

@product_feed_products = FeededProduct.where(product_feed_query_line).where(:is_available => true).paginate(:page => params[:page], :per_page => 18)

The database has indexes according to schema.rb:

  add_index "feeded_products", ["name", "description", "category"], :name => "name"

This takes about 4.5 sec to process in the database, which obviously is a lot. But is it unreasonably long according to the dataset (1M)?

I know that entirely changing to a different search engine would be a good idea for the future but until I can take the time to learn this I need to know if there is something I should do in order to speed this up, or if this type of request just is this slow.

A query using `LIKE` as you have shown will do a table scan over the whole table as it won't use an index. So yes, the time does seem somewhat reasonable. You might want to look into the full text search capabilities to speed this up. — , Apr 22 '13 at 08:02
You might also want to look at trigrams: http://www.postgresql.org/docs/current/static/pgtrgm.html — Jakub Kania, Apr 22 '13 at 11:33
The table had an index but still was this slow. I hired a consultant to create a Postgres-search solution for me, after Jakob's suggestion, and it solved it. — Christoffer, Apr 25 '13 at 03:19
There are a few possibilities to allow an index to be used. If you really start with a '%' and end in a fixed string, you can simply reverse the order on both, and create an index for the reversed string, and the index can be used. — Chris Travers, Apr 25 '13 at 15:13

score 0 · Answer 1 · answered May 24 '13 at 14:21

I know you say you got this fixed by hiring a consultant, but for posterity I figure it is probably worth giving an answer here.

SELECT * FROM foo WHERE bar like '%search%'

is not going to be able to use an index by default so you have a number of options.

Re-evaluate your search patterns. How many "contains" searches can you reasonably convert to "begins with?"
If your search involves begins with and ends with, you can convert to two begins with searches (one on a reversed value), and index the reversed value too.
You can also use the pg_trgm and GIN indexes, but these are not that selective and generate a number of false positives so this is where to go when you have taken the other methods as far as you can. For example "search" overlaps, trigram-wise, "sea urchin arc"

Is 4.5 sec reasonable for a ILIKE request with 1M items

1 Answers1