Django endpoint does 300+ queries, select_related vs prefetch_related confusion
Kacper Nowicki@kacper2004
Hi, I am intern at a fintech, writing internal API in Django REST Framework. Endpoint /api/transfers/ returns 50 transfers and django-debug-toolbar shows 312 queries xD
Models: Transfer has two ForeignKeys to Account (sender, receiver), Account has ForeignKey to Customer, and Transfer has ManyToMany tags.
class TransferViewSet(viewsets.ReadOnlyModelViewSet):
queryset = Transfer.objects.select_related("sender", "receiver")
serializer_class = TransferSerializer
class TransferSerializer(serializers.ModelSerializer):
sender_name = serializers.CharField(source="sender.customer.full_name")
receiver_name = serializers.CharField(source="receiver.customer.full_name")
tags = serializers.SlugRelatedField(many=True, read_only=True, slug_field="name")
class Meta:
model = Transfer
fields = ["id", "amount", "created_at", "sender_name", "receiver_name", "tags"]With select_related("sender", "receiver") it went from 312 to 162. When I add "tags" to select_related I get:
FieldError: Invalid field name(s) given in select_related: 'tags'. Choices are: sender, receiverHow it is possible that select_related does not work for tags? And the customer of the account is still one query per row I think. Django 5.1, Postgres.
Accepted answer
Good debugging, you already found the counter, that's half the job.
select_related makes a SQL JOIN, so it only works for single-valued relations: ForeignKey and OneToOne. You can follow a chain with double underscores. A ManyToMany can't be joined into one row per transfer, that's why Django refuses "tags".
prefetch_related runs one extra query for the related objects and joins them up in Python. That's what ManyToMany and reverse ForeignKeys need.
queryset = (
Transfer.objects
.select_related("sender__customer", "receiver__customer")
.prefetch_related("tags")
.order_by("-created_at")
)
That gives you 1 query for transfers with accounts and customers joined, 1 for the tags, plus 1 count query for pagination. 3 in total.
If tags have big columns you don't need: Prefetch("tags", queryset=Tag.objects.only("id", "name")).
And lock it in with a test so nobody brings it back:
with self.assertNumQueries(3):
self.client.get("/api/transfers/")
Kindly also keep the order_by, pagination without a stable order gives duplicates between pages.
Get the best of #rubber-duck-club weekly
One email each Monday with the most useful posts. No spam; unsubscribe in one click.
Comments
Sign in to join the conversation.
Sign in to commentAyesha Siddiqui@laravel_ayesha
laravel person passing by: select_related is our with() for belongsTo, prefetch_related is with() for many-to-many. the N+1 is the same disease in every ORM 😄
Accepted answer
Grace Wanjiru@grace_wanjiru
Good debugging, you already found the counter, that's half the job.
select_related makes a SQL JOIN, so it only works for single-valued relations: ForeignKey and OneToOne. You can follow a chain with double underscores. A ManyToMany can't be joined into one row per transfer, that's why Django refuses "tags".
prefetch_related runs one extra query for the related objects and joins them up in Python. That's what ManyToMany and reverse ForeignKeys need.
queryset = (
Transfer.objects
.select_related("sender__customer", "receiver__customer")
.prefetch_related("tags")
.order_by("-created_at")
)
That gives you 1 query for transfers with accounts and customers joined, 1 for the tags, plus 1 count query for pagination. 3 in total.
If tags have big columns you don't need: Prefetch("tags", queryset=Tag.objects.only("id", "name")).
And lock it in with a test so nobody brings it back:
with self.assertNumQueries(3):
self.client.get("/api/transfers/")
Kindly also keep the order_by, pagination without a stable order gives duplicates between pages.
Kacper Nowicki@kacper2004
3 queries!! From 312. Thank you Grace. So select_related is JOIN and prefetch is a second query, now it makes sense xD I will add the assertNumQueries test so my senior sees I am professional
Grace Wanjiru@grace_wanjiru
Exactly! And the test will impress your senior more than the fix itself. Well done, share the next one you find 😊
Javier Montero@javi_montero
Kacper, also ask your fintech to pay the overtime when you fix their N+1 at 8 pm. Eso también es rendimiento 😏
Kacper Nowicki@kacper2004
Javi I am on B2B internship contract, there is no overtime, only freedom xD