Query Performance Optimization in Django

Question

I am creating a Blog website in django and while creating a page for specific blog posts I came across a problem in querying the data using the ORM.

I have a model for Post which is related to another model comments where all the comments are captured. The comments model have following fields ->

class Comment(models.Model):
    comment = models.TextField(null=True)
    Created_date = models.DateTimeField(auto_now_add=True)
    Updated_date = models.DateTimeField(auto_now=True)
    post = models.ForeignKey(Post, on_delete=models.CASCADE, related_name='comments_post')
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='comments_user')

    def __str__(self):
        return self.comment

Now while Querying the single blog post there could be N number of comments and for each comment there is a user assigned who has written the comment. So I want to find the user details such (name, email or phone number) for the user for each comment.

The Query that I have executed is ->

post = Post.objects.select_related().prefetch_related('images_set','comments_post').get(id=post_id)

Now when I try to use {{comment.user.email}} and if there are 3 comments for the post, the ORM will query 3 times to the DB to find the email for each user. But if I use {{comment.user_id}} it will query only once as it preloads the user_id as it is available in comments table.

Is there any way I can make only one query to the DB to get all the Names for all the comments?

Because of the .select_related(), it will fetch the data of the user in the same query, so {{ post.user.email }} will not make extra queries. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Mar 1, 2022 at 16:53
What is post in {{ post.user.email }}? A Post object, or a Comment object? — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Mar 1, 2022 at 16:56
post is the post object which will contain all the details related to the blog post. — Tushar Sethi
– Tushar Sethi, Commented Mar 1, 2022 at 17:02
I need to find all the comments for the post and the related authors/users for those comments. — Tushar Sethi
– Tushar Sethi, Commented Mar 1, 2022 at 17:03
Sorry I updated the query I am using. I used Post instead of comment. — Tushar Sethi
– Tushar Sethi, Commented Mar 1, 2022 at 17:04

Javohir Elmurodov · Accepted Answer · 2022-03-01 17:03:26Z

5

It's possible prefetch_related foreign key inside foreign key. Such as prefetch_related('comments_post__user'), or If you want more performance you can use annotate. Annotate required field, It's more faster than prefetch

answered Mar 1, 2022 at 17:03

Javohir Elmurodov

14311 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tushar Sethi Over a year ago

Wow man. It worked. The number of queries are same. Thanks man. Really appreciate it.

Abdul Aziz Barkat Over a year ago

There's also Prefetch objects using which an even more efficient query could be written (Using select related inside the prefetch object)

willeM_ Van Onsem · Accepted Answer · 2022-03-02 06:36:44Z

1

You can save an extra query to the database that fetches the user with a Prefetch object [Django-doc]:

from django.db.models import Prefetch

post = Post.objects.select_related().prefetch_related(
    'images_set',
    Prefetch('comments_post', Comment.objects.select_related('user'))
).get(id=post_id)

This will make three roundtrips to the database: one for the Post, one for the image_set, and one for the comments_post. The last one will also fetch the data of the user, to prevent making an extra query for that.

answered Mar 2, 2022 at 6:36

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Collectives™ on Stack Overflow

Query Performance Optimization in Django

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related