使用GIN和pg_trgm对jsonb列键进行索引，在Rails中执行ILIKE查询。

Question

我有一个名为“Leads”的表，其结构如下：

# == Schema Information
#
# Table name: leads
#
#  id                       :integer          not null, primary key
#  data                     :jsonb            not null
#  state                    :string
#  priority                 :string
#  lead_no                  :string
#  user_id                  :integer
#  location_place_id        :string
#  uuid                     :string
#  agent_id                 :integer
#  location_coordinates     :geography        point, 4326
#  location                 :jsonb            not null
#  user_details             :jsonb            not null
#  inventory_id             :integer
#  source_details           :jsonb            not null
#  connect_details          :jsonb            not null
#  referral_details         :jsonb            not null
#  process_details          :jsonb            not null
#  tags                     :jsonb            not null
#  created_at               :datetime
#  updated_at               :datetime
#  name                     :string

user_details jsonb列以{name : "John Doe", country : "IN", phone_no : "123456789"}的形式存储数据。 我想使用ILIKE针对名称键查询我的数据库列：

Lead.where("user_details->>name ILIKE ?","john%")

为了实现这一点，我创建了如下的迁移：

class AddIndexUserNameOnLeads < ActiveRecord::Migration[5.2]
  def up
      execute("CREATE INDEX leads_user_details_name_idx ON leads USING gin((user_details->>'name') gin_trgm_ops)")
  end

  def down
    execute("DROP INDEX leads_user_details_name_idx")
  end
end

这将创建所需的索引。我已经在之前的迁移中启用了pg_trgm扩展。我的structure.sql看起来像： 此外，相应的schema.rb会为leads表添加以下行 -

t.index "((user_details ->> 'name'::text)) gin_trgm_ops", name: "leads_user_details_name_idx", using: :gin

然而，当我尝试查询我的数据库时，它执行了一个顺序扫描。

另一方面，如果我为整个"user_details"列创建一个gin索引，然后使用"@> {name: "john"}.to_json"进行查询，它将使用索引进行扫描。

我的Rails版本是5.2.0，PostgreSQL版本是12.5。如何在这种情况下使用ILIKE查询？我做错了什么？如果需要，我很乐意提供更多详细信息。

- the_namikaze

---

不要将文本以文本图像的形式发布。请以纯文本形式发布文本。 - jjanes

我一定会记住下次的。非常感谢你的回答！ - the_namikaze

Answer 1

另一种方法是让您的索引已经使用大写或小写对值进行排序，这样您就可以在查询中简单地使用LIKE。

CREATE INDEX leads_user_details_name_idx ON leads 
USING gin(lower(user_details->>'name') gin_trgm_ops);

查询此jsonb键时，您必须使用相同的函数。这样做，查询规划器将找到您的部分索引：

SELECT * FROM leads
WHERE lower(user_details->>'name') ~~ '%doe%';

Demo: db<>fiddle

- Jim Jones

Answer 2

你的表可能太小，无法进行索引扫描。看起来它只有269行。您可以使用set enable_seqscan=off来查看它是否使用索引。或者您可以向表中添加适量的行（然后执行VACUUM ANALYZE）。

- jjanes