fast lookup for the last element in a Django QuerySet?

Question

I've a model called Valor. Valor has a Robot. I'm querying like this:

Valor.objects.filter(robot=r).reverse()[0]

to get the last Valor the the r robot. Valor.objects.filter(robot=r).count() is about 200000 and getting the last items takes about 4 seconds in my PC.

How can I speed it up? I'm querying the wrong way?

Answer 1

It sounds like your data set is going to be big enough that you may want to denormalize things a little bit. Have you tried keeping track of the last Valor object in the Robot object?

class Robot(models.Model):
    # ...
    last_valor = models.ForeignKey('Valor', null=True, blank=True)

And then use a post_save signal to make the update.

from django.db.models.signals import post_save

def record_last_valor(sender, **kwargs):
    if kwargs.get('created', False):
        instance = kwargs.get('instance')
        instance.robot.last_valor = instance

post_save.connect(record_last_valor, sender=Valor)

You will pay the cost of an extra db transaction when you create the Valor objects but the last_valor lookup will be blazing fast. Play with it and see if the tradeoff is worth it for your app.