sqlruby-on-railsrubypostgresqlpg-search

How can I make pg_search faster?


I'm trying to implement pg_search in my rails application. I've got it working but searches are very slow, over 20 seconds. I have over 7 million records in my addresses table. Are there ways I can make it faster?

app/models/address.rb

class Address < ApplicationRecord
  include PgSearch::Model
  pg_search_scope :search_for, against: %i[address_line_1 address_line_2], using: %i[tsearch trigram]

UPDATE

I've added this index but it still seems to be just as slow

class IndexAddressesOnAddressLine1 < ActiveRecord::Migration[6.1]
  # An index can be created concurrently only outside of a transaction.
  disable_ddl_transaction!

  def up
    execute <<~SQL
CREATE INDEX pg_search_addresses_on_fields ON addresses USING gin(coalesce(address_line_1, address_line_2, ''::text) gin_trgm_ops)
SQL
  end

  def down
    execute <<~SQL
DELETE INDEX pg_search_addresses_on_fields
    SQL
  end
end

Solution

  • By default, pg_search uses a threshold of 0.3 for trigram searches. Higher numbers match more strictly and thus return fewer results. Lower numbers match more permissively, letting in more results.

    pg_search_scope :search_for, 
      against: %i[address_line_1 address_line_2], 
      using: {
        tsearch: { dictionary: 'english' },
        trigram: { threshold: 0.5 } # increase the threshold
      }
    

    I'm not sure but maybe you need to reindex your model:

    rake pg_search:multisearch:rebuild[Address]