Pesquisa de substring do Postgres por índice

Em um projeto recente, tive que criar um recurso de sugestão automática que poderia olhar para 1,5 milhão de linhas e sugerir com base em sub-strings. Procurei uma solução baseada em redis, uma solução customizada e um mecanismo como o Solr. Eu realmente não gostei da complexidade extra que essas soluções criaram, então comecei a estudar como fazer isso no postgres. A solução que encontrei é muito simples e funciona incrivelmente rápido, <5ms.

Ele usa índices e correspondência de sub-strings para fazer isso e levou apenas alguns segundos para configurar.

Por ser um projeto do rails 3, a primeira coisa a fazer foi criar a migração para criar os índices:

class AddSubstringIndexes < ActiveRecord::Migration
  def up
    30.times {|i| execute "CREATE INDEX charity_name_substring_#{i+1} ON charities USING gin(to_tsvector('english', substring(name from 1 for #{i+1})));"}
  end

  def down
    30.times {|i| execute "DROP INDEX charity_name_substring_#{i+1};"}
  end
end

A migração criará um matcher de substring que funcionará com até 30 caracteres de comprimento. Seria possível adicionar mais funções de correspondência linguística a isso, como fuzzystrmatch e soundex, mas isso não foi necessário aqui.

Agora que configuramos nossos índices, podemos começar a consultar. Adicionar um método de classe de sugestão ao nosso modelo facilita o uso em todo o nosso aplicativo.

def self.suggest(query, options = {})
      options.reverse_merge!({limit: 50})
      rank = <<-RANK
        ts_rank(to_tsvector(name), plainto_tsquery(#{sanitize(query)}))
      RANK
      ActiveRecord::Base.connection.select_all("SELECT id, name, mailing_address FROM charities WHERE to_tsvector('english', substring(name from 1 for #{query.length})) @@ plainto_tsquery('english', #{sanitize(query)}) ORDER BY #{rank} DESC LIMIT #{options[:limit]}")
end

Queremos a consulta mais rápida possível, então vamos ignorar o registro ativo e ir para uma instrução SQL direta.

O método acima usa um argumento limit e usa reverse_merge para criar um padrão.
Em seguida, criamos um método de classificação para o postgres usar.

Finalmente, executamos a consulta.