Is there a way to make this django annotate query more efficient
I'm struggling to make a django query more efficient. It either takes a long time on localhost, times out on my production server, or just gives a 500 on the tiny AWS nano server that I use for testing.
What I'm trying to do is find the most "rolled on" charts (these are random tables or random generators), over a period of time. The largest period of time is 12 months.
The query looks a bit like this...
queryset = Chart.objects.all().filter(diceroll__timestamp__gt=time_delta, diceroll__timestamp__lte=timestamp_now).annotate(num_dicerolls=Count("diceroll")).order_by('-num_dicerolls')
The problem is that the longer the period of time, the longer the query takes, so it seems that the massive number of dice rolls is a contributing factor. Each dice roll is logged as its own entry in a DiceRoll table (so Chart is a fk in the DiceRoll table).
The only alternative I can think of is a non-django related solution where a completely different database table is used to cache the number of dice rolls per chart for every past month. That would mean that the annotator would have less to count. This is just a guess though, so untested, so before I go down that rabbit hole, I would to know if there is anything fundamentally wrong with my django query.