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

user_13662850 0
Viewed 470

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 : enter image description here

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. enter image description here

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 enter image description here

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.

pg-trgm database-indexes postgresql ruby-on-rails
2 Answers

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

user_22753880

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)

user_17212390
Related