Casecommons/pg_search — Issue #15: How to add indexes
Author: tcocca
Created: 2011-10-13 01:49:15+00:00
State: open

So I have a model, Property, and I want to use a simple search_scope:

class Property < ActiveRecord::Base
include PgSearch
pg_search_scope :search_by_keywords, :against => :description
end

I opened script/console and did a simple: Property.search_by_keywords('test') and it generated the following query:

SELECT "properties".*, (ts_rank((to_tsvector(E'simple', coalesce("properties"."description", ''))), (to_tsquery(E'simple', E''' ' || E'test' || E' ''')))) AS pg_search_rank FROM "properties" WHERE (((to_tsvector(E'simple', coalesce("properties"."description", ''))) @@ (to_tsquery(E'simple', E''' ' || E'test' || E' ''')))) ORDER BY pg_search_rank DESC, "properties"."id" ASC

How would I go about adding a fulltext index for this search scope so that the query is fast.

Keep in mind eventually I will want to do something like:

Property.search_by_keywords('town house').paginate(:conditions => {:state => 'MA', :price => (0..250000)}, :order => "price asc", :page => 1, :per_page => 25)

Or you get the point, we are going to be combining the search_scope for keywords with some other complex filtering. Any ideas on what the best index syntax is to use to add the index on the fulltext for description?

~ Tom