PostgreSQL/Rails场景下跨多列前后通配符LIKE慢查询如何优化？

DevOps 小满

2026-9-29

你的场景下trigram索引是最优解，to_tsvector全文搜索不适用

方案选型说明

首先排除to_tsvector全文搜索方案：PostgreSQL内置全文搜索基于分词和词干提取，仅能匹配分词后的完整词汇/词干，无法支持任意子串的模糊匹配，完全不符合你的搜索需求。
trigram索引完全适配你的场景：pg_trgm扩展会将字符串拆解为连续3个字符的三元组进行索引，前后带%的LIKE/ILIKE查询都可以命中该类索引，刚好满足任意子串匹配的需求。

具体实现步骤（适配Ruby on Rails + PostgreSQL 13）

1. 启用pg_trgm扩展

生成迁移文件执行扩展启用：

# db/migrate/xxxxxx_enable_pg_trgm.rb
class EnablePgTrgm < ActiveRecord::Migration[6.1]
  def change
    enable_extension 'pg_trgm'
  end
end

2. 创建优化索引

因为你的查询默认是限定单个用户的商品，推荐把user_id作为索引前缀，先缩小扫描范围再做模糊匹配，效率提升最明显：

单字段搜索（仅搜name）

add_index :products, [:user_id, :name], using: :gin, opclass: { name: :gin_trgm_ops }

多字段联合搜索（同时匹配name/barcode/sku）

直接基于三个字段的拼接表达式建索引：

add_index :products, 
  [:user_id, "lower(name) || ' ' || lower(barcode) || ' ' || lower(sku)"], 
  name: 'index_products_on_user_id_search_fields',
  using: :gin,
  opclass: { "lower(name) || ' ' || lower(barcode) || ' ' || lower(sku)" => :gin_trgm_ops }

3. 调整查询写法

确保查询逻辑和索引表达式对齐即可命中索引：

# 多字段搜索示例
search_text = params[:search].to_s.downcase
@products = current_user.products.where(
  "lower(name) || ' ' || lower(barcode) || ' ' || lower(sku) LIKE ?",
  "%#{search_text}%"
)