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?
class Address < ApplicationRecord
include PgSearch::Model
pg_search_scope :search_for, against: %i[address_line_1 address_line_2], using: %i[tsearch trigram]
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
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]