Create an index which includes has_many associations

Question

I use Ransack to search through a Organization model and a couple of has_many models (most are polymorphic).

app/models/organization.rb

class Organization < ApplicationRecord
  has_many :phone_numbers, as: :callable, dependent: :destroy
  has_many :addresses, as: :addressable, dependent: :destroy
  has_many :memberships, dependent: :destroy
  has_many :members, through: :memberships, source: :person 

  ransack_alias :ransack_attributes, :name_or_
                                     old_id_or_addresses_line1_or_
                                     addresses_street_or_addresses_zip_code_or_
                                     addresses_city_or_phone_numbers_value_or_
                                     members_first_name_or_
                                     members_last_name_and_slug_and_old_id
  [...]

The used migrations:

create_table :organizations do |t|
  t.string :name
  t.string :description
end
add_index :organizations, :name

create_table :addresses do |t|
  t.string :line1
  t.string :line2
  t.string :street
  t.string :zip_code
  t.string :city
  t.references :address_type, foreign_key: true
  t.references :addressable, polymorphic: true
end

create_table :phone_numbers do |t|
  t.string :value
  t.references :phone_number_type, foreign_key: true
  t.string :description
  t.references :callable, polymorphic: true
  t.integer :position
end

create_table :memberships do |t|
  t.references :organization, foreign_key: true
  t.references :person, foreign_key: true
  t.references :membership_type, foreign_key: true
  t.string :memo
end

create_table :people do |t|
  t.string :first_name
  t.string :middle_name
  t.string :last_name
  t.date :birthday
  t.string :title
  t.string :gender
end 

In a controller I search for the organizations:

@organizations = Organization.ransack(ransack_attributes_cont:params[:q]).
                              result(distinct: true).
                              includes(
                              :addresses
                              ).order(:updated_at).reverse_order

Obviously the searches are slow. So I'm thinking of creating indexes for the requested attributes for each used model.

def change
  add_index(:addresses, [:line1, :street, :zip_code, :city], name: 'ransack_addresses_index')
  add_index(:organizations, [:name, :old_id, :slug], name: 'ransack_organizations_index')
  add_index(:phone_numbers, [:value])
  add_index(:people, [:first_name, :last_name], name: 'ransack_people_index')
end

But I'd like to know if there is even a better way to index this? Can I create a super index over all the used fields or is that impossible because those are polymorphic has_many associations? I'm using PostgreSQL.

Answer 1

There is no super index for the given problem.