ziemekwolski's Profile

Comments by

ziemekwolski on #343 Full-Text Search in PostgreSQL (pro) 2012-09-25 12:43:12

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"