Heroku上Rails Admin带过滤查询异常缓慢问题求助

问题现状 #

• 本地与预发布环境执行相同带过滤器的查询速度正常，但服务器配置更优的Heroku生产环境加载Listing索引耗时约40秒
• 预发布/开发环境Listing模型约13.4万条记录，生产环境约15万条，数据量差距不大但性能差异显著
• 从请求日志可见，ActiveRecord耗时占比超过99%，视图渲染耗时可忽略

请求日志 #

2022-09-20T08:48:24.696263+00:00 app[web.1]: I, [2022-09-20T08:48:24.696196 #3]  INFO -- : [76f22ddd-486a-4157-bf5e-8f3b4afa6fca] Started GET "/admin/listing?model_name=listing&utf8=%E2%9C%93&query=Missouri&_pjax=%5Bdata-pjax-container%5D" for 172.70.147.182 at 2022-09-20 08:48:24 +0000
2022-09-20T08:48:24.706653+00:00 app[web.1]: I, [2022-09-20T08:48:24.706580 #3]  INFO -- : [76f22ddd-486a-4157-bf5e-8f3b4afa6fca] Processing by RailsAdmin::MainController#index as HTML
2022-09-20T08:48:24.706693+00:00 app[web.1]: I, [2022-09-20T08:48:24.706659 #3]  INFO -- : [76f22ddd-486a-4157-bf5e-8f3b4afa6fca]   Parameters: {"model_name"=>"listing", "utf8"=>"✓", "query"=>"Missouri", "_pjax"=>"[data-pjax-container]"}
2022-09-20T08:48:25.634623+00:00 app[web.1]: I, [2022-09-20T08:48:25.634511 #3]  INFO -- : [76f22ddd-486a-4157-bf5e-8f3b4afa6fca]   Rendering vendor/bundle/ruby/2.5.0/gems/rails_admin-2.0.2/app/views/rails_admin/main/index.html.haml within layouts/rails_admin/pjax
2022-09-20T08:48:26.626621+00:00 app[web.1]: I, [2022-09-20T08:48:26.626531 #3]  INFO -- : [a319c253-315d-462b-b078-b8bf702b07f2] Started GET "/admin/listing?model_name=listing&utf8=%E2%9C%93&query=Missouri" for 172.70.147.182 at 2022-09-20 08:48:26 +0000
2022-09-20T08:48:26.627933+00:00 app[web.1]: I, [2022-09-20T08:48:26.627863 #3]  INFO -- : [a319c253-315d-462b-b078-b8bf702b07f2] Processing by RailsAdmin::MainController#index as HTML
2022-09-20T08:48:26.627975+00:00 app[web.1]: I, [2022-09-20T08:48:26.627937 #3]  INFO -- : [a319c253-315d-462b-b078-b8bf702b07f2]   Parameters: {"model_name"=>"listing", "utf8"=>"✓", "query"=>"Missouri"}
2022-09-20T08:48:27.608308+00:00 app[web.1]: I, [2022-09-20T08:48:27.608202 #3]  INFO -- : [a319c253-315d-462b-b078-b8bf702b07f2]   Rendering vendor/bundle/ruby/2.5.0/gems/rails_admin-2.0.2/app/views/rails_admin/main/index.html.haml within layouts/rails_admin/application
2022-09-20T08:49:05.644054+00:00 app[web.1]: I, [2022-09-20T08:49:05.643938 #3]  INFO -- : [76f22ddd-486a-4157-bf5e-8f3b4afa6fca]   Rendered vendor/bundle/ruby/2.5.0/gems/rails_admin-2.0.2/app/views/rails_admin/main/index.html.haml within layouts/rails_admin/pjax (40009.3ms)
2022-09-20T08:49:05.650745+00:00 app[web.1]: I, [2022-09-20T08:49:05.650658 #3]  INFO -- : [76f22ddd-486a-4157-bf5e-8f3b4afa6fca] Completed 200 OK in 40944ms (Views: 58.3ms | ActiveRecord: 40596.8ms)
2022-09-20T08:49:07.592857+00:00 app[web.1]: I, [2022-09-20T08:49:07.592762 #3]  INFO -- : [a319c253-315d-462b-b078-b8bf702b07f2]   Rendered vendor/bundle/ruby/2.5.0/gems/rails_admin-2.0.2/app/views/rails_admin/main/index.html.haml within layouts/rails_admin/application (39984.4ms)
2022-09-20T08:49:07.593402+00:00 app[web.1]: I, [2022-09-20T08:49:07.593325 #3]  INFO -- : [a319c253-315d-462b-b078-b8bf702b07f2]   Rendered vendor/bundle/ruby/2.5.0/gems/rails_admin-2.0.2/app/views/layouts/rails_admin/_head.html.haml (0.4ms)
2022-09-20T08:49:07.595530+00:00 app[web.1]: I, [2022-09-20T08:49:07.595465 #3]  INFO -- : [a319c253-315d-462b-b078-b8bf702b07f2]   Rendered vendor/bundle/ruby/2.5.0/gems/rails_admin-2.0.2/app/views/layouts/rails_admin/_secondary_navigation.html.haml (1.6ms)
2022-09-20T08:49:07.595630+00:00 app[web.1]: I, [2022-09-20T08:49:07.595589 #3]  INFO -- : [a319c253-315d-462b-b078-b8bf702b07f2]   Rendered vendor/bundle/ruby/2.5.0/gems/rails_admin-2.0.2/app/views/layouts/rails_admin/_navigation.html.haml (1.9ms)
2022-09-20T08:49:07.608988+00:00 app[web.1]: I, [2022-09-20T08:49:07.608929 #3]  INFO -- : [a319c253-315d-462b-b078-b8bf702b07f2]   Rendered vendor/bundle/ruby/2.5.0/gems/rails_admin-2.0.2/app/views/layouts/rails_admin/_sidebar_navigation.html.haml (13.2ms)
2022-09-20T08:49:07.609182+00:00 app[web.1]: I, [2022-09-20T08:49:07.609140 #3]  INFO -- : [a319c253-315d-462b-b078-b8bf702b07f2]   Rendering vendor/bundle/ruby/2.5.0/gems/rails_admin-2.0.2/app/views/layouts/rails_admin/pjax.html.haml
2022-09-20T08:49:07.612388+00:00 app[web.1]: I, [2022-09-20T08:49:07.612333 #3]  INFO -- : [a319c253-315d-462b-b078-b8bf702b07f2]   Rendered vendor/bundle/ruby/2.5.0/gems/rails_admin-2.0.2/app/views/layouts/rails_admin/pjax.html.haml (3.1ms)
2022-09-20T08:49:07.612715+00:00 app[web.1]: I, [2022-09-20T08:49:07.612623 #3]  INFO -- : [a319c253-315d-462b-b078-b8bf702b07f2] Completed 200 OK in 40985ms (Views: 73.4ms | ActiveRecord: 40601.5ms)

RailsAdmin配置 #

config.model Listing do
    list do
      scopes %i[active archived only_active pending sold unlisted]
      items_per_page 10
      field :id
      field :created_at do
        pretty_value do
          value.in_time_zone('Arizona').strftime('%B %d, %Y %I:%M')
        end
      end
      field :updated_at do
        pretty_value do
          value.in_time_zone('Arizona').strftime('%B %d, %Y %I:%M')
        end
      end

      field :status
      field :price
      field :address
      field :address2
      field :listing_type
      field :city
      field :notes
      field :seller
      field :user
      field :images
      field :documents
      field :video
      field :offers
      field :appointments
      field :user_favorite_listings
      field :favorite_by
      field :lender_prequalifications
    end
end

优化方案 #

1. 针对性添加数据库索引 #

• RailsAdmin的全局搜索会对list配置的所有字段执行模糊匹配，检查以下字段是否有索引：
  • 对address、city等文本字段添加部分索引（如add_index :listings, :city, using: :gin）或PostgreSQL全文索引
  • 关联字段（如seller_id、user_id）确保外键字段有索引，关联表中用于匹配的字段（如sellers.name）也需添加索引
• 在生产环境执行EXPLAIN ANALYZE查看查询计划，确认是否存在全表扫描

2. 精简RailsAdmin配置 #

• 限制全局搜索的字段范围，避免关联集合字段参与搜索：

  config.model Listing do
    list do
      # ... 原有配置
      # 只指定需要搜索的字段，排除关联集合
      searchable [:address, :city, :status, :price, :seller_name, :user_email]
    end
  end
  

• 移除列表中不需要展示的关联集合字段（如images、offers、appointments），这些字段会触发N+1查询，即使分页仅展示10条，也会每条加载关联数据

3. 检查Heroku数据库配置 #

• 确认生产数据库实例规格，确保有足够内存和CPU处理查询
• 调整Rails数据库连接池大小，匹配Heroku数据库的连接限制
• 执行heroku pg:vacuum-analyze清理数据库碎片并更新统计信息

4. 定位慢查询 #

• 启用Heroku PostgreSQL的慢查询日志，定位具体耗时的SQL语句
• 使用Rails.logger.debug Listing.ransack({}).to_sql查看RailsAdmin生成的查询语句，手动优化

内容的提问来源于stack exchange，提问作者Salman Haseeb Sheikh