Efficient use of PostgreSQL full text search with highlighting using pg_search

For some time I was struggling with efficient of ts_highligh function of postgres using Rails and pg_search. I had bunch of really long articles and I wanted to highlight search results in the content as well in the title with the power of tsquery and polish dictionary that we feed our database.

In the end solutions was quite simple really, maybe not the most elegant one but does the job just right and in the end your will end up still with ActiveRecord::Relation, so you can easily paginate your results with will_paginate or kaminari gem.

So, let’s break it down.

First - I was using custom polish dictionary, and I already had a trigger in database that recreated my tsvector column every update/insert. So in that case you can declare PgSearch scope like that:

pg_search_scope :search_full_text,
 :against => :body,
 :using  => { :tsearch =>
              {
                :dictionary       => "polish",
                :tsvector_column  => 'fts',
                :prefix           => true
              }
            }

And basically that works great, except when your try to select ts_highligh with can be painfully slow. So what I did there:

• first I monkey patched pg_search for my application - I didn’t wanted to select all the columns, just few ones. So in pg_search/lib/pg_search/scope_options.rb I customized apply method

• then I just used select from select in a simple manner, take a look at example below

  def self.search(search_query, page)
  body = "ts_headline('polish', body, plainto_tsquery('polish','#{search_query}'), 'startsel=''<em class=\"headline\">'' stopsel=</em>, MaxFragments=2, maxwords=15, minwords=5') as body"
  self.select[:id, body].from("(#{self.search_full_text(search_query).paginate(:page => page).to_sql}) as result")
  end

As you see I’m selecting id column and my highlighted body from paginated results (using will_paginate here). In the view you can display pagination over search_full_text(search_query).paginate(:page => page). Refactor it as you please. Right now it seems like the best solution I could think of - if you have better idea let me know!