Rails 7.1: How to efficiently filter records based on a multi-level association with ActiveRecord?
I'm optimizing some code but I'm trying to filter a list of `Order` records based on associated `User` and `Profile` attributes using ActiveRecord, but I'm running into performance optimization. I have the following associations: ```ruby class User < ApplicationRecord has_many :orders has_one :profile end class Profile < ApplicationRecord belongs_to :user end class Order < ApplicationRecord belongs_to :user end ``` In my controller, I want to filter orders where the user's profile has a specific attribute, let's say `location`, set to 'New York'. Here's what I attempted: ```ruby @orders = Order.joins(user: :profile).where(profiles: { location: 'New York' }) ``` This seems to work in terms of getting the correct records, but the query takes forever to run when the dataset grows. I also tried adding an index on the `location` column in the profiles table: ```ruby add_index :profiles, :location ``` However, the performance improvement is minimal. Additionally, I noticed the query execution plan shows a lot of table scans. Here's the output when I run `EXPLAIN`: ``` Seq Scan on profiles (cost=0.00..30.00 rows=3000 width=32) ``` Is there a more efficient way to perform this query? I want to ensure I'm adhering to best practices without compromising the performance, especially as the number of users and orders grows. Any insights into optimizing this would be greatly appreciated! What's the best practice here?