An architecture choice to build reports on top of Django

[nilamo]

Can you view the raw sql that the orm generates in any way?  Some db clients let you view currently running queries, or query the statistics tables to view long-running queries.  That sort of thing should be your first step, to see if simply adding an index or two solves your problem.  10 second report times are completely unacceptable, so definitely some sort of caching should be used if indexes can't solve the problem.

If all your reports are historical data (ie, yesterday, last month, etc) rather than right NOW, Today, The Past Five Minutes sort of thing, you could fake an OLAP cube by pre-compiling sums/averages using whatever groupings you use, and store those in a new table.  That way, when you run a report, all you do is grab the pre-calculated values, which would be lightning fast.  Just don't tell users you do that, because they have a tendency to freak out when you tell them you're doing all the math ahead of time and just showing them numbers that might not actually reflect that data anymore.

To summarize:

• check if indexes can save the day.
  
• eliminate everything you can from the orm (extra fields/groupings/math/anything).  The less work you make the database do, the better.
  
• consider trying to do things asynchronously.  If you can run 5 queries at the same time, instead of having 4 of them waiting for the first to finish, you'll get the same data, the same way, but be much faster doing it.  ...I don't actually know if Django's orm is thread safe, but coroutines are an equally valid option.
  
• cache some (or all) of the results, or parts of how you get those results (join a pre-calculated table, instead of a massive Transactions table, for example)
  
• rewrite the query in sql.  You might not like it, but sometimes when it comes to "complicated" things, an orm just won't cut it.