using select_related and prefetch_related with many arguments

Question

I am relatively new to django, and I am using select_related() and prefetch_related() in django to decrease my hits to the database.

this is a part of my views.py file:

topic = Topic.objects.filter(id=topic_id).select_related('followed_by')
questions = Question.objects.filter(topics=topic).prefetch_related('answers','question_comments','answers__created_by__userprofile','answers__answer_comments')

without the select_related and prefetch_related the view does 42 queries in 23.36MS, and with those added, it decreases to 10 queries in 7.91MS, looking at this data, I think those functions are really great.

So my question is this: Is there any downside to using these two functions? Should I not join this many tables in one go?

Answer 1

Simple answer

No, there isn't a downside

Real Answer

Prefetch related does the join on the python side. Most of the time this is fine but always profile. You may also find the Prefetch object really helpful when you need to tune your DB access further.

Select related does what you would expect (it creates the join statements and flushes that to the database) and will perform really well most if the time.

In scaling up, the joins may become more of a hinderance - you'll want to maybe alter your data model by denormalizing, implementing a caching layer, separating the calls by resource type, etc..

Side Note

You'll want to start putting these bigger queries into your model manager so the view can just call a method and you can tune away behind the scenes.

e.g. (Note, there are newer ways of doing this with queryset subclasses, etc..)

# models.py

class QuestionManager(models.Manager):
    def by_topic(self, topic):
        return self.filter(topic=topic).prefetch_related('...')

# views

def my_view(request, *args, **kwargs):
    # get the topic however
    topic = get_topic(kwargs.get('topic')
    Question.objects.by_topic(topic)