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.

Fred