Rails：提高简单搜索方法的性能 [英] Rails: Improve performance of simple searching method

问题描述

这些天我一直在开发应用程序。该功能一点都不花哨，我必须连接到客户端的SOAP Web服务，获取一些数据，将其保存到我的pg数据库中，然后基于此数据构建搜索功能。

I have been building an app these days. The functionality is nothing fancy at all, I have to connect to a client's SOAP webservice, fetch some data, save it into my pg database and build a search functionality based on this data.

必须在两个表上执行搜索，两个表的组合就像80K行。它需要从这两个表的多个字段中查找输入文本中的每个单词，它们具有一对多的经典关联。

The search has to be performed on two tables, both combined are like 80K rows. It needs to look for every word in the input text in several fields from these two tables, which have a classical assocation one to many.

以前为了弄清我的手，我一直在考虑必须完成功能的选择（Rackack，searchkick，scoped_search等），但最后我还是尝试了Vanilla Active Record，令我感到非常惊讶的是，我可以比我想象的更容易实现功能，并且响应时间可以接受，对于本地最昂贵的查询，活动记录时间约为400ms。

Previous to get my hands dirty I was looking at the choices I had to get the functionality done (ransack, searchkick, scoped_search etc), but I ended up trying first just vanilla Active Record and I was very surprised to find that I could achieve the functionality way easier than I thought and with an acceptable response time, about to 400ms active record time for the most expensive queries in local.

所以问题是，此应用程序在Heroku中的性能要比本地应用程序差（我正在使用无聊的盒子btw开发）。平均而言，查询花费的时间是本地查询时间的2-3倍，因此用户体验从可接受的变为糟糕的。我想知道是否有人可以帮助改善我的查询。我还担心获取数据的后台作业是否会比本地作业表现差，并担心内存问题，但这是另一回事。

So the problem is, the performance of this app in Heroku is way worse than in local (I'm developing using a vagrant box btw). On average, queries take 2-3 times longer than in local, so the user experience goes from acceptable to poor. I was wondering If someone could help to improve my query. I'm also worried about how the background job that fetchs the data is also way les performant than in local and about some issues with the memory, but that's a different story though.

相关片段如下：

part_master.rb 其中实现了搜索方法：

part_master.rb where the search method is implemented:

class PartMaster < ApplicationRecord
has_many :part_variants, foreign_key: 'sap_cod', primary_key: 'sap_cod'
has_many :locations, foreign_key: 'sap_cod', primary_key: 'sap_cod'

scope :con_stock, -> { where("stock > 0") }
scope :planta, -> (planta) { where planta_cod: planta}

def self.search(params)
  recordset = PartMaster.joins(:part_variants).all
  recordset = recordset.con_stock if params[:stock].present?
  recordset = recordset.planta(params[:planta]) if params[:planta].present?
  recordset = search_keywords(params[:search], recordset)
  recordset
end

private 

def self.search_keywords(query, recordset)
  keywords = query.to_s.strip.split
  if query
    keywords.each do |keyword|
      recordset = recordset.where('part_masters.sap_cod ILIKE :q OR 
                          unaccent(descripcion_maestro) ILIKE unaccent(:q)
                          OR fabricante ILIKE :q OR ref_fabricante ILIKE :q 
                          OR fabricante_prov ILIKE :q OR ref_prov ILIKE :q', 
                          q: "%#{keyword}%")
    end
    recordset.distinct.order(:sap_cod)
   end
 end
end

这是控制器对方法的调用：

And this is the call to the method from the controller:

  def index
   parts = params[:search].present? ? PartMaster.search(params) : 
           PartMaster.none
   @parts = parts.page(params[:page]).per(50)
  end

我在每个可搜索字段中都有一个索引。

I have an index in every searchable field.

编辑：最后，我尝试了提案中包含了答案。我在每个表中创建了一个字段，该字段是用于搜索的相关字段的串联，具有2个OR语句而不是5个语句，并且我还将trigram GIN索引放在了两个新字段中。不过，我还没有看到任何改善，与ActiveRecord对应的时间非常相似，也许略有改善。

Finally I have tried a mix of the proposal in the answers. I have created one field in each table that is a concatenation of the relevant fields for the search, having so 2 OR statements instead of 5, and I also have put trigram GIN indexes in both new fields. I haven't seen any improvement though, the times corresponding to ActiveRecord are very similar, perhaps marginally better.

问题是，使用EXPLAIN查询的输出不会显示有关正在使用的索引的任何信息。

The thing is, the output for the query using EXPLAIN dones't show any info about the indexes being used.

Hash Join  (cost=2243.29..6067.41 rows=2697 width=132)
Hash Cond: ((part_variants.sap_cod)::text = (part_masters.sap_cod)::text)
Join Filter: ((part_masters.combinada_maestro ~~* '%rodamiento%'::text)         OR (part_variants.combinada_info ~~* '%rodamiento%'::text))
->  Seq Scan on part_variants  (cost=0.00..1264.96 rows=54896 width=18)
->  Hash  (cost=1128.13..1128.13 rows=34813 width=132)
     ->  Seq Scan on part_masters  (cost=0.00..1128.13 rows=34813             width=132)
(6 rows)

推荐答案

我同意Spikermann。循环中的多个OR也无济于事。

I agree with spikermann. Also the multiple ORs in a loop is not helping neither.

如果您只想使用原始解决方案而不是添加SOLR或任何其他引擎，则可以单独使用一个字段来保存您要搜索的字符串的副本。 （例如名称，说明等）。仅搜索此字段。当名称，说明或其他值更改时，您将需要某种方法来使字段保持更新。

If you only want to work on a vanilla solution vs adding SOLR or any other engine, you could have one separate field to hold copies of the strings that you would like to search. (ex. name, description, ...). The search this field only. You will need some method to keep the field updated when the name, description or other values change.

这篇关于Rails：提高简单搜索方法的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！