Index jsonb column keys using GIN and pg_trgm, for ILIKE queries in Rails

Question

I have a table "Leads" with the following structure :

# == 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

The user_details jsonb column stores data in the form- {name : "John Doe", country : "IN", phone_no : "123456789"}. I want to query my database columns using ILIKE for the name key as :

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

To achieve this, I created a migration as shown:

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

This creates the necessary index. I have already enabled the pg_trgm extension in a previous migration. My structure.sql looks like :

Also, the corresponding schema.rb adds the following line for leads table -

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

However, when I try to query my database, it does a sequential scan.

On the other hand,if I create a gin index for the entire user_details column and then query using "@> {name: "john"}.to_json" it uses index for scan

My Rails version is 5.2.0 and PostgreSQL version is 12.5. How can I use ILIKE queries for this use case? Where am I going wrong? I'll be happy to provide more details if necessary.

Answer 1

An alternative is to tell your index to already sort the values using either upper or lower case, so that you can simply use LIKE in your queries.

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

When querying this jsonb key you have to use the same function. Doing so the query planer will find your partial index:

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

Demo: db<>fiddle

Answer 2

You table is probably just too small for an index scan to seem worthwhile. It looks like it only has 269 rows in it. You can set enable_seqscan=off to see if it uses the index then. Or you can just add a realistic number of rows to the table (and then VACUUM ANALYZE it)