0

I have a Rails application where I'm currently using PostgreSQL as my database. In one of my methods, I have utilized the "USE INDEX" feature in MySQL to specify a specific index to be used in the query for optimizing performance. However, I have come to understand that PostgreSQL doesn't support this exact syntax.

Here is an example of the code I'm referring to:

def travels
  q = @company.travel_charges
               .from('travel_charges USE INDEX(index_company_spend_breakdown)')
               .not_deleted
               .year_weeks(filter_year_week_range)
               .includes(:traveler_type, :service_type_description, :status_message,
                         :address_to, :address_from, :refund, :service_type_group,
                         taggings: :tag)
               .ransack(params[:q])

  q.result.order(travel_date: :desc).page(params[:page]).per(params[:page_per])
end

I would like to achieve a similar behavior in PostgreSQL, where I can optimize the query by specifying the use of a specific index. How can I achieve this in PostgreSQL?

Any guidance, alternative approaches, or recommended practices would be highly appreciated. Thank you in advance for your assistance!

Andres23Ramirez
  • 647
  • 1
  • 4
  • 14
  • 2
    This is not possible in PG. The server will automagically use the best index that is available for the table. No action necessary on the application side. – Patrick Jun 07 '23 at 18:11
  • 1
    When tackling performance issues, start with the query plan. Always use explain (analyze, verbose, buffers) for your statements to see what’s going on. Without this information it’s almost impossible to optimize for performance. – Frank Heikens Jun 08 '23 at 04:56

0 Answers0