What is the best way to do intense read only queries in Django

Question

We have a really big application in Django which uses Postgres database. We want to build an analytics module.

This module uses a base query e.g.

someFoo = SomeFoo.objects.all() # Around 100000 objects returned. 

Then slice and dice this data. i.e.

someFoo.objects.filter(Q(creator=owner) | Q(moderated=False))

These queries will be very intense and as this will be an analytics and reporting dashboard the quires will hit the database very badly.

What is the best way to handle complex queries in such conditions ? i.e. when you have a base query and it will be sliced and diced very often in a span of short time and never be used again.

A few possible solutions that we have though of are

1. A read only database and a write only database.
2. Writing Raw sql queries and using them. As django ORM can be quite inefficient for certain types of queries.
3. Caching heavily (Have not though or done any research in this.)

Edit : E.g. query

select sport."sportName", sport.id, pop.name, analytics_query.loc_id, "new count"
from "SomeFoo_sportpop" as sportpop join "SomeFoo_pop" as pop on (sportpop.pop_id=pop.id) join "SomeFoo_sport" as sport on (sportpop.sport_id=sport.id) join 
(select ref.catcher_pop_id as loc_id,
(select count(*) from "SomeFoo_pref" where catcher_pop_id=ref.catcher_pop_id and status='pending' and exists=True) as "new count"
from "SomeFoo_pref" as ref
where ref.exists=TRUE and ref.catcher_pop_id is not NULL
group by ref.catcher_pop_id) as analytics_query on (sportpop.pop_id=analytics_query.loc_id)
order by sport."sportName", pop.name asc

This is an example of a raw sql query we are planning to make and its going to have a lot of where statements and groupby. Basically we are going to slice and dice the base query a lot.

Is there any other possible solution or method that you can point us to. Any help is highly appreciated.

Answer 1

I can think to PREPARED STATMENT and a faster server, may be on linux...