Django endpoint does 300+ queries, select_related vs prefetch_related confusion
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",…
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.