I am using pg_search gem to my app for search function. Before adding pg_search, I already added 130,000 rows of data to a table in my Postgres database. Now when I run a search it takes too long i.e around 16000ms.
I am following Railscasts Episode343 Full-Text Search in PostgreSQL
Here is the code in my model for pg_search:
include PgSearch
pg_search_scope :search, :against => [:applicant, :generic_name, :trade_name, :description],
using: {tsearch: {dictionary: "english"}},
ignoring: :accents
def self.text_search(query)
if query.present?
rank = <<-RANK
ts_rank(to_tsvector(generic_name), plainto_tsquery(#{sanitize(query)})) +
ts_rank(to_tsvector(trade_name), plainto_tsquery(#{sanitize(query)}))+
ts_rank(to_tsvector(description), plainto_tsquery(#{sanitize(query)})) +
ts_rank(to_tsvector(applicant), plainto_tsquery(#{sanitize(query)}))
RANK
where("generic_name @@ :q or trade_name @@ :q or description @@ :q or applicant @@ :q", q: query)
else
all
end
end
And my server output is as follows:
Parameters: {"utf8"=>"✓", "query"=>"intraocular lenses"}
Parameters: {"utf8"=>"✓", "query"=>"intraocular lenses"}
Rendered layouts/_search.html.erb (1.5ms)
Rendered layouts/_search.html.erb (1.5ms)
Rendered medicaldevices/index.html.erb within layouts/application (16535.9ms)
Rendered medicaldevices/index.html.erb within layouts/application (16535.9ms)
Rendered layouts/_header.html.erb (1.8ms)
Rendered layouts/_header.html.erb (1.8ms)
Rendered layouts/_footer.html.erb (0.1ms)
Rendered layouts/_footer.html.erb (0.1ms)
Completed 200 OK in 16574ms (Views: 60.3ms | ActiveRecord: 16510.7ms)
Completed 200 OK in 16574ms (Views: 60.3ms | ActiveRecord: 16510.7ms)
Here is my migration file for indexing
class AddSearchIndexToMedicaldevices < ActiveRecord::Migration
def up
execute "create index generic_name on medicaldevices using gin(to_tsvector('english', generic_name))"
execute "create index trade_name on medicaldevices using gin(to_tsvector('english', trade_name))"
execute "create index description on medicaldevices using gin(to_tsvector('english', description))"
execute "create index applicant on medicaldevices using gin(to_tsvector('english', applicant))"
end
def down
execute "drop index generic_name"
execute "drop index trade_name"
execute "drop index description"
execute "drop index applicant"
end
end
Here is your answer, I think (http://www.postgresql.org/docs/8.3/static/textsearch-tables.html)
12.2.2. Creating Indexes
We can create a GIN index (Section 12.9) to speed up text searches:
CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', body));
Notice that the 2-argument version of to_tsvector is used. Only text search functions that specify a configuration name can be used in expression indexes (Section 11.7). This is because the index contents must be unaffected by default_text_search_config. If they were affected, the index contents might be inconsistent because different entries could contain tsvectors that were created with different text search configurations, and there would be no way to guess which was which. It would be impossible to dump and restore such an index correctly.
Because the two-argument version of to_tsvector was used in the index above, only a query reference that uses the 2-argument version of to_tsvector with the same configuration name will use that index. That is, WHERE to_tsvector('english', body) @@ 'a & b' can use the index, but WHERE to_tsvector(body) @@ 'a & b' cannot. This ensures that an index will be used only with the same configuration used to create the index entries.