Searching multiple tables in Postgres and Rails

Question

I use PostgreSQL's full text search capabilities, which works fine. All the relevant columns are indexed so it's nice and speedy:

  def self.text_search(query)
    if (query.present?)
      # search(query)
      where(
          "to_tsvector('english', title) @@ plainto_tsquery(:q)",
          q: query
      )
    else
      scoped
    end
  end

But now I also want to search through related abbreviations:

def self.text_search(query)
    if (query.present?)
      # search(query)
      includes(:abbreviations).where(
          "to_tsvector('english', articles.title) @@ plainto_tsquery(:q)"+
          " or to_tsvector('english', abbreviations.abbreviation) @@ plainto_tsquery(:q)",
          q: query
      )
    else
      scoped
    end
  end

This works, but now my queries take 2.5+ seconds! How do I remedy this? I was thinking that maybe this is a Rails inefficiency, so I could best perform raw SQL. But how do I do that and still get back a ActiveRecord relation?

realDonaldDuck · Accepted Answer · 2013-08-06 20:29:33Z

1

What I did as a workaround, added a str_* column to my main table, and update this column when an element is saved, and then search over that column:

  before_validation(on: :create) do
    self.str_abbreviations = join_abbreviations()
    ... etc ...
    true
  end

answered Aug 6, 2013 at 20:29

realDonaldDuck

7,3897 gold badges58 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Searching multiple tables in Postgres and Rails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related