Improve the Ruby on Rails query time by updating composite indexing

composite_index_query_optimisation.rb

#Author: Shubham Mishra
#Table 
Approval: assignee_id, deleted_at, is_visible, status, ...

#Query:
result = ActiveRecord::Base.connection.execute('
  EXPLAIN ANALYZE
  SELECT "approvals"."request_type"
  FROM "approvals"
  WHERE "approvals"."deleted_at" IS NULL
    AND "approvals"."assignee_id" = 282794
    AND "approvals"."is_visible" = false
    AND "approvals"."status" = \'Pending\'
')

#Current index:
index_approvals_on_assignee_id_with_delete_null on approvals
Index Condition: (assignee_id = 282794)

#Query execution time:
Execution Time: 0.507 ms


#Updating the composite index in Rails c (On production server u can try this)
ActiveRecord::Migration.add_index(
  :approvals,
  [:assignee_id, :is_visible, :status],
  name: 'index_approvals_dummy_111',
  algorithm: :concurrently,
  where: 'deleted_at IS NULL'
)

#Now run same above query again
result = ActiveRecord::Base.connection.execute('
  EXPLAIN ANALYZE
  SELECT "approvals"."request_type"
  FROM "approvals"
  WHERE "approvals"."deleted_at" IS NULL
    AND "approvals"."assignee_id" = 282794
    AND "approvals"."is_visible" = false
    AND "approvals"."status" = \'Pending\'
')

#WOW i was surprised to see the reduction in query time for one single record selection
#Query execution time: 
Execution Time: 0.039 m

#So Updated composite index:
index_approvals_dummy_111 on approvals
Index Condition: ((assignee_id = 282794) AND (is_visible = false) AND ((status)::text = 'Pending'::text))