Sign in through GitHub

Please read for an updated status on RailsCasts:

Learn more or hide this

ziemekwolski's Profile

GitHub User: ziemekwolski

Comments by

Avatar

Hey Ryan,

Thanks for the great screencast! The solution that I found to your problem with slow rank or weighted search on multiple columns is to create a column to actually use for searching:

Example:

ruby
class AddIndexForFullTextSearch < ActiveRecord::Migration
  def up
    execute "ALTER TABLE users ADD COLUMN tsv tsvector"
    execute <<-QUERY
    UPDATE users SET tsv = (to_tsvector('english', coalesce("building"."first_name"::text, '')) || 
                            to_tsvector('english', coalesce("building"."last_name"::text, '')));
    QUERY
    
    execute "CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
              ON users FOR EACH ROW EXECUTE PROCEDURE
              tsvector_update_trigger(tsv, 'pg_catalog.english', first_name, last_name);"
  end
  def down
    execute "drop trigger tsvectorupdate on users"
    execute "alter table users drop column tsv"
  end
ruby
class User < ActiveRecord::Base
  include PgSearch
  pg_search_scope :search, against: [:first_name, :last_name], 
    using: {
      tsearch: {
        dictionary: "english",
        tsvector_column: 'tsv'
      }
    }
end

I've tested this solution with 200K records and it makes a significant difference.
Solution was found on "http://linuxgazette.net/164/sephton.html"