Ruby on Rails Postgres /活动记录搜索优化 [英] Ruby on Rails Postgres/Active Record Search Optimization

问题描述

所以可能没有办法解决此问题，但我想我会问：

So it's possible that there may not be a way to fix this but I thought I would ask:

我已经设置了一个Postgresql数据库。第一个表包含重播，每个重播都有10个唯一的播放器（一个有很多关系）。我想基于我的搜索呈现一个JSON，其中包含其播放器的重放。但是，我要进行大量的重播（2000）-全部都有10个播放器，这意味着要搜索20,000个播放器。

I have a Postgresql DB set up. The first table contains replays which each have 10 unique players (a has many relationship). I want to render a JSON based on my search that contains the replays with their players. However, I am pulling a large number of replays (2000) -- which all have 10 players, meaning searching for 20,000 players.

这是索引搜索当前的样子：

This what the index search currently looks like:

def index
  @replays = Replay.where(map_id: params['map_id'].to_i)
  @replays = @replays.reverse
  render json: @replays[0..2000].to_json(include: [:players])
end

搜索需要这么长时间：

Completed 200 OK in 639596ms (Views: 42.1ms | ActiveRecord: 329252.3ms)

有没有更好的方法可以搜索并呈现不会花费这么长时间的JSON？值得注意的是，仅搜索2k重播，20k播放器，甚至与播放器进行一次特定的重播仅需几秒钟（第一次搜索本身也仅需几秒钟），因此我认为这是一个音量问题。

Is there a better way I can search and render the JSON that won't take this long? It's worth noting that just searching for 2k replays, 20k players, or even a single specific replay with players takes only a couple seconds (the first search itself also takes only a couple of seconds) so I assume it's a volume issue.

推荐答案

尝试热切加载您的重放文件

Try to eager load your replays

replays = Replay.last_two_thousands_by_map_id(params[:map_id])

render json: replays.to_json(include: [:players])

# replay.rb model
def self.last_two_thousands_by_map_id(map_id)
  includes(:players).where(map_id: map_id).order(id: :desc).limit(2000)
end

这篇关于Ruby on Rails Postgres /活动记录搜索优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！