Speeding up ActiveRecord Querying on very large joins?

The bottleneck begins to occur on offense = ... (around 30 ms) and then
deepens on defense = ... (around 460 ms).

These are indeed large but everything listed here is absolutely required
for my app to work. What can I do to optimize this?

First off use EXPLAIN ... in your database's the query browser/console
to find out how database is executing your query. You may find that
it's doing something really stupid or that there are indices missing.