I am new in Django, I am creating a basic website where users can post some messages and add some tags to that post. I have 3 models Person, Post, and Tag. Using the rest framework, I created API for Posts, which should give me a list of posts with person's names and tags which posts relate with. For a small number of posts, this is working fine, but for large posts, it is taking too long to respond. Using Django-toolbar I am able to figure out too many queries are being fired, so I used prefetch_related and select_related in queryset. Here, a number of queries were reduced by half but still for foreign key relationship query is executing for each record. below is a sample of my code.
For getting 10 records it is firing 34 queries without using prefetch_related on Tag model. After using prefetch_related on Tag, it is 15 queries. Now for an individual post, it is firing query on Person with self-join. How can I reduce this number of queries?
# models.py
class Tag(models.Model):
name = models.CharField(max_length=200,unique=True)
class Person(models.Model):
name = models.CharField(max_length=200,unique=True)
friends = models.ManyToManyField("self", blank=True)
class Post(models.Model):
person_id = models.ForeignKey(Person)
text = models.TextField(blank=True,null=True)
related_tags = models.ManyToManyField(Tag)
# serilizers.py
class PostSerializer(serializers.HyperlinkedModelSerializer):
@staticmethod
def setup_eager_loading(queryset):
queryset = queryset.select_related("person_id").prefetch_related("related_tags")
return queryset
class Meta:
model = Post
fields = ('url', 'id', 'text','related_tags','person_id')
# fields = '__all__'
depth = 1
#views.py
class PostViewSet(viewsets.ModelViewSet):
queryset = PostSerializer.setup_eager_loading(Post.objects.all())
serializer_class = PostSerializer
Below is the list of queries with respective occurrence:
SELECT `myapp_post`.`id`, `myapp_post`.`person_id_id`, `myapp_post`.`text`, `myapp_person`.`id`, `myapp_person`.`name`
FROM `myapp_post` INNER JOIN `myapp_person` ON (`myapp_post`.`person_id_id` = `myapp_person`.`id`)
LIMIT 10
Occurrence - 1 time
SELECT (`myapp_post_related_tags`.`post_id`) AS `_prefetch_related_val_post_id`, `myapp_tag`.`id`, `myapp_tag`.`name`
FROM `myapp_tag` INNER JOIN `myapp_post_related_tags` ON (`myapp_tag`.`id` = `myapp_post_related_tags`.`tag_id`)
WHERE `myapp_post_related_tags`.`post_id` IN (11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
Occurrence - 1 time
SELECT `myapp_person`.`id`, `myapp_person`.`name`
FROM `myapp_person` INNER JOIN `myapp_person_related_persons` ON (`myapp_person`.`id` = `myapp_person_related_persons`.`to_person_id`)
WHERE `myapp_person_related_persons`.`from_person_id` = 704692
Occurrence - 10 times for 10 records
