Ruby on Rails Postgres/Active Record Search Optimization

Question

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

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

The search takes this long:

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

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.

The first search: SELECT "players".* FROM "players" WHERE "players"."replay_id" = $1 Then when searching the players: SELECT "players".* FROM "players" WHERE "players"."replay_id" = $1 [["replay_id", 117217]] (per replay) — JayDee
– JayDee, Commented Nov 24, 2017 at 15:06

Ursus · Accepted Answer · 2017-11-24 15:22:10Z

2

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

edited Nov 24, 2017 at 15:22

answered Nov 24, 2017 at 14:55

Ursus

30.1k3 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

JayDee Over a year ago

This works, the time was cut drastically. Completed 200 OK in 254032ms (Views: 34.1ms | ActiveRecord: 20682.4ms)

SRack Over a year ago

Just the ticket - nice answer. I'd add that it'd be worth dropping at least some elements of that request into a model scope to clean up the controller a little. @GustavMauler - for future reference, it was an N + 1 problem slowing you down, docs here.

Ursus Over a year ago

the query still seems pretty huge. Open another question with the new bottleneck if you want. Thanks @SRack

JayDee Over a year ago

Yeah I see that now. Perhaps it's greedy but is it possible to get even better performance?

Ursus Over a year ago

Yeah, I'm pretty sure. But we need more info as the ones in this question. Open another one

|

Collectives™ on Stack Overflow

Ruby on Rails Postgres/Active Record Search Optimization

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related