Optimize DB requests in Django

Question

I have a page view in Django set up as follows:

    blog = get_object_or_404(Blog, subdomain=extracted.subdomain)
    all_posts = Post.objects.filter(
        blog=blog, publish=True).order_by('-published_date')
    nav = all_posts.filter(is_page=True)
    posts = all_posts.filter(is_page=False)

This causes the DB to be accessed 3 times. I'm trying to optimize this to only access the DB once. The following snippet reduces the number of calls to 2, but I'm sure there is a better way than this that I don't know about.

blog = get_object_or_404(Blog, subdomain=extracted.subdomain)
all_posts = Post.objects.filter(
    blog=blog, publish=True).order_by('-published_date')
nav = []
posts = []
for post in all_posts:
    if post.is_page:
        nav.append(post)
    else:
        posts.append(post)

As far as I understand, prefetch_related and select_related work inversely and I'm unsure how to implement them in this context.

My models are set up as follows:

class Blog(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE, blank=True)
    title = models.CharField(max_length=200)
    ...

class Post(models.Model):
    blog = models.ForeignKey(
        Blog,
        on_delete=models.CASCADE,
        related_name='post')
    title = models.CharField(max_length=200)
    ...

Thanks in advance!

Edit:

And for some reason this executes 3 DB requests

blog = get_object_or_404(Blog.objects.prefetch_related('post_set'), subdomain=extracted.subdomain)

posts = blog.post_set.filter(publish=True).order_by('-published_date')

You're likely optimizing the wrong thing. Why do you need two lists? You can just iterate the one queryset and inspect the property is_page when rendering. Only on the first iteration will the queryset be evaluated. — user1600649
– user1600649, Commented May 30, 2020 at 13:01
Yeah, I can do that on the template, which works fine without doing an extra request. I still can't seem to get the blog info and post info in the same call — Herman Martinus
– Herman Martinus, Commented May 30, 2020 at 13:12

user1600649 · Accepted Answer · 2020-05-30 14:14:40Z

1

Some things:

Separating them into two querysets or lists is a bad idea. Use the template to inspect is_page and only render it in the nav section or posts section.
Optimizing queries like this is not where you gain the big chunks of performance. You want to get rid of queries that happen when iterating the queryset (so in the for loops) and that is what prefetch and select related are for.

That said, to get rid of the extra query:

posts = Post.objects.filter(
    blog__subdomain=extracted_subdomain, publish=True
).order_by('-published_date')
if not posts:
   raise Http404('no posts for subdomain')

And to make sure you have access to Blog properties from Posts without querying with each iteration:

posts = Post.objects.filter(
    blog__subdomain=extracted_subdomain, publish=True
).select_related('blog').order_by('-published_date')

answered May 30, 2020 at 14:14

user1600649

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

1 Comment

Herman Martinus Over a year ago

This makes sense. Thank you :)

Collectives™ on Stack Overflow

Optimize DB requests in Django

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related